Identifying SQL Server Failover Cluster Shared Drives, Nodes, and Current Ownership

A busy database administrator (DBA) can often be blindsided with a request involving a SQL Server instance they've not had to look at for a while—or worse yet—an instance that is a suprise in their environment. Recently, I was contacted about a pending upgrade that was to be arranged in our domain for a legacy SQL Server 2008 failover cluster instance.

SQL 2008R2 and older is no longer under mainstream support with Microsoft; extended support agreements need to be purchased for SQL 2008 and SQL 2008R2, at this point. This being the case, I've set a mandated review process for any SQL Server database with a scheduled upgrade to determine if we can migrate the database to SQL Server 2012 or 2014, which at the time of this article, are the only two version of SQL Server under mainstream support with Microsoft. Since I'm primarily involved with healthcare systems, the independent software vendors (ISVs) are under Food and Drug Administration review for the majority of the applications they produce and tend to lag behind other sectors in adoption of new technologies, so this is always a tedious and sensitive process. 

Related: Planning and Implementing a SQL Server Cluster

This particular instance had been an ISV-managed instance since it was originally constructed. I had built the cluster, installed SQL Server, analyzed and corrected for violation of best practice configuration settings after the vendor installed the databases and then placed the instance into monitor and maintain mode: ensure backups were valid and restorable, monitored for performance, and performed other automated tuning processes. This was always a buggy product from the vendor which resulted in frequent reboots of the server despite the DBA Team coaching that this is not a performance tuning process that should be attempted as a first-level response to issues of performance. (We all have systems such as this in our lives don't we?)

Related: Disk Configuration for Failover Clustering

Knowing the vendor would not heed advice, the automated processes were successfully running, and the environment was coasting along we had not needed to touch the instance for some time. That means details get lost. If I needed to have an educated discussion with the vendor about possibly moving to a new version of SQL Server, as well as possibly doing away with use of a physical cluster in lieu of a virtualized cluster (another consideration we had made elsewhere in the previous year with almost all instances in this environment), I needed to be informed of the current cluster state. That is where the clustering Dynamic Management Views come into play.

Clustering Dynamic Management Views

I consult the clustering Dynamic Management Views (DMVs) to answer three specific questions:

What I wanted to do in this case is analyze the environment at a very high level so I could engage with the ISV on the subject of moving to a virtualized instance of SQL Server 2012 or newer running on a VMWare cluster (which would satisfy the high-availability needs of this application's data environment.) There were three distinct simple queries involved in answering that question:

SELECT DrivenameFROM sys.dm_io_cluster_shared_drives;-- Get all cluster node machine namesSELECT NodeNameFROM sys.dm_os_cluster_nodes;-- What machine owns the cluster resourcesSELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS owning_node;

sys.dm_io_cluster_shared_drives is perhaps one of the most-accurately named DMVs in SQL Server. It returns exactly what it states: the listing of all shared drives in the clustered SQL instance being queried. Likewise, the aptly-named sys.dm_os_cluster_nodes provides information on all the nodes (physical or virtual) that are the foundation of the clustered SQL instance. Finally, I do need to drop out of the DMVs and query a server property, ComputerNamePhysicalNetBIOS, which presents the name of the physical or virtual server hosting a particular SQL instance, clustered or not.) The results of these three simple queries, answering the three questions itemized, are presented below:

In this particular case, the K and L drives are the only two drives shared between nodes SQLNODE01 and SQLNODE02 in the SQL Server failover cluster. Currently, it's SQLNODE01 that owns the clustered instance of SQL. 

Being a DBA: It's Not Just About Keeping the Lights On

Armed with this information, I can then proceed to analyze the physical enviroment for this instance and make node of the requirements needed for the current implementation to run as-is. I'm also able to use this information to determine the cost savings in terms of hardware and licensing in the current state as a physical server compared to migrating the instance to a virtualized environment. After all, when it comes to being a database administrator, it's not just about keeping things running—it's also about constant analysis of the SQL environment as a whole to determine opportunities for streamlining, optimizing, standardizing, and saving your company or customers money.

Physical clusters may have made sense three or four years ago. That may not be the case now with advances in virtualization and cloud. It would be great if we as DBAs could build something sustainable and move on to the next project, but we must constantly be on the watch for improvements and enhancements. It makes our lives busy, but also exciting, doesn't it?