Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Simple SQL Server

I started out working with Microsoft Access and SQL Server back in 2000 as the only employee doing IT full-time, and worked most of my career where “big fish in a little pond” was an overstatement. Learning is scarce when you do everything and don’t work with anyone who knows more than you. In 2010 I was plunged into the Ocean and grabbed onto anything I could find to stay afloat. I wasn’t going to simply run scripts I didn’t understand, so I learned the DMVs and system tables in the scripts I found and rewrote them all. Now, I know enough where I can start giving back to a community that saved me from drowning.

Cluster Info

This script is so simple you’ll start off by asking why you’d ever use it.  Then you’ll use it and wonder why no one gave this to you as part of your new-hire orientation at your first DBA job.  The truth is that in larger environments there will always be times when you’re asked what the active node is, are two instances running on the same node, what nodes are in that cluster, what drives belong to what instance, etc…  Here’s your answer.

SELECT VirtualServerName = SERVERPROPERTY('ServerName')
    , ActiveNode = SERVERPROPERTY('ComputerNamePhysicalNetBIOS') 
    , PassiveNode = CASE (SELECT COUNT(*) FROM sys.dm_os_cluster_nodes WHERE NodeName <> SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))
        WHEN 0 THEN 'Stand-Alone'
        WHEN 1 THEN (SELECT NodeName FROM sys.dm_os_cluster_nodes WHERE NodeName <> SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))
        ELSE Cast((SELECT COUNT(*) FROM sys.dm_os_cluster_nodes WHERE NodeName <> SERVERPROPERTY('ComputerNamePhysicalNetBIOS')) as VarChar(2)) + ' Passive Nodes'
        END

SELECT PassiveNodes = NodeName 
FROM sys.dm_os_cluster_nodes 
WHERE NodeName <> SERVERPROPERTY('ComputerNamePhysicalNetBIOS') 

SELECT ClusterDrives = DriveName 
FROM sys.dm_io_cluster_shared_drives
ORDER BY DriveName

Filed under: Scripts, SQL Server, System State Tagged: cluster, drive, node, server

Comments

Leave a comment on the original post [simplesqlserver.com, opens in a new window]

Loading comments...