Show active node (failover cluster)

Get information about your cluster nodes and their status (Cluster Node Properties)

-- Get information about your cluster nodes and their status (Cluster Node Properties)
-- (if your database server is in a failover cluster)
SELECT NodeName, status_description, is_current_owner
FROM sys.dm_os_cluster_nodes WITH (NOLOCK) OPTION (RECOMPILE);