Clustering SQL Server Analysis Services Part 1

By:   |   Comments (5)   |   Related: 1 | 2 | > Clustering


Problem

In a previous tip on Installing SQL Server 2008 on a Windows Server 2008 Cluster Part 1, we have seen how to install and configure a SQL Server 2008 instance on a Windows Server 2008 Failover Cluster. We now have a new requirement to provide high availability to our SQL Server Analysis Services instance. How do I make SQL Server Analysis Services highly available?

Solution

While a lot of documentation, articles and blog post focus on providing high availability to the SQL Server database engine using Windows Server Failover Clustering, not much is covered in terms of providing the same for SQL Server Analysis Services. For years, it's been a topic of debate whether or not it is supported to run on top of a Windows Server Failover Cluster (WSFC.) The fact that there are TechNet and MSDN articles pertaining to running SQL Server Analysis Services on WSFC means that it is supported. However, there are several differences that we need to understand before we can run SQL Server Analysis Services on WSFC.

  • The SQL Server database engine is considered a cluster-aware application while Analysis Services isn't. This simply means that a SQL Server failover clustered instance has a corresponding cluster resource DLL responsible for health detection and failover policies from the WSFC-level down to the database engine-level. If something does not look right inside the database engine - for example, a T-SQL query timing out after 7 seconds - the cluster resource DLL of the database engine can tell the WSFC to initiate a failover. SQL Server Analysis Services is technically not a cluster-aware application. During setup, a Generic Application is created on a WSFC for the Analysis Services instance. This means that any issues happening inside the Analysis Services engine would need to be detected by other means, like a monitoring tool, for example. Automatic failover like the one caused by the Database Engine cluster resource DLL has to be initiated by a script such as those calling the Failover Clustering PowerShell modules. However, WSFC will automatically failover the Analysis Services instance if issues are detected at the node level, such as when the node accidentally gets rebooted.
  • SQL Server Analysis Services high availability can be implemented either via WSFC or network load balancing (NLB) while the database engine does not support running on NLB. This is still one of the biggest misconceptions about the SQL Server database engine running on WSFC. It does not support load balancing, even with Availability Group readable secondary's, because applications can only read and write on one copy of the database at any given point in time. However, SQL Server Analysis Services support running on a network load balanced configuration, similar to SQL Server Reporting Services, provided that the databases are configured as read-only.

With these differences in mind, we can go ahead and provide high availability to SQL Server Analysis Services instances running on WSFC. In this tip, we will proceed to do so. It is assumed that you already have a WSFC with the proper storage and configuration. If you haven't done so, you can follow the steps outlined in part 1 and part 2 of the series on Install SQL Server 2008 on a Windows Server 2008 Cluster. While the tips are written for Windows Server 2008, the concepts still apply up to Windows Server 2012 R2. Similar to a SQL Server failover clustered instance, the binaries and executable will be installed on the local drives whereas the database and backup files are stored on the clustered drives.

To install a SQL Server 2014 on a WSFC:

  1. Run setup.exe from the installation media to launch SQL Server Installation Center.
  2. Click on the Installation link on the left-hand side. Click the New SQL Server failover cluster installation link. This will run the SQL Server 2014 Setup wizard.

    SQLSetup

  3. In the Microsoft Updates dialog box, you have the option to include SQL Server product updates like service packs and cumulative updates in the installation process. Click Next.

    SQLSetup_MSUpdate

  4. In the Install Failover Cluster Rules dialog box, validate that the checks return successful results. If the checks returned errors, make sure you fix them before proceeding with the installation.

    SQLSetup_FCI_Rules

  5. In the Product Key dialog box, enter the product key that came with your installation media and click Next.

    SQLSetup_ProductKey

  6. In the License Terms dialog box, click the I accept the license terms check box and click Next. And, don't worry, you're not the only one NOT reading the EULA.

    SQLSetup_EULA

  7. In the Setup Role dialog box, select the SQL Server Feature Installation option and click Next.

    SQLSetup_FeatureSelection

  8. In the Feature Selection dialog box, select Analysis Services and Management Tools. Click Next.

    NOTE: The decision to include the database engine and Analysis Services in the same WSFC cluster resource group/role is totally up to you. We will discuss the pros and cons of having them either both in the same WSFC cluster resource group/role or on separate ones at the end of this series.

    SQLSetup_SSAS_Selection

  9. In the Feature Rules dialog box, verify that all the rules have passed. If the rules returned a few warnings, make sure you fix them before proceeding with the installation. Click Next.

    SQLSetup_FeatureRules

  10. In the Instance Configuration dialog box, enter the SQL Server Network Name that you would like to use for your Analysis Services instance. This is the name that will be available on the network for the client applications to access. This will vary depending on your selection of whether it is a default or named instance. In this example, the named instance is selected because I already have a default instance of the database engine installed. In a WSFC, we can only have one default instance of SQL Server, regardless of the engine, unlike in a standalone instance where we can add a default instance of Analysis Services on top of an existing default instance of the database engine.

    A couple of things need highlighting in this section. By default, the instance name is used as the Instance ID. This is used to identify installation directories and registry keys for your instance of SQL Server and is helpful when you want to run multiple instances in a cluster. This is the case for both default and named instances. For a default instance, the instance name and instance ID would be MSSQLSERVER. To use a non-default instance ID, you should select the Instance ID box and specify a value.

    The section on Detected SQL Server instances and features on this computer would make sense if there are other SQL Server instances running on your server. Click Next

    SQLSetup_InstanceName

  11. In the Cluster Resource Group dialog box, check the resources available on your WSFC. This tells you that a new Resource Group will be created on your cluster for the SQL Server Analysis Services instance. To specify the SQL Server cluster resource group name, you can either use the drop-down box to specify an existing group to use or type the name of a new group to create it. Click Next.

    SQLSetup_ResourceGroup

  12. In the Cluster Disk Selection dialog box, select the clustered disks that you would like to use for your SQL Server Analysis Services instance.

    SQLSetup_ClusterDisk

  13. In the Cluster Network Configuration dialog box, enter the IP address and subnet mask that your SQL Server Analysis Services virtual network name will use. Uncheck the checkbox under the DHCP column as you will be using static IP addresses. Click Next.

    SQLSetup_ClusterIP

  14. In the Server Configuration dialog box, enter the domain credentials that you will use for your SQL Server Analysis Services service account in the Service Accounts tab. Click Next.

    SQLSetup_ServiceAccount

  15. In the Analysis Services Configuration dialog box,
    • In the Server Configuration tab, select the server mode that you want to run in your SQL Server Analysis Services instance. In this example, I will use the traditional Multidimensional and Data Mining Mode. You also need to add the account that you are using to perform the installation so you can manage the Analysis Services instance later

      SQLSetup_SSAS_Mode

    • In the Data Directories tab, specify the clustered drive and the folder structure where the Analysis Services database and backups will be stored.

      SQLSetup_SSAS_Folders

    Click Next.
  16. In the Feature Configuration Rules dialog box, verify that all checks are successful. Click Next.

    SQLSetup_FeatureRulesCheck

  17. In the Ready to Install dialog box, verify that all configurations are correct and click Install.

    SQLSetup_Install

  18. In the Complete dialog box, click Close. This concludes the installation of a SQL Server Analysis Services instance on a WSFC.


    19. SQLSetup_Complete

At the completion of a successful installation and configuration of the node, you now have a functional (but not necessarily highly available) SQL Server Analysis Services instance on WSFC. Since our goal is high availability, we still have to add the second node to the clustered SQL Server Analysis Services instance.

You can verify the installation by reviewing the configuration using the Failover Cluster Manager console.

WSFC_SSAS
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Edwin Sarmiento Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Tuesday, December 17, 2019 - 11:48:11 AM - Tommy Ja Back To Top (83450)

Thank Edwin for your feedback,

I am configuring a SSAS cluster in multi-AZ in AWS.

I tried to find a Microsoft document regarding How to configure SSAS in multi-subnet environment or if this configuration is full supported by Ms.

Best regards,

Tommy Ja

Monday, December 16, 2019 - 11:06:21 AM - bass_player Back To Top (83438)

Tommy,

Yes, you can. The question is, why would you want to?

Monday, December 16, 2019 - 7:03:26 AM - Tommy Ja Back To Top (83435)

Hi Edwin,

first of all, thank you for this great article.

My question is regarding SSAS cluster on multi- subnet.

Currently I have a configuration with two nodes on two different subnet.

Can we cluster SSAS service in multi subnet configuration?

thank for your feedback,

Best regards,

Tommy Ja

Wednesday, November 20, 2019 - 5:00:21 PM - bass_player Back To Top (83150)

Manuel,

Are you trying to use the existing shared disks that the clustered SQL Server instance is already using? If that's the case, you won't be able to do so. Only one clustered resource can access the shared disk at any given point in time.

Tuesday, September 24, 2019 - 11:55:33 AM - Manuel Back To Top (82560)

Hi,

I have a cluster with SQLServer instance installed. I´m tryin to instal SSAS clustered but I get an error cheking the shared discs availability. The error says that I dont have shared discs but is not true, I have shared disks, oviously, because the SQLServer instance is clustered on shared disks. Can you help me? Best regards














get free sql tips
agree to terms