24

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.

Improve this question

3 Answers 3

Reset to default
41 
Select ServerProperty('ComputerNamePhysicalNetBIOS')
Improve this answer
4
  • 1
    If your driver can't handle it directly, convert it to a varchar: SELECT convert(varchar(100), ServerProperty('ComputerNamePhysicalNetBIOS'))
    – bradvido
    Commented Apr 9, 2014 at 19:08
  • Note that according to the docs 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
    Commented Oct 24, 2014 at 7:12
  • I'm willing to bet there might be a difference with VMs.
    – Stu
    Commented Oct 30, 2014 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
    – Robert Cutajar
    Commented Jan 2, 2015 at 15:12
2

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

Improve this answer
1

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
Improve this answer
1
  • Thanks Gozzy, this works great. Is there any reason the query could not be simplified as SELECT * FROM sys.dm_hadr_availability_replica_states WHERE role_desc = 'PRIMARY'?
    – Belladonna
    Commented Feb 26 at 10:51

Not the answer you're looking for? Browse other questions tagged or ask your own question.