Monitoring Availability Group Health

The dashboard is green, but a secondary has stopped keeping up. Availability group health needs more than the current role label. Watch connection state, synchronization, and the queues that show work waiting to move or redo.

Start With the Topology

List each availability group, replica, database, commit mode, failover mode, and listener. Mark which replica is primary and which are intended failover targets. A secondary that was never configured for automatic failover should not be judged by an automatic-failover checklist. Record the business recovery objective and the expected network path between replicas.

I ask what the group is supposed to protect before opening a DMV. Is the secondary for rapid failover, reporting, or a remote recovery site? Those purposes give different acceptable lag and response rules. The word healthy must describe the intended service, not only a green icon.

Check Availability Group Health by Replica Connection State

The replica state DMV reports role, connection, and synchronization health. Query it from an instance with the required permissions and understand that visibility differs by where you connect. A disconnected secondary cannot receive new log, even if the database looked synchronized earlier. Capture the replica server name with the state so a report does not leave the operator guessing which node needs attention.

I compare connection state with the endpoint and cluster health before touching the database. A network or service outage will not be fixed by rebuilding an index. The query below provides a first look at replica state on the connected instance.

SELECT
    ar.replica_server_name,
    rs.role_desc,
    rs.connected_state_desc,
    rs.synchronization_health_desc
FROM sys.dm_hadr_availability_replica_states AS rs
JOIN sys.availability_replicas AS ar
  ON ar.replica_id = rs.replica_id
ORDER BY ar.replica_server_name;

Read Per-Database Synchronization

A replica can host several availability databases with different health. Use the database replica state DMV to inspect each one. Synchronization state and synchronization health deserve separate attention. A suspended database, a seeding problem, or a lagging remote replica can sit behind a group-level summary. The query below connects the database and replica names to send and redo queues.

I look at each database before saying the whole group is ready. One forgotten database is enough to make a failover incomplete for an application. Keep the group_database_id join in the query so names come from availability group metadata rather than a guess based on local database IDs.

SELECT
    ag.name AS GroupName,
    ar.replica_server_name,
    adc.database_name,
    drs.synchronization_state_desc,
    drs.synchronization_health_desc,
    drs.is_suspended,
    drs.log_send_queue_size,
    drs.redo_queue_size
FROM sys.dm_hadr_database_replica_states AS drs
JOIN sys.availability_replicas AS ar
  ON ar.replica_id = drs.replica_id
JOIN sys.availability_groups AS ag
  ON ag.group_id = ar.group_id
JOIN sys.availability_databases_cluster AS adc
  ON adc.group_database_id = drs.group_database_id
ORDER BY ag.name, adc.database_name, ar.replica_server_name;

Interpret the Send Queue

The log send queue is log generated on the primary that has not yet reached a secondary. Its size can grow when the network, endpoint, or secondary cannot keep up. A single queue value is a snapshot. Compare a series of samples with workload and log generation rate. A queue that rises and later drains has a different meaning from one that grows through every normal workload cycle.

I check whether the secondary is connected and whether send rate is moving before blaming network bandwidth. Long transactions and bursts of log generation can change the picture. The query gives bytes in documented units; convert them consistently in monitoring. Do not invent a time-to-catch-up number from size alone without a measured rate.

Interpret the Redo Queue

The redo queue is log already received on a secondary but not yet applied there. A growing redo queue can delay readable secondary freshness and recovery work after a role change. Check redo rate, secondary storage, CPU, and concurrent read workload. Synchronous commit status does not mean redo has finished for every read scenario.

I compare redo queue behavior with the reporting queries running on the secondary. A busy report can compete for resources, but the cause needs evidence. Keep the observation window and workload in the incident note. A queue snapshot without time context is a photograph of traffic, not a travel forecast.

Check Failover Readiness Explicitly

Automatic failover depends on synchronous commit configuration, replica health, cluster quorum, and other conditions. A database synchronization state is necessary evidence, not the entire decision. Check the listener, application reconnection behavior, and the databases that must move together. Rehearse failover under an approved plan and record what users experienced.

I ask the team to test the application through the listener rather than connecting directly to a node. A failover can succeed at the database layer while an old client string keeps pointing to the former primary. The server and application both have to finish the trip.

Alert on Availability Group Health Trends and State Changes

Create alerts for disconnected replicas, suspended movement, unhealthy databases, and queues that stay above a workload-specific threshold. Separate an instantaneous spike from sustained lag. Send the message with group, database, replica, current state, and the last healthy time. An alert that says only “AG unhealthy” makes the first responder repeat your query.

I tune thresholds from measured normal behavior and the recovery objective. The same queue size can be harmless during a planned load and serious before a failover test. Keep maintenance windows visible so alerts remain actionable without hiding real outages.

Review After Every Change

Patch, storage, network, and application changes can affect availability group health. Recheck replica state and queues after each change, then watch through a normal busy period. Keep a short runbook for resuming suspended databases and escalating transport or cluster issues. Avoid automatic fixes that erase evidence or force a role change without a human decision.

The best availability group health check tells you what is connected, what is synchronized, and what work is still queued. I use those facts to decide whether the group can meet its recovery promise today, not merely whether it answered a status query.

Connect Availability Group Health to User Impact

An availability group warning can appear without a user interruption, while a quiet dashboard can miss an application pointed at the wrong replica. Include listener connectivity and application symptoms in the review. Compare replica state with backup preference and read-only routing. I record the time and role when a queue changes, since failover changes how that number should be read. Ask whether a secondary is behind because of transport, redo work, or its own workload. A queue value needs a trend and context before it becomes an alarm. Keep on-call action tied to recoverability rather than to one isolated counter.

Related reading on this blog: Find Instance Name for Availability Group Listener and T-SQL Script to List Automatic Seeding of Always On Availability Group.

Availability group health is not a role label, it is evidence that data movement and failover expectations are being met.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.