One of my most frustrating moments is when I need to log into the active node of a cluster. I inevitably choose a passive node first, then wind up opening the Failover Cluster Manager to find the active node then finally log in to the correct server. Well, I finally took some time to get it right by using my trusty Central Management Server and T-SQL.
Running all of these scripts on a CMS will allow you to hit every SQL server in your domain. When I first decided to pull Server data using a script, I immediately thought of
@@SERVERNAME. However, this returns the cluster name (for clusters), not the active server node. What I really needed took a minor amount of research.
Another possible choice included sys.dm_os_cluster_nodes, but this had surprising limitations. While it does provide node names, if you try to query all servers using a CMS, the script can fail to merge the result sets, displaying the following error message. In my case, it also failed to return results for one entire cluster.
An error occurred while executing batch. Error message is: The result set could not be merged because the result schema did not match the schema from the first responding server.
The next option was to use SERVERPROPERTY to find the actual server name hosting SQL. When using a CMS, the script will also return the results of
@@SERVERNAME as a bonus.
SELECT [ActiveServer] = SERVERPROPERTY('ComputerNamePhysicalNetBIOS')
While this is good enough for some scenarios, I wanted a more targeted answer, so I kept digging. First, I wanted a script that would only show the active nodes in each cluster. I’ll mostly use this query to ensure I’m avoiding the primary server during maintenance and to verify which server is the active node when developers or management start asking questions and want immediate answers. (You know, as opposed to when they aren’t in a hurry…)
To get the exact results I want will require a bit of string parsing. I suggest you review SUBSTRING and CHARINDEX if you aren’t positive on how they work. In short,
Substring will display the server name up to where
Charindex determines the ‘\’ appears. This will provide a column consisting of only the cluster name. The next column will display the active node. I then filter out all servers other than clusters and exclude default instance names as well.
SELECT [ClusterName] = SUBSTRING(@@SERVERNAME,0,CHARINDEX('\',@@SERVERNAME)) ,[ActiveNode] = SERVERPROPERTY('ComputerNamePhysicalNetBIOS') WHERE SERVERPROPERTY('ComputerNamePhysicalNetBIOS') <> SUBSTRING(@@SERVERNAME,0,CHARINDEX('\',@@SERVERNAME)) AND @@SERVERNAME <> SERVERPROPERTY('ComputerNamePhysicalNetBIOS')
This final query returns every node in each cluster, with the last column denoting the active node. Useful when you need to remember which servers belong to what and your documentation has gone missing – or doesn’t exist yet.
SELECT [ClusterName] = SUBSTRING(@@SERVERNAME,0,CHARINDEX('\',@@SERVERNAME)) ,[Nodes] = NodeName ,[IsActiveNode] = CASE WHEN NodeName = SERVERPROPERTY('ComputerNamePhysicalNetBIOS') THEN '1' ELSE '' END FROM sys.dm_os_cluster_nodes WHERE SERVERPROPERTY('ComputerNamePhysicalNetBIOS') <> SUBSTRING(@@SERVERNAME,0,CHARINDEX('\',@@SERVERNAME)) AND @@SERVERNAME <> SERVERPROPERTY('ComputerNamePhysicalNetBIOS')
Summary I shared these scripts with my colleagues, who were very excited about not having to play “Guess the Active Node” anymore. There might be more eloquent ways to determine cluster nodes, but this method is perfect for my environment. I suggest saving the query to a code repository project in Solution Explorer or as a Snippet so that it’s always easily accessible.