How to Identify SQL Server Cluster Node and Shared Drives Information Using TSQL Queries

When you have a Multiple SQL Server Cluster Configuration and multiple shared drives it becomes tough to know which SQL Server Failover Cluster Instance is using which Shared Drives. This article has the collection of TSQL queries which can help you identify Cluster Node and Shared Drive Information when you have Multiple SQL Server Failover Cluster Configurations.

Identify is your SQL Server Clustered using TSQL Query

The below T-SQL query will help you identify where the SQL Server Instance you are supporting is clustered on non cluster instance.

SELECT SERVERPROPERTY('IsClustered')
GO

The above query will return either of the below mentioned options.

• If clustered the above query will return the value as “1”
• If it’s a not clustered instance then the above query will return the value as “0”

Identify SQL Server Machine Name, Instance Name and Server Name using TSQL Query

Execute the below query to identify SQL Server Machine Name, Instance Name and Server Name

SELECT SERVERPROPERTY('MachineName')
GO
SELECT SERVERPROPERTY('InstanceName')
GO
SELECT SERVERPROPERTY('ServerName')
GO

TechNet explanation for each of the above property is:-

• MachineName: – Windows computer name on which the server instance is running. For a clustered instance, an instance of SQL Server running on a virtual server on Microsoft Cluster Service, it returns the name of the virtual server.
• InstanceName: – Name of the instance to which the user is connected.
• ServerName: – Both the Windows server and instance information associated with a specified instance of SQL Server.

Identify the Node on which SQL Server Instance is Currently Running using TSQL Query

Execute the below query to identify the node on which SQL Server instance is currently running.

SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS')
GO

TechNet explanation for the above property is: –

• ComputerNamePhysicalNetBIOS: – NetBIOS name of the local computer on which the instance of SQL Server is currently running. For a clustered instance of SQL Server on a failover cluster, this value changes as the instance of SQL Server fails over to other nodes in the failover cluster. On a stand-alone instance of SQL Server, this value remains constant and returns the same value as the MachineName property.

Identify Nodes of SQL Server Failover Cluster using TSQL Query

You can identify the nodes of SQL Server Failover cluster either by using an inbuilt function or using a DMV. Below mentioned queries will return the same result.

/* Using In-Built Function Identify Nodes of SQL Server Failover Cluster */
SELECT * FROM fn_virtualservernodes()
GO

/* Using DMV Identify Nodes of SQL Server Failover Cluster */
SELECT * FROM sys.dm_os_cluster_nodes
GO

Identify all drives on the node and free space using TSQL queries

Now that you know the SQL Server Nodes the next thing which you would like to know will be the different drives which are available and the free space in each drive. This information you can quickly reterive using XP_FIXEDDRIVES undocumented extended stored procedure.

Use master
GO
EXEC XP_FIXEDDRIVES
GO

In the above snippet you could see that there are 9 drives available on the node. However, at this point you wont know which are the drives used by SQL Server Cluster.

Identify Shared Drives used by SQL Server Cluster using TSQL Queries

You can identify the shared drives used by SQL Server Failover Cluster Instance either by using an inbuilt function or using a DMV. Below mentioned queries will return the same result.

/* Using In-Built Function Identify Shared Drives Used by SQL Server Failover Cluster Instance */
SELECT * FROM fn_servershareddrives()
GO

/* Using DMV Function Identify Shared Drives Used by SQL Server Failover Cluster Instance */
SELECT * FROM sys.dm_io_cluster_shared_drives
GO

In the above snippet you could see that “F”, “J”, “I” an “H” is shared drives which belong to currently Instance of SQL Server Cluster.

Conclusion

All the above mentioned queries are very useful to a database administrator who has to manage a multiple failover cluster configurations in a very large SQL Server Environment.

References

Read more about SERVERPROPERTY on TechNet
Read more about fn_virtualservernodes() on TechNet
Read more about sys.dm_os_cluster_nodes on TechNet
Read more about fn_servershareddrives on TechNet
Read more about sys.dm_io_cluster_shared_drives on TechNet