Note: The three queries I am discussing today are not particularly interesting or useful if you are not using either a traditional, failover cluster instance (FCI), or an AlwaysOn Availability Group.
For Day 7 of this series, we start out with Query #13, which is SQL Server Error Log. This query retrieves information from the sys.dm_os_server_diagnostics_log_configurations dynamic management view with the current configuration for the SQL Server failover cluster diagnostic log. Query #13 is shown in Figure 1.
Figure 1: Query #13 SQL Server Error Log
This query basically shows you where all the SQL Server related error and diagnostic logs are located. Knowing this can be very helpful if you need to troubleshoot problems with various parts of SQL Server. For example, if the SQL Server database engine will not start, opening the SQL Server Error log in a text editor will often give you some very useful information about what the problem is.
Query #14 is Cluster Node Properties. This query retrieves information from the sys.dm_os_cluster_nodes dynamic management view with one row for each node in the failover cluster instance configuration. If you have a standalone instance of SQL Server, this query will return an empty rowset. Query #14 is shown in Figure 2.
Figure 2: Query #14 Cluster Node Properties
This query is useful because it lets you identify each node in the cluster, see it’s current status, and see which node currently owns the instance.
Query #15 is AlwaysOn AG Cluster. This query retrieves information from the sys.dm_hadr_cluster dynamic management view about the Windows Server Failover Clustering (WSFC) quorum, if one exists on the node that is hosting the SQL Server instance, and you are using an AlwaysOn AG. Query #15 is shown in Figure 3.
Figure 3: Query #15 AlwaysOn AG Cluster
This query is one way of detecting the fact that there is an AlwaysOn AG in place on your instance, and then seeing what quorum type is being used.
If you are going to be using either traditional FCIs or AlwaysOn AGs (which require WSFC, but not shared storage), it is very important that you use Windows Server 2012 or newer for your operating system. The clustering code was basically rewritten in Windows Server 2012, and it was further improved in Windows Server 2012 R2.