SQL Query to Find SQL Cluster Nodes

Below is the SQL Query to find the sql cluster nodes

If(serverproperty('IsClustered'))=1
Begin
SELECT @@servername as DatabaseServer,NodeName,
case when serverproperty('instancename') is null then 'Default Instance - MSSQLSERVER' Else serverproperty('instancename') End as InstanceName,
Case when serverproperty('Computernamephysicalnetbios')=NodeName then 'Active' else 'Passive' End as NodeStatus
FROM sys.dm_os_cluster_nodes
order by Case when serverproperty('Computernamephysicalnetbios')=NodeName then 'Active' else 'Passive' End
End
Else
Begin
Select 'This is database Server is not clustered.'
End

Sample Output :

Advertisement