Replica states

Job details

Name:	Replica states
Platform:	Sqlserver
Category:	Cluster and Replication
Description:	Checks role state for all alwayson groups.
Long description:
Version:	1.3
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	model	List of alwayson groups (separated by comma) which will be ignored. You can use % (percent sign) to represent wild card characters.
status SECONDARY -> PRIMARY	0	Return status value (ALARM – 2, WARNING – 1, or OK – 0) when group status change from SECONDARY to PRIMARY.
status PRIMARY -> SECONDARY	1	Return status value (ALARM – 2, WARNING – 1, or OK – 0) when group status change from PRIMARY to SECONDARY
primary host for all groups		If set to “YES“ then the current host machine is preferred primary node for all groups.
status when not preferred primary in use	1	Return status value (ALARM – 2, WARNING – 1, or OK – 0) when preferred primary node (controlled by the “primary host” parameter) is not in use.

dbWatch Control Center Monitoring Job Summary

The monitoring job outlined in the XML data you provided is designed for dbWatch Control Center, focusing on managing and monitoring Microsoft SQL Server instances that employ AlwaysOn availability groups.

Purpose

Monitors the state and role transitions of AlwaysOn Availability Groups in Microsoft SQL Server.
Verifies changes in roles among PRIMARY and SECONDARY states, to ensure high availability and disaster recovery setups are functioning correctly.

Why

This monitoring task is critical for systems that depend on SQL Server AlwaysOn for high availability and disaster recovery. Proper monitoring helps in proactively managing, diagnosing issues, and preventing potential disruptions.
Role state monitoring ensures that the primary and secondary server roles are operating as intended, and will alert in case of any critical state transitions which might affect the availability or performance of the database systems.

Manual checking
In order to manually check AlwaysOn states, the following SQL commands can be used:

SELECT ag.name AS AG_Name, replica_server_name, ars.role_desc
   FROM sys.availability_groups AS ag
   JOIN sys.dm_hadr_availability_replica_states AS ars
   ON ag.group_id = ars.group_id
   WHERE ag.name <> 'model' --excluding model database or modify as required

Automated Monitoring

Execution at set intervals (appears scheduled to run every 5 minutes) ensures timely updates on the state.
Checks for role state changes from primary to secondary and vice-versa, and handles specific acknowledgments setting to true, which indicates required user intervention or acknowledgment upon state change alerts.
Incorporates error handling and logging procedures to manage exceptions and maintain records of historical states for reference.

Dependencies and Data Management
The job has self-dependencies on tables and procedures created for monitoring the AlwaysOn states, which includes automated cleanup in case of failures to keep the environment clean:

dbw_replica_states – A stored procedure that captures the details of replicas and their states.
dbw_alwayson_state_tab – A table to hold the availability groups’ current and historical states.

Output
The SQL job returns monitoring results in a structured format, which can be utilized for alert management systems or included in dashboards for a comprehensive view:

Data includes replica server name, group name, state, and the timestamp of the recorded state.
Results are designed to distinguish between current state and historical records.

Tables and Presentations

Presentation of results in dbWatch Report:

Replica Server Name	_. Group Name	_. State	_. Date	_. Type
---	---	---	---	---
Server A	AG1	PRIMARY	2023-10-01 12:00	current state
Server B	AG1	SECONDARY	2023-10-01 12:05	history state

The job setup and details provided denote a comprehensive approach to monitoring the key aspects of SQL Server AlwaysOn availability groups, helping database administrators ensure system reliability and data availability.