I have been testing an AlwaysOn high availability failover solution in SQL Server Enterprise on an Azure VM, and this works pretty well as a failover for SQL Server Databases, but I also need a high availability solution for SQL Server Analysis Server, and so far I haven't found a way to do this.  I can load balance it between two machines, but this isn't working as a failover and because of the restriction of not being able to use shared storage in a Failover Cluster in Azure VM's, I can't set it up as a cluster which is required for AlwaysOn in Analysis Services. 

Anyone else found a solution to use an AlwaysOn High Availability for SQL Analysis Services in Azure VM?  As my databases are read-only, I would be satisfied with even just a solution that would sync the OLAP databases and switch the data connection to the same server as the SQL databases.

Thanks!

Bill

Hi Bill,

According to your description, I think you need to use AlwaysOn Availability Groups.

An AlwaysOn availability group is a predefined collection of SQL Server relational databases that failover together when conditions trigger a failover in any one database, redirecting requests to a mirrored database on another instance in the same availability group. If you are using availability groups as your high availability solution, you can use a database in that group as a data source in an Analysis Services tabular or multidimensional solution. All of the following Analysis Services operations work as expected when using an availability database: processing or importing data, querying relational data directly (using ROLAP storage or DirectQuery mode), and writeback.

For more information, please see:

http://msdn.microsoft.com/en-us/library/hh510230.aspx

http://msdn.microsoft.com/en-us/library/hh967523.aspx#bkmk_ssasAODB

TechNet Subscriber Support

If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

Hi Iric,

Thanks for your reply - I appreciate it.

I did manage to get AlwaysOn availability groups set up for my SQL Server relational databases using the following links as guides:

http://social.technet.microsoft.com/wiki/contents/articles/11578.best-practices-for-running-sql-server-in-windows-azure-virtual-machine-en-us.aspx

http://social.technet.microsoft.com/wiki/contents/articles/14776.configuring-windows-failover-cluster-in-windows-azure-for-alwayson-availability-groups.aspx

The second link you provided describes how to set up a connection that will use the readable secondary replica as a secondary data source for Analysis Services, which doesn't solve my problem of having a failover solution for Analysis Services itself.  What I need is a solution that will allow Analysis Services to keep on working in the event that one of the SQL Servers stops functioning, just like I currently do for the SQL Server relational databases.  I currently have them load balanced, so if one of the servers is switched off, refreshing the report enough times will eventually make the report run when it runs off the server that is up, but that is not a great solution.

Thanks,

Bill

• Edited by Bill Vanderpol Monday, December 10, 2012 7:02 PM

Hi Iric,

Thanks for your reply - I appreciate it.

I did manage to get AlwaysOn availability groups set up for my SQL Server relational databases using the following links as guides:

http://social.technet.microsoft.com/wiki/contents/articles/11578.best-practices-for-running-sql-server-in-windows-azure-virtual-machine-en-us.aspx

http://social.technet.microsoft.com/wiki/contents/articles/14776.configuring-windows-failover-cluster-in-windows-azure-for-alwayson-availability-groups.aspx

The second link you provided describes how to set up a connection that will use the readable secondary replica as a secondary data source for Analysis Services, which doesn't solve my problem of having a failover solution for Analysis Services itself.  What I need is a solution that will allow Analysis Services to keep on working in the event that one of the SQL Servers stops functioning, just like I currently do for the SQL Server relational databases.  I currently have them load balanced, so if one of the servers is switched off, refreshing the report enough times will eventually make the report run when it runs off the server that is up, but that is not a great solution.

Thanks,

Bill

• Edited by Bill Vanderpol Monday, December 10, 2012 7:02 PM

Hi Iric,

Thanks for your reply - I appreciate it.

I did manage to get AlwaysOn availability groups set up for my SQL Server relational databases using the following links as guides:

http://social.technet.microsoft.com/wiki/contents/articles/11578.best-practices-for-running-sql-server-in-windows-azure-virtual-machine-en-us.aspx

http://social.technet.microsoft.com/wiki/contents/articles/14776.configuring-windows-failover-cluster-in-windows-azure-for-alwayson-availability-groups.aspx

The second link you provided describes how to set up a connection that will use the readable secondary replica as a secondary data source for Analysis Services, which doesn't solve my problem of having a failover solution for Analysis Services itself.  What I need is a solution that will allow Analysis Services to keep on working in the event that one of the SQL Servers stops functioning, just like I currently do for the SQL Server relational databases.  I currently have them load balanced, so if one of the servers is switched off, refreshing the report enough times will eventually make the report run when it runs off the server that is up, but that is not a great solution.

Thanks,

Bill

• Edited by Bill Vanderpol Monday, December 10, 2012 7:02 PM

Hi Iric,

Thanks for your reply - I appreciate it.

I did manage to get AlwaysOn availability groups set up for my SQL Server relational databases using the following links as guides:

http://social.technet.microsoft.com/wiki/contents/articles/11578.best-practices-for-running-sql-server-in-windows-azure-virtual-machine-en-us.aspx

http://social.technet.microsoft.com/wiki/contents/articles/14776.configuring-windows-failover-cluster-in-windows-azure-for-alwayson-availability-groups.aspx

The second link you provided describes how to set up a connection that will use the readable secondary replica as a secondary data source for Analysis Services, which doesn't solve my problem of having a failover solution for Analysis Services itself.  What I need is a solution that will allow Analysis Services to keep on working in the event that one of the SQL Servers stops functioning, just like I currently do for the SQL Server relational databases.  I currently have them load balanced, so if one of the servers is switched off, refreshing the report enough times will eventually make the report run when it runs off the server that is up, but that is not a great solution.

Thanks,

Bill

• Edited by Bill Vanderpol Monday, December 10, 2012 7:02 PM

Hi Iric,

Thanks for your reply - I appreciate it.

I did manage to get AlwaysOn availability groups set up for my SQL Server relational databases using the following links as guides:

http://social.technet.microsoft.com/wiki/contents/articles/11578.best-practices-for-running-sql-server-in-windows-azure-virtual-machine-en-us.aspx

http://social.technet.microsoft.com/wiki/contents/articles/14776.configuring-windows-failover-cluster-in-windows-azure-for-alwayson-availability-groups.aspx

The second link you provided describes how to set up a connection that will use the readable secondary replica as a secondary data source for Analysis Services, which doesn't solve my problem of having a failover solution for Analysis Services itself.  What I need is a solution that will allow Analysis Services to keep on working in the event that one of the SQL Servers stops functioning, just like I currently do for the SQL Server relational databases.  I currently have them load balanced, so if one of the servers is switched off, refreshing the report enough times will eventually make the report run when it runs off the server that is up, but that is not a great solution.

Thanks,

Bill

• Edited by Bill Vanderpol Monday, December 10, 2012 7:02 PM

Bill,

So, what you need is a model like SQL Server failover cluster instances. (before sql server 2012)

In SQL Server 2012, AlwaysOn replaces SQL Server failover cluster, and it has been seperated to AlwaysOn Failover Cluster Instances (SQL Server) and AlwaysOn Availability Groups (SQL Server).

Since your requirement is not in database level, I think the best option is to use AlwaysOn Failover Cluster Instances (SQL Server).

As part of the SQL Server AlwaysOn offering, AlwaysOn Failover Cluster Instances leverages Windows Server Failover Clustering (WSFC) functionality to provide local high availability through redundancy at the server-instance levela failover cluster instance (FCI). An FCI is a single instance of SQL Server that is installed across Windows Server Failover Clustering (WSFC) nodes and, possibly, across multiple subnets. On the network, an FCI appears to be an instance of SQL Server running on a single computer, but the FCI provides failover from one WSFC node to another if the current node becomes unavailable.

It is similar to SQL Server failover cluster in SQL 2008 R2 and before.

Please refer to these references:

Failover Clustering in Analysis Services

Installing a SQL Server 2008 R2 Failover Cluster

Hi there,

I am not sure if this would work at all. The issue is that SSAS HA requires share storage, which is not supported in Azure.

Regards,
P.

Hi, I'm also having the issue where WSFC requires a shared disk which does not exist in Azure. So how do I setup a clustered SSAS in Azure?

Hi Mark,

The short answer is - you can't.  The best you can do is to set up a sync of your Analysis Services database across your servers so that the same database is on both servers and then set up a load balanced port for SSAS in Azure, either internal or external depending on what you are using it for.