Understanding the difference between the Windows Server cluster active node and the SQL Server cluster active node

October 10, 2012

Could there be scenario where a SQL cluster active node was running on a Windows cluster passive node? In other words, could your SQL Server application failover without the Windows Cluster failing over? This topic is worth understanding so that it does not catch you out at 3am – the favourite DBA callout time.

Environment

For the purpose of this tip we will use the following 2 node cluster configuration (active/passive):

Cluster Nodes (Active/Passive)

Failing over SQL Server

Within the Failover Cluster Manager tool you can move an application to another node. This is typically how you would test the SQL Server failover process. Expand the FailoverCluster Manager tree – cluster name – Services and Applications and right click your SQL Server Instance and select ‘Move this service or application to another node’.

Move this service or application to another node

After confirming the move you will notice the resources going down, moving to the other node and coming up again. If you connect to your SQL Cluster Cluster IP now and run the following T-SQL query, it will confirm the active SQL Server node.

Select ServerProperty('ComputerNamePhysicalNetBIOS')

The following diagram illustrates what we’ve achieved by moving the SQL Server application to the other node.

Cluster Moving an application

Let’s verify this by comparing the Windows and SQL Cluster active node information

  • Connect to your Windows cluster IP (xxx.xx.xx.01) using Remote Desktop. Note it is the default behaviour that when connecting to a Windows Cluster IP you are directed and connect to the Active Windows node.
  • Open Windows Server Manager from Administrative tools and note the computer name
  • Open SQL Server Management Studio and connect to your SQL Cluster name (MySQLClusterName)
  • Run the following query to confirm the SQL Cluster active node
 Select ServerProperty('ComputerNamePhysicalNetBIOS')

Compare Windows and SQL active node

Summary: By connecting to the Windows Cluster IP, we are automatically directed to Node A as it is the Windows Cluster Active node, which is technically correct. The Node A server has not gone down or experienced any server level error to cause a failover. However, as we’ve moved SQL Server to the Node B, from a SQL Server point of view, Node B is the active node.

Now let us try the following steps:

  • Connect to your SQL Server cluster IP (xxx.xx.xx.02) using Remote Desktop. Note it is the default behaviour that when connecting to a SQL Cluster IP you are directed and connect to the Active SQL node.
  • Open  Server Manager from Administrative tools and note the computer name
  • Open SQL Server Management Studio and connect to your SQL Cluster name (MySQLClusterName)
  • Run the following query to confirm the SQL Cluster active node
Select ServerProperty('ComputerNamePhysicalNetBIOS') 

Compare Windows and SQL active cluster node

Summary: By connecting to the SQL Cluster IP, we are automatically directed to Node B as it is the SQL Cluster Active node.

Failing over Windows Server

If we were to initiate a windows level failover by shutting down server (Node A), then the following scenario would occur. Both Windows and SQL would failover to the same node.

Windows Cluster Failover

Lessons Learnt

  • Do not assume the active node in a cluster, verify it using code.
  • Understand that the Windows Active node may not necessarily be the SQL Server Active node.
  • Initiating a true failover at windows level, would failover both Windows and SQL Server to to the other node.
  • When specifying a SQL Server name in your code or connection strings always specify the SQL Cluster name or IP. Do not use the Windows Cluster name or IP.

Thanks for reading.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: