Getting Information on SQL Server Clustering

SQL Server 2005 aruably gives the best resources to see if the instance is clustered or not. And if so, what are the properties of the cluster. The DMVs that are related to the Cluster can be used for this. These DMVs are very useful for a new DBA joining a company who can easily find out the environment and the disk details without needing for a proper document on place.

To check if the instance is clustered:

select SERVERPROPERTY('IsClustered')

If this returns 1 then your environment is clustered.

To check the computer name of the node owning the cluster

Select SERVERPROPERTY('ComputerNamePhysicalNetBIOS')

The following DMVs would can be used for the cluster information.

sys.dm_os_cluster_nodes
This will return a row for each node in the failover cluster instance configuration. If the current instance is a failover clustered instance, it returns a list of nodes on which this failover cluster instance (formerly ‘virtual server’) has been defined. If the current server instance is not a failover clustered instance, it returns an empty rowset.

Sample Output:

SQLCLUSN1
SQLCLUSN2

sys.dm_io_cluster_shared_drives

This will return the information such as drive name of each of the shared drives if the current server instance is a clustered server. If the current server instance is not a clustered instance it returns an empty rowset.

Sample Output:

D
L
T
X