Windows Server Failover Cluster (on SQL Server) Monitoring

Overview

LogicMonitor can monitor Windows Server Failover Clusters (WSFCs) and SQL without triggering redundant SQL alerts. WSFCs are run from virtual IP (VIP) addresses and virtual network names (VNNs). There is no physical hardware at these addresses.

LogicMonitor’s SQL Server monitoring, which is handled by our Microsoft SQL Server package, primarily uses SQL database queries to poll internal SQL monitoring tables. When WSFC monitoring is run in conjunction, LogicMonitor will monitor the active SQL Server node through the WSFC. Failover will automatically switch monitoring to the active node. Hardware monitoring will alert for changes on the nodes. A good way of looking at cluster monitoring is that application level monitoring is handled on the cluster VNN (or VIP) and hardware monitoring is on the cluster nodes.

Compatibility

As of May 2020, LogicMonitor’s WSFC package is known to be compatible with clusters running on Windows Server 2012 and greater, running PowerShell version 5 or greater.

Note: Due to the many potential configurations of SQL Server with WSFC, LogicMonitor may not be able to provide out-of-the-box compatibility in all environments. Most environments will require some manual configuration.

Note: SQL nodes in a cluster that are also used as standalone SQL machines require manual configuration and may trigger redundant alerting.

Setup Requirements

Dependencies

• WMI. LogicMonitor uses the WMI protocol for automated SQL-named instance discovery and SQL services (browser, reporting services, agent, and so on) discovery.
• Microsoft SQL Server monitoring. This WSFC monitoring package is intended for use with our Microsoft SQL Server package. You must be running the most recent versions of the SQL Server LogicModules for successful operation.

Add Resources Into Monitoring

Add your WSFC into monitoring. Enter the fully qualified domain name (FQDN) as the IP Address/DNS name. The VIP address can optionally be used, but it is recommended that the FQDN be used if possible. For more information on adding resources into monitoring, see Adding Devices.

Provide Credentials

LogicMonitor must be able to provide the appropriate credentials in order to successfully access the WSFC. These credentials must belong to a user account with the minimum SQL Server permissions outlined in Microsoft SQL Server Monitoring.

Assign Properties to WSFC

The same properties that are assigned to SQL Server should also be present on the cluster VNN/VIP. See Microsoft SQL Server Monitoring for detailed information and instructions on setting these properties.

In addition to the set of SQL Server properties required, there are a few additional properties that must be set on the VNN. LogicMonitor strives to auto-discover all required properties; however, there may be cases where manual property configuration is required. For this reason, many of the properties listed next have both an automatic version (prepended with “auto.”) and a manual version. If both versions are set, manually assigned properties override automatically assigned ones.

For more information on assigning properties, see Resource and Instance Properties.

Property Name	Description	Example
system.categories	LogicMonitor assigns the value of “WSFC_VNN” to cluster VNNs and the value of “WSFC_Node” to failover cluster nodes. “SQL_Node” is assigned to nodes in a SQL cluster.
auto.wsfc.SQLInstanceNames or wsfc.SQLInstanceNames	Assigned to VNNs/VIPs, this property carries a comma-separated string of SQL instances serviced by this WSFC. Instance names for clusters are different from instance names for non-clustered SQL Servers. When SQL Server is used in a cluster, this property will override mssql.sql_server_instances.	DSLAB\Instance1, DSLAB\Instance2
auto.wsfc.active_node or wsfc.active_node	Assigned to VNNs/VIPs, this property carries the name of the current active node on the cluster. This property will only update when Active Discovery is run.	ClusterNode1.dslab.lm
auto.wsfc.name or wsfc.name	Assigned to VNNs/VIPs, this property carries the name of the cluster without the domain. It is useful in PowerShell commands.	ClusterVNN
auto.wsfc.ip or wsfc.ip	Assigned to VNNs/VIPs, this property carries the virtual IP address of the cluster. Currently only supports IPV4 addresses.	10.19.23.102
auto.wsfc.fqdn or wsfc.fqdn	Assigned to VNNs/VIPs, this property carries the FQDN of the cluster.	ClusterVNN.dslab.lm
auto.wsfc.nodes or wsfc.nodes	Assigned to VNNs/VIPs, this property carries a comma-separated string of the names of all nodes assigned to this cluster.	ClusterNode1,ClusterNode2,ClusterNode3.

When Running SQL Server Monitoring in Conjunction with WSFC Monitoring

If you are already monitoring SQL Servers in a WSFC using the Microsoft SQL Server package, the activation of WSFC monitoring will initially cause redundant data collection and alerting for the cluster nodes. This is because all nodes were previously assigned the system.categories. property of “MSSQL” in order to facilitate SQL Server monitoring. However, under the cluster monitoring configuration, only the VNN/VIP should have the “MSSQL” designation.

Ultimately, you’ll want to remove the “MSSQL” system.categories property designation from all cluster nodes, but do so with caution as the removal of this property will cause the SQL Server DataSources to disassociate, which, in turn, will cause all historical SQL Server collection data to be lost for the nodes.

For this reason, we recommend that you allow the SQL Server DataSources to remain associated with the cluster nodes for a period of time (with alerting disabled for the Microsoft SQL Server DataSources) in order to verify everything is working as expected and to build up data history for the VNN/VIP. Then, once you are comfortable, you can manually remove the “MSSQL” system.categories property designation from the cluster nodes.

LogicModules in Package

LogicMonitor’s package for WSFC consists of the following LogicModules. For full coverage, please ensure that all of these LogicModules are imported into your LogicMonitor platform.

Name	Type	Description
addCategory_WindowsFailoverCluster	PropertySource	For discovered clusters, assigns either “WSFC_VNN” or “WSFC_Node” to the system.categories property to differentiate between the cluster’s VNN and its nodes.
Microsoft_SQLServer_FailoverClusterNodeStatus	DataSource	Monitors the SQL Server nodes on a WSFC. Reports status and current active node; warns if a node is down.

When setting static datapoint thresholds on the various metrics tracked by this package’s DataSources, LogicMonitor follows the technology owner’s best practice KPI recommendations. If necessary, we encourage you to adjust these predefined thresholds to meet the unique needs of your environment. For more information on tuning datapoint thresholds, see Tuning Static Thresholds for Datapoints.