Quick Way to Check Which is the Active Node in a Failover Clustered Instance

If you have a SQL Server failover clustered instance, often you’ll want to know which server is acting as the active node. This is easy since you can just check in Windows Cluster Failover Manager which node is the current owner. But what if you have multiple clusters and you want to check them all? It’ll take too long to check them all individually in WCFM. There are a few different ways of doing this, but since we’re dealing with SQL Server, lets use a handy little SQL T-SQL query…

This is the query I use for this:

select 
	NodeName as node_name,
	case is_current_owner when 1 then N'Active' when 0 then N'Passive' end [node_status]
from sys.dm_os_cluster_nodes
where is_current_owner = 1
order by node_name;

If you want to see all the active and passive nodes, then just comment out the where clause.

This will come in handy in the scenario where you want to install some patches to all the passive cluster nodes and you need to know which ones are passive.

There you go, short and sweet.