How to Quickly Review your SQL Server Cluster Configuration on Windows Server 2003

Article
01/21/2009

Working at Microsoft Customer Support Services (CSS), we are frequently asked by our customers to check their SQL Server cluster configuration. Event though the task of installing and configuring a SQL Server cluster have been simplified every new Windows Server version, it is not strange to find clusters with non-optimal configurations. Part of this problem relies on the amount of settings the IT professional has to take into account when setting up a new cluster. Fortunately, Windows Server 2008 has introduced significant changes into the cluster setup, configuration and maintenance with the aim of simplifying most of the hard work for the IT professional.

The goal of this post is to provide a quick guide for checking for some of the most common mistakes when configuring SQL Server on a Windows Server 2003 failover cluster. For this task we will use the CLUSMPS.EXE utility included in MPSReports.

MPSReports to the Rescue

Microsoft Product Support’s Reporting Tools (aka MPSReports) is probably one of the most popular tools among Microsoft Support professionals. This tool is used in most of the support incidents to gather SQL Server and general Windows logs and settings, including the failover cluster ones. When executing MPSReports (for SQL Server you will have to download and run MPSRPT_SQL.exe executable file) we are actually executing a group of scripts and executables behind the scenes; one of these executables, CLUSMPS.EXE, will dump the SQL Server cluster configuration to a text file in the form of SERVERNAME_CLUSTER_MPS_INFORMATION.txt.

The setup of MPSReports is pretty straightforward but is always recommended you check the ReadMe.txt file to have a better understanding of what test are performed and what log files are generated. You can find this ReadMe.txt here and additional information about this tool here.

After running this tool we will find the cluster configuration file into the resultant CAB file in the MPSReports folder (default path for this folder is %WINDIR%\MPSReports\). We can optionally execute CLUSMPS.EXE directly by opening a Command Prompt windows on the active cluster node and running the tool from %WINDIR%\MPSReports\SQLServer\BIN\x86 path. The following picture describes the command-line options available:

It is important to note that neither CLUSMPS.EXE nor the rest of the tests performed by MPSReports will change the Windows or SQL Server configuration in any way.

General Cluster Configuration

The first section of the resultant SERVERNAME_CLUSTER_MPS_INFORMATION.txt file provides a general overview of what nodes are part of the cluster and what cluster groups and resources are located on these nodes. This is an at-a-glance view of the information we can find by using Windows Cluster Administrator (cluadmin.exe) tool.

This section includes Nodes Information, Group and Resource Information, which will be useful if you need to dig deep for cluster events into cluster.log, (see my previous post for more information on this) and Resource to Guid Information.

Network Configuration

The Network Configuration section includes Cluster Networks Information, Cluster Networks Priority, Network Interfaces, and Network Interfaces Binding Order. It is important to do not overlook these settings. In most of the cases not configuring network settings correctly will cause your SQL Server instance to do not perform optimally.

Cluster Networks Information will show a list of the networks recognized by Microsoft Cluster Services (MCS). On a typical cluster configuration, each one of the cluster nodes will include at least two Network Interface Cards (NICs): One is used for internal cluster communications between nodes (commonly referred as "Heartbeat” network) and the other is used for external communications (“public” network).

Pay attention to the Network Role column, by default you will see that both the Heartbeat and Public network are configured to support All Communications (“All Comm”) but it is a best practice to modify the Heartbeat configuration so it listens to Internal Communications only. To do so open the Heartbeat network properties on the Cluster Administrator utility (cluadmin.exe) and select the option Internal Cluster Communications Only (Private Network) :

Cluster Networks Priority shows the priority of each one of this networks. As a best practice, ensure that the private network is listed with the highest priority. This setting can be changed by accessing the cluster properties on Cluster Administrator utility:

The Network Interfaces Binding Order determines how each network protocol is bound to each services that make use of it. As a general rule, the most-used protocols should be listed first. The recommended binding order for a Windows cluster is as follows:

- - - - External public network
      - Internal private network (Heartbeat)
      - Other connections, if any

Network bindings can be configured using the Advanced > Advanced Settings option in Control Panel > Network and Dial-up Connections.

The following Knowledge Base article provides specific guidelines to configure network components on a Windows cluster: Recommended private “Heartbeat” configuration on a cluster server, Server Clusters: Network Configuration Best Practices for Windows 2000 and Windows Server 2003.

You can also find best practices to configure the different network components on a cluster on the following TechNet link: Network Configuration Best Practices for Windows 2000 and Windows Server 2003.

Cluster Resources Dependencies

On each one of the cluster groups, it is frequent to find resources that depend on others to function properly. These dependencies are depicted on the Dependency Tree List and Dependency List sections of the SERVERNAME_CLUSTER_MPS_INFORMATION.txt file. We usually do not need to take SQL Server cluster dependencies into account, these are automatically configured at setup time and should not be changed from defaults but it is worth the time to check our current dependencies tree against the recommended settings. You can find this information and other special considerations on Knowledge Base article KB835185.

Here you can find default dependencies for the SQL Server 2000 failover cluster components:

SQL Server (SHILOH) { SQL Server }
+(1)-----Depends On-> SQL Network Name(SQL2000) { Network Name }
+(2)-----Depends On-> SQL IP Address1(SQL2000) { IP Address }
+(1)-----Depends On-> Disk S: { Physical Disk }

SQL Server Agent (SHILOH) { SQL Server Agent }
+(1)-----Depends On-> SQL Server (SHILOH) { SQL Server }
+(2)-----Depends On-> SQL Network Name(SQL2000) { Network Name }
+(3)-----Depends On-> SQL IP Address1(SQL2000) { IP Address }
+(2)-----Depends On-> Disk S: { Physical Disk }

SQL Server Fulltext (SHILOH) { Microsoft Search Service Instance }
+(1)-----Depends On-> SQL Server (SHILOH) { SQL Server }
+(2)-----Depends On-> SQL Network Name(SQL2000) { Network Name }
+(3)-----Depends On-> SQL IP Address1(SQL2000) { IP Address }
+(2)-----Depends On-> Disk S: { Physical Disk }

And here are the default dependencies for the SQL Server 2005 failover cluster components:

SQL Server (YUKON) { SQL Server }
+(1)-----Depends On-> Disk Y: { Physical Disk }
+(1)-----Depends On-> SQL Network Name (SQL2005) { Network Name }
+(2)-----Depends On-> SQL IP Address 1 (SQL2005) { IP Address }

SQL Server Agent (YUKON) { SQL Server Agent }
+(1)-----Depends On-> SQL Server (YUKON) { SQL Server }
+(2)-----Depends On-> Disk Y: { Physical Disk }
+(2)-----Depends On-> SQL Network Name (SQL2005) { Network Name }
+(3)-----Depends On-> SQL IP Address 1 (SQL2005) { IP Address }

SQL Server Fulltext (YUKON) { Generic Service }
+(1)-----Depends On-> Disk Y: { Physical Disk }

Cluster Service Account Rights

The Account Privileges section includes a list of the different right required by the Microsoft Cluster Service account as well as any missing permissions. The following example illustrates an scenario where two of the permissions are missing for this account:

_____________________________________________
|/////////////////////////////////////////////|
|// Current Effective Rights //|
|/////////////////////////////////////////////|

Act as part of the operating system.
Back up files and directories.
Restore files and directories.
Adjust memory quotas for a process.
Increase scheduling priority.
Log on as Service.
Debug programs.
Manage auditing and security log.
Access this computer from the network.

_____________________________________________
|/////////////////////////////////////////////|
|// Missing Current Effective Rights //|
|/////////////////////////////////////////////|

Load and unload device drivers.
Impersonate a client after authentication.

The required permissions should be configured on each one of the cluster nodes. Failure in configuring proper rights for the Cluster Services account will cause you SQL Server cluster to show permissions error message or to fail completely during startup or failover operations. For a list of what permissions are required by this account in Windows Server 2003 please, refer to the Knowledge Base article KB269229.

Other Information

If you plan to make use of Distributed Transaction Services within SQL Server you should plan way ahead how to install the Microsoft Distributed Transaction Coordinator (MSDTC) cluster resource. The Knowledge Base article How to configure Microsoft Distributed Transaction Coordinator on a Windows Server 2003 cluster is the best place to start. In case you need to revert or modify an existing MSDTC clustered installation so it can be used by SQL Server, refer to this link.

For a more in-depth analysis of the general SQL Server cluster does and don’ts I highly recommended to check the following Microsoft Knowledge Base article: Clustered SQL Server do’s, don’ts, and basic warnings.

The following TechNet link contains a quick reference guide to install and configuring new Microsoft cluster in Windows Server 2003: Quick Start Guide for Server Clusters. For specific SQL Server guidelines refer to SQL Server Books Online.

During this post I have tried to summarize the key points to look for when checking SQL Server cluster configuration on Windows Server 2003. This is not a complete checklist and working over the different links provided in the post will give you a more complete information. The new Cluster Validation Wizard introduced in Windows Server 2008 cluster simplifies and reduced significantly the overhead associated with cluster configuration in previous Windows versions. If you haven’t had the opportunity to work with Server 2008 cluster, I encourage you to setup your lab and get familiar with this new cluster model.