How to: Identify The Active Cluster Node

There are several ways to check for the cluster configuration, the fundamental one is access to the “Failover Cluster Management” console (“cluadmin.msc”)  in windows.  Another way is to execute a DMV scripts in your SQL Server Management studio.  We going to introduce 2 useful scripts in this article to help you identify your all your cluster nodes and also identify which on is the active one.

In order to get a list of all cluster nodes, please find how to use the DMV scripts for your SQL Server environment here.

Next, you can execute the following query to identify which cluster node your SQL Server is currently running on.


Some times execute a script is much more easier than clicking through the GUI in windows to get just a simple information.

How to: Check Cluster Node Configuration and Information

SQL Server 2005 introduces the DMV sys.dm_os_cluster_nodes, which has the same functionality as the function fn_virtualservernodes in SQL Server 2000. This DMV returns all of the server cluster nodes that are defined as part of the failover clustering instance, and can be used to verify the configuration. The user must have the VIEW SERVER STATE permission to access this DMV.

To execute, run the following query in a query window. For more information, see sys.dm_os_cluster_nodes.

-- for SQL Server 2000
SELECT * FROM fn_virtualservernodes

-- for SQL Server 2005 and above
SELECT * FROM sys.dm_os_cluster_nodes