Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Identifying Windows Volumes on a Clustered SQL Server Instance with the DMVs


By:   |   Read Comments (1)   |   Related Tips: More > Clustering

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


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] 
FROM [DMV] 
WHERE [fieldX] value 
ORDER BY [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 
FROM sys.dm_io_cluster_shared_drives 
ORDER BY 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



Last Update:


signup button

next tip button



About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, January 14, 2009 - 11:29:43 AM - ThomasLL Back To Top

How do you get the Mounted Volume list?

 

Thomas


Learn more about SQL Server tools