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
- Purpose: The purpose of this monitoring job is to check the status of each member node of the current Windows Server Failover Clustering (WSFC) using Dynamic Management Views. It interacts with the database to periodically update and maintain a state table of cluster members.
- Why: This job is crucial for maintaining the health and status overview of WSFC clusters, especially in environments with critical applications running on SQL Server instances. By tracking state changes and maintaining historical data, the job aids in prompt detection of failures or status changes which might impact availability and performance.
- Manual checking: You can manually check the current state of WSFC members in the database by issuing the following SQL command:
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
- Category: Cluster and Replication
- Scheduled Execution: Frequently at fixed intervals – every minute.
- Acknowledgements: Requires acknowledgements upon execution.
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:
- Initially creates a table to hold current and historical state information.
- Deploys a procedure to handle status checking, ignoring specified members, updating state history, and handling exceptions.
- Manages and cleans historical data to maintain performance and relevance.
Reporting
- Report Title: Member State
- Purpose of Report: Displays current state and historical state changes of WSFC members.
- The reports generated by this job can be described in two sections:
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 |
- Output: Provides tabulated data for quick assessment and deep dive into each member’s availability and state changes over time. The outputs are essential for auditing and troubleshooting purposes.
Additional Configuration
- Execution Details Tracking: Updates are logged and tracked to provide a clear audit trail and facilitate troubleshooting.
- Failure Management: Handles failures gracefully by implementing a try-catch mechanism in SQL procedures, ensuring that the environment remains clean even after execution failures.
The design of this monitoring job ensures that critical WSFC environments are under constant surveillance, minimizing downtime and facilitating rapid response to potential issues.