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:
- 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 New SQL Server failover cluster installation link. This will run the SQL Server 2014 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 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.
- 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. And, don’t worry, you’re not the only one NOT reading the EULA.
- In the Setup Role dialog box, select the SQL Server Feature Installation option and click Next.
- 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.
- 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.
- 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
- 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.
- In the Cluster Disk Selection dialog box, select the clustered disks that you would like to use for your SQL Server Analysis Services instance.
- 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.
- 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.
- 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
- In the Data Directories tab, specify the clustered drive and the folder structure where the Analysis Services database and backups will be stored.
Click Next.
- 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
- In the Feature Configuration Rules dialog box, verify that all checks are successful. Click Next.
- In the Ready to Install 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 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.
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.
