Health state
Job details
| Name: |
Health state |
| Platform: |
Sqlserver |
| Category: |
Cluster and Replication |
| Description: |
Checks groups health state. |
| Long description: |
Checks groups health state. |
| Version: |
1.2 |
| 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 groups |
|
List of alwayson groups (separated by comma) which will be ignored. You can use % (percent sign) to represent wild card characters. |
Job Summary
- Purpose: The purpose of this job is to monitor the health state of availability groups and their associated replicas and databases within a SQL Server environment, specifically focusing on properties like synchronization state and reasons for any suspensions.
- Why: This job is important to ensure the continual operational health and readiness of SQL Server AlwaysOn Availability Groups. By tracking the health states, any potential issues can be diagnosed and addressed proactively, thus maintaining the high availability and disaster recovery capabilities of the server.
- Manual checking: You can check this manually in the database by issuing these SQL commands:
SELECT group_name, db_name, health_state, reason, replica_server_name, hist_date FROM dbw_alwayson_health_states_tab;
Technical Description
- “ms_health_states_job” falls under the “Cluster and Replication” category and executes specific SQL code to track and log the health status of SQL Server’s AlwaysOn groups.
- The implementation includes creating and populating a table “dbw_alwayson_health_states_tab” which records the health states, reasons for state changes, and other relevant details periodically as specified by the schedule.
Dependencies
- The job has self-dependency upon the “dbw_health_states” object.
- It depends on another object “dbw_alwayson_health_states_tab” which is essential for holding and managing the data related to the health checks.
Scheduled Execution
- This job is scheduled to run every 5 minutes as indicated in the “default-schedule” tag: “1,6,11,16,21,26,31,36,41,46,51,56 * * *”.
Execution Environment
- The job is designed to execute on instances of SQL Server that support AlwaysOn features (version higher than ‘2005′), which are active and where the engine edition supports AlwaysOn capabilities.
Output and Presentation
| Group Name |
Database Name |
Health State |
Reason |
Date |
| Group 1 |
DB1 |
HEALTHY |
No issues |
2023-01-01 00:00:00 |
| Group 2 |
DB2 |
NOT HEALTHY |
Synchronization lag |
2023-01-01 00:05:00 |
- The report generated provides a tabular presentation of the health states across different availability groups and databases, making it easier for database administrators to review and monitor the health status efficiently.
Upgrades and Maintenance
- New versions of the job, such as 1.1 and 1.2, include procedural updates aiming to refine the data collection and logging specifics.
Additional Considerations
- Acknowledgements: The job configuration allows for processing acknowledgements, implying that certain operator inputs or automated responses can be utilized to manage job execution feedback.
- Cleanup: In case of a failed task execution, cleanup procedures will be initiated to maintain environmental integrity and prepare for subsequent attempts.
Overall, “ms_health_states_job” is an essential component in a robust SQL Server maintenance plan, facilitating high availability and ease of maintenance by monitoring the health states of critical server components.