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.

Answer 1

Select ServerProperty('ComputerNamePhysicalNetBIOS') 

Answer 2

This works in newer versions:

SELECT * FROM fn_virtualservernodes(); 

full details at https://docs.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://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-cluster-nodes-transact-sql?view=sql-server-2017