SQL Server Configuration Manager behavior in Clustered installation

According to Books Online reference "SQL Server Configuration Manager is a tool to manage the services associated with SQL Server, to configure the network protocols used by SQL Server, and to mange the network connectivity configuration".

One of the greatest things about SQL Server Configuration Manager (SSCM for the sake of brevity) is that you can manage different SQL Server settings in one tool, as opposed to SQL Server 2000 where different tools are needed to perform the same action. The following table summarizes the different configuration options in SQL Server 2000 and 2005:

However if you have made use of SSCM in a clustered installation you have probably noticed something weird...

Managing Network Components

Let's start our test with a SQL Server 2005 cluster with two cluster nodes: NEWYORK and BOSTON sharing a single SQL Server 2005 virtual instance called "SQL-2005":

Suppose we want to enable Named Pipes network protocol on "SQL-2005" virtual instance using SSCM, we will see the following result in NEWYORK node after performing the change and restarting virtual SQL Server instance:

Oddly enough, if we check now Named Pipes configuration on BOSTON cluster node we will see this protocol as disabled, even after restarting SQL Server service:

This information can be confusing but this is the expected behavior for SSCM in a clustered instance. The changed network parameter is stored on cluster Quorum and is only refreshed by the rest of cluster nodes during the virtual instance failover. This does not mean we need to move the SQL Server cluster group to each owner, we just need to remember the information will not persist on other cluster nodes until the service is moved to the rest of possible owners, and therefore, the configuration shown by local SSCM will not be accurate.

Managing Services

Let's now look at how managing clustered SQL Server services looks in SSCM. On the following image we can find Cluster Administrator and SSCM side by side running on "NEWYORK" node:

As you can see SQL Server instance "YUKON" is shown as Online in both Cluster Administrator and SSCM. Let's move now SQL Server 2005 Cluster Group resources from NEWYORK node to BOSTON node. After refreshing SQL Server services under SQL Server Configuration Manager we will see the services as Stopped:

This is happening because SSCM relies on Windows Service Control Manager (SCM) to show the services status and from a local (non-cluster) point of view, SQL Server service is stopped on NEWYORK and running on BOSTON.

If we want to know the status of SQL Server service from a cluster point of view (non-local) we will need to open the SSCM Microsoft management console using the SQL Server Network Name as parameter:

SQLServerManager.msc /computer:SQL-2005

This can be performed from any cluster node and will open a new console where SQL Server services status appear as it is actually shown in Cluster Administrator. This can also be used to check for the virtual server network configuration settings at cluster level.

Obviously, this will only inform of the status of the services under the specified virtual server after the /computer switch ("SQL-2005" in our example) but will not provide accurate information for other possible instances. In our example, BOSTON cluster node contains an additional non-clustered instance called "YUKON2" which is currently stopped. If we open SSCM in BOSTON using the abovementioned command, we will see the following information:

As we can see, while the information shown for virtual instance YUKON is accurate while the services status for local instance YUKON2 is not.

In this post I have tried to explain the limitations you should be aware of when configuring a SQL Server 2005 cluster using Configuration Manager. The behavior explained here can be easily understood keeping in mind how Windows Cluster registry information is replicated among nodes (more information on KB174070). Remember that only using the virtual SQL Server instance as SSCM startup parameter will guarantee accurate information from this tool.