Always On AG Configuration Monitoring

Why this problem matters

Drift in availability-group role, availability mode, or failover mode can alter high-availability expectations. The presence of a configuration does not prove every replica is operating under the intended protection model.

How to diagnose it

List the local replica role and all replica configurations by availability group. Distinguish dynamic state that is visible only from the local server from configuration metadata.

The query reads availability-group configuration and local dynamic replica state. Dynamic fields for remote replicas may be NULL when it is not run on the primary replica.

SELECT ag.name AS availability_group,
       ar.replica_server_name,
       ar.availability_mode_desc,
       ar.failover_mode_desc,
       ar.seeding_mode_desc,
       ars.role_desc,
       ars.connected_state_desc,
       ars.operational_state_desc
FROM sys.availability_groups AS ag
JOIN sys.availability_replicas AS ar
  ON ar.group_id = ag.group_id
LEFT JOIN sys.dm_hadr_availability_replica_states AS ars
  ON ars.replica_id = ar.replica_id
 AND ars.is_local = 1
ORDER BY ag.name, ar.replica_server_name;

A safe solution approach

Compare intended synchronous or asynchronous mode, failover policy, and endpoints with the documented architecture. Apply changes in a controlled manner after considering business impact, quorum, and failover testing.

Why continuous monitoring matters

Role and connection state can change quickly during maintenance, network problems, or failover. moon can monitor SQL Server state with low overhead and route relevant events to Slack, PagerDuty, or webhooks.