Member state


Job details

Name: Member state
Platform: Sqlserver
Category: Cluster and Replication
Description: Checks the status of each member node of the current WSFC cluster based on Dynamic Management Views sys.dm_hadr_cluster_members.
Long description:
Version: 1.1
Default schedule: 1,6,11,16,21,26,31,36,41,46,51,56 * * *
Requires engine install: Yes
Compatibility tag: .[type=‘instance’ & databasetype=‘sqlserver’]/instance[ maj_version > ‘2005′ & hasengine=‘YES’ & alwayson_active=‘YES’ & engine_edition = ‘Microsoft SQL Server’ & eng_inst_priv = 0]

Parameters

Name Default value Description
ignore members List of alwayson member (separated by comma) which will be ignored. You can use % (percent sign) to represent wild card characters.
return status 2 Return status value (ALARM – 2, WARNING – 1, or OK – 0) when member is down.

Job Summary

SELECT name, member_type_desc, member_state_desc, hist_date FROM [dbw_member_state_tab] WHERE type = 1 ORDER BY hist_date DESC;

Job Configuration

Dependencies

Dependency Object Object Type Description Cleanup on Fail
master.sys.dm_hadr_cluster_members Dynamic Management View Utilized to fetch the current member states of the WSFC cluster. false
dbw_member_state_proc Procedure Main procedure handling the logic for state monitoring and logging. true
dbw_member_state_tab Table Stores the current and historical state data of cluster members. true

Implementation Details

This job implements operations through a series of SQL operations:

Reporting

Current Member State Report

Member Name Type State Last Checked
Member name Member type Current state Timestamp of last check

Member State History Report

Member Name Type State Date
Historical member name Member type Historical state Timestamp of state record

Additional Configuration

The design of this monitoring job ensures that critical WSFC environments are under constant surveillance, minimizing downtime and facilitating rapid response to potential issues.