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.
1: -- Shows you where the SQL Server failover cluster diagnostic log is located and how it is configured (Query 13) (SQL Server Error Log)
2: SELECT is_enabled, [path], max_size, max_files
3: FROM sys.dm_os_server_diagnostics_log_configurations WITH (NOLOCK) OPTION (RECOMPILE);
4:
5: -- Knowing this information is important for troubleshooting purposes
6: -- Also shows you the location of other error and diagnostic log files
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.
1: -- Get information about your cluster nodes and their status (Query 14) (Cluster Node Properties)
2: -- (if your database server is in a failover cluster)
3: SELECT NodeName, status_description, is_current_owner
4: FROM sys.dm_os_cluster_nodes WITH (NOLOCK) OPTION (RECOMPILE);
5:
6: -- Knowing which node owns the cluster resources is critical
7: -- Especially when you are installing Windows or SQL Server updates
8: -- You will see no results if your instance is not clustered
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.
1: -- Get information about any AlwaysOn AG cluster this instance is a part of (Query 15) (AlwaysOn AG Cluster)
2: SELECT cluster_name, quorum_type_desc, quorum_state_desc
3: FROM sys.dm_hadr_cluster WITH (NOLOCK) OPTION (RECOMPILE);
4:
5: -- You will see no results if your instance is not using AlwaysOn AGs
6:
7: -- Recommended hotfixes and updates for Windows Server 2012 R2-based failover clusters
8: -- http://support.microsoft.com/kb/2920151
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.