SSAS high availability
If you are looking at providing high availability (HA) for SSAS, here are 3 options:
- Install SSAS on a Windows Server Failover Cluster (WSFC)
Here’s a good article. The main issue with this option is that SSAS isn’t cluster-aware, so if windows is “OK” but SSAS (the service) is hung, it won’t failover. Also check out How to Cluster SQL Server Analysis Services - Network Load Balancing (NLB) across a SSAS Scale-Out Query Cluster
Basically just load balancing queries across 1+N servers that are all hosting a separate copy of the tabular or multidimensional model. If a query-server goes down, there’s still several others available to resolve the query. This provides scalability and availability. Unfortunately, it is not completely transparent as you have to manage:
– Configuration of the load balancer
– Deployment of updates (for the analysis services databases you can do detach->file copy to other servers->reattach, analysis services database backup/restore, or process data on a “process” server and use database synchronization to update the read-only instances you put behind the load balancer). If you have to have 24×7 availability, you have to put a node offline when a node is already synchronized, otherwise you accept having different versions of the same database available at the same moment. Keep in mind scale-out query server architecture is primarily used to satisfy a high-concurrency requirement. HA is just a bi-product of the architecture (and only if you have a smart NLB) and comes at a pretty steep cost in terms of licensing. Over the years I’ve been on a number of projects where the “requirements” change once presented with the cost - Azure Analysis Services
This new service in the cloud has options for high availability. It makes it super easy to (programmatically) spin up another server and restore a backup. Just keep in mind the new server does not have the same address, so you have to manage the client connection and this is not transparent. Also note this service has a 99.9% SLA
On a side note, SSAS can use a SQL Server database in an Always On availability group as a data source: Analysis Services with Always On Availability Groups.
Pingback:SSAS HA – Curated SQL
Hi Eugene…HA for SSRS is different: https://docs.microsoft.com/en-us/sql/reporting-services/report-server-sharepoint/high-availability-reporting-services
Sounds like I should do a blog post on that 🙂
Thanks for the article, James! Quick question: is everything apply the same for the HA for SSRS?
You missed the WP I wrote on clustering SSAS which has a lot of the caveats and such in detail. https://msdn.microsoft.com/en-us/library/dn736073.aspx
Thanks Allan! I have added a link to your excellent document above.