Identifying Windows Volumes on a Clustered SQL Server Instance with the DMVs
January 7th 2009 13:56
Identifying Windows Volumes on a Clustered SQL Server Instance with the DMVs
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_driv es. 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_driv es 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_driv es
ORDER BY DriveName
If I run this query against both nodes I get the following results in my environment:
Node1 Results
DriveName
L
M
N
O
Node2 Results
DriveName
R
S
T
U
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.
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_driv es. 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_driv es 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_driv es
ORDER BY DriveName
If I run this query against both nodes I get the following results in my environment:
Node1 Results
DriveName
L
M
N
O
Node2 Results
DriveName
R
S
T
U
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.
| 51 |
| Vote |
Add To: 
