Determine Active Node in SQL Failover Cluster

Question

Does anyone know how to determine the active node of a SQL Active-Passive Failover Cluster programmatically from T-SQL?

@@SERVERNAME only returns the virtual server name, which is identical from both nodes.

I don't plan to make any decisions based on the data - I trust the failover to do its thing - but I would like to include the information in an event log so I can tell which node in the cluster was active when the event occurred, or help determine if exceptions come up as a result of a failover.

Stu · Accepted Answer · 2013-01-27T20:36:16.797

38

Select ServerProperty('ComputerNamePhysicalNetBIOS')

edited Jan 27 '13 at 20:36

answered Apr 22 '09 at 17:14

Stu

15,675
4
43
74

If your driver can't handle it directly, convert it to a varchar: `SELECT convert(varchar(100), ServerProperty('ComputerNamePhysicalNetBIOS'))` – bradvido Apr 09 '14 at 19:08
Note that according to the [docs](http://msdn.microsoft.com/en-us/library/ms174396.aspx) you should be using `MachineName` rather than `ComputerNamePhysicalNetBIOS` for fail over clusters. Tested both values on a HA group and a standalone instance in each case it worked as expected but I assume there is some reason they recommend one over the other. – Richard Moss Oct 24 '14 at 07:12
I'm willing to bet there might be a difference with VMs. – Stu Oct 30 '14 at 12:12
1

I've expanded the answer. In my case (SQL2008R2) `MachineName` returns the network name associated with the instance, not the node. `ComputerNamePhysicalNetBIOS` returns the actual node name. If you're with SQL 2012 and on, better use the [dmv sys.dm_os_cluster_nodes](http://msdn.microsoft.com/en-us/library/ms187341%28v=sql.110%29.aspx) – Robert Cutajar Jan 02 '15 at 15:12

score 2 · Answer 2 · edited Sep 04 '19 at 17:42

This works in newer versions:

SELECT * FROM fn_virtualservernodes();

full details at https://learn.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-virtualservernodes-transact-sql?view=sql-server-2017

SELECT * FROM sys.dm_os_cluster_nodes;

full details at https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-cluster-nodes-transact-sql?view=sql-server-2017

score 0 · Answer 3 · answered Sep 19 '20 at 17:56

Try this, this checks if Server is the Primary server in the Availability Group and then based on this condition do X:

IF EXISTS (SELECT ars.role FROM sys.dm_hadr_availability_replica_states ars JOIN sys.availability_groups ag ON ars.group_id = ag.group_id WHERE ars.role_desc = 'PRIMARY')
    BEGIN
        SELECT 'PRIMARY' -- DO STUFF IF PRIMARY
    END
ELSE
    BEGIN
        SELECT 'NOT PRIMARY' --- DON'T DO STUFF
    END

Determine Active Node in SQL Failover Cluster

3 Answers3