Finding Shared Drives on a Cluster

When working in a clustered environment, it is a common practice to open the Cluster Administrator to look at the shared drives for the SQL Server instance.  This can be performed from one of the participating nodes in the cluster. Using SQL Server Dynamic Management View sys.dm_io_cluster_shared_drives we can query and retrieve the shared drives for the SQL Server.

Note that the DMV returns only one column DriveName. The script returns no values when run on a standalone installation of SQL Server.

For the convenience of users, the script can be copied from the text at the end of the post.

In the above image, there are 7 rows indicating the 7 shared drives for the SQL Server fail-over cluster.

select * from sys.dm_io_cluster_shared_drives

— Bru Medishetty

Finding the nodes in a Failover Cluster

I was recently asked by a friend, how to find the names of the nodes that are participating in a failover cluster.

The Dynamic Management View sys.dm_os_cluster_nodes will give this information. The following script will return the list of Node names in the cluster.

Note that the query was run on a 3 Node cluster, so the result set displays the 3 Nodes in the cluster.

If the same command is run on a stand alone machine, it would not return any node name indicating it is not a cluster.

— Bru Medishetty