Clustering SSAS Part 2

Problem

In part 1 of this tip series, we discussed the preliminary steps for setting up a Windows Failover Cluster for SQL Server Analysis Services. In this tip, we will look at how to add a secondary node to the cluster for SQL Server Analysis Services.

Solution

To continue this series on Clustering SQL Server Analysis Services, we will look at adding a node to our clustered SQL Server Analysis Services instance. The process is similar in concept to adding a node to a SQL Server database engine failover clustered instance. So, if you've done something similar in the past, this should be a walk in the park for you. If not, there's always a first time.

To add a node on a clustered SQL Server Analysis Services instance:

Run setup.exe from the installation media to launch SQL Server Installation Center.
Click on the Installation link on the left-hand side. Click the Add node to a SQL Server failover cluster link. This will run the SQL Server Setup wizard.
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.
In the Add Node 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.
In the Product Key dialog box, enter the product key that came with your installation media and click Next.
In the License Terms dialog box, click the I accept the license terms check box and click Next. If you didn't read the EULA when you were installing the cluster, I doubt that you would read it this time when you're adding a node to it.
In the Cluster Node Configuration dialog box, make sure that you select the correct SQL Server instance to add this node to and validate that the information is correct. Click Next.
In the Cluster Network Configuration dialog box, verify that the IP address that you've previously assigned is correct. You won't be able to change the networking configuration since you're only adding a node to the cluster. Click Next.
In the Service Accounts dialog box, verify that the information is the same as what you have used to configure the first node. Provide the password to the SQL Server service account that you used. Click Next.
In the Feature Rules dialog box, verify that all prerequisite checks return successful results. Click Next.
In the Ready to Add Node dialog box, verify that all configurations are correct and click Install.
In the Complete dialog box, click Close. This concludes the installation of a SQL Server Analysis Services instance on a WSFC.

At the completion of a successful installation and configuration of the node, you can verify the installation by reviewing the configuration using the Failover Cluster Manager console.

Testing Connectivity to the Clustered SQL Server Analysis Services Instance

Proper connectivity testing should be done on an application that is outside the WSFC in itself. For example, to test connectivity to the clustered SQL Server Analysis Services instance, we will be using SQL Server Management Studio running on a client workstation. Remember to allow traffic on the Windows Firewall for all of the WSFC nodes on port 2383. PING and TELNET commands are your friend to verify connectivity to the clustered SQL Server Analysis Services instance.

Once you've verified connectivity, you can use SQL Server Management Studio to further validate application connectivity.

Deciding Whether or Not To Include Analysis Services with the Database Engine

In the first part of this series, we looked at installing and configuring SQL Server Analysis Services to be on its own clustered resource group. But how do we know whether to include it with the database engine or have it on its own dedicated clustered resource group (or Roles in Windows Server 2012) like what we did in the example?

A good rule of thumb is to review your recovery objectives (RPO/RTO) and service level agreements (SLAs.) The database engine and Analysis Services instance should have their own recovery objectives and SLAs independent of each other. By combining both the database engine and the Analysis Services instance on the same clustered resource group, you run the risk of causing a failure on one because of the other. For example, if the database engine has a higher recovery objective and SLA requirements compared to the Analysis Services instance, any issue with the Analysis Services instance will affect the availability of the database engine. Also, if the functionality provided by the Analysis Services instance is totally unrelated to the data in the database engine, it would not make sense to combine them in the same clustered resource group. For example, if the Analysis Services instance uses a different data source than the database engine running on the failover clustered instance, availability risks are compounded because any potential issue affects more than one functionality. I try to isolate the database engine from the Analysis Services instance as much as I possibly can by having them in different clustered resource groups. That would, of course, require having multiple virtual server names and virtual IP addresses (and sometimes multiple shared storage devices if dealing with failover clustered instances versus Availability Groups) but that is the price to pay to meet availability requirements.

Next Steps

Review part 1 and part 2 of the series on Install SQL Server 2008 on a Windows Server 2008 Cluster to prepare your WSFC.
Check out the other SQL Server Analysis Services Administration tips.

About the author

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

Article Last Updated: 2015-08-19

Clustering SQL Server Analysis Services Part 2