By: Tim Ford | Updated: 2009-01-05 | Comments (1) | Related: > Clustering
Problem
I have inherited a Microsoft SQL clustered server and I don't know much about it's logical structure. It is a two node, active-active cluster and I am trying to determine which of the logical volumes on the shared disk are associated with each SQL Server instance. Is there a simple method in T-SQL to determine this without having to fire up Cluster Administrator?
Solution
There most certainly is. One of the Dynamic Management Views (DMV) that released with SQL Server 2005 is sys.dm_io_cluster_shared_drives. Let's briefly discuss what DMVs are first for those that may be new to the concept before I answer your question.
Dynamic Management Views provide insight into internal Microsoft SQL Server metrics. They function just as database views do: they act as a logical object that is really simply an aggregate of data from joined tables and views, usually allowing for a simplified method of querying disparate data sets. In the case of DMVs these tables and views are internal to the database engine, are system objects, and are even in some cases hidden from the end user. Dynamic Management Views act as a window into the inner workings of the SQL Server instance. The process by which you query the DMVs is identical to how you would query any other view:
SELECT [field1], [field2], [fieldN] |
Now to address your question. sys.dm_io_cluster_shared_drives will return a listing of all logical drive volume names for every shared drive of the current server instance. If the server instance is not clustered, no results are returned. The structure of this DMV is extremely simple, just a single column, DriveName. I too have a two node, active-active cluster. This is a misnomer however. The new terminology would be multi-node, multi-instance cluster as you may have one or more instances on a give node (server) in a SQL Server cluster.
Below is the query code. Note that there are absolutely no parameters. The hardest part of constructing this query is remembering the name of the Dynamic Management View.
SELECT DriveName |
If I run this query against both nodes I get the following results in my environment:
|
Node1 Results |
|
|
|
Node2 Results |
|
|
These results in-hand I can ascertain that Node 1's instance is associated with the L, M, N, and O logical disk volumes. Node 2's instance is reliant upon the R, S, T, and U logical disk volumes. All this information was retrieved with the simplest of T-SQL queries, without the need to launch Cluster Administrator.
Next Steps
- Next time you need to find out the drives associated with your clustered SQL Server's, be sure to revisit this tip.
- Additional Microsoft SQL Server clustering tips are available on MSSQLTips.com:
About the author
Tim Ford is a Senior Database Administrator with MindBody.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2009-01-05
