sqlhadr-review

SQL Server Always On AG Health Review Skill

Purpose

Analyze output from the sys.dm_hadr_* DMV family to assess the health of one or more Always On Availability Groups. Applies 27 checks (H1–H28, with H21 retired and merged into sqlag-review F15 — see Category 4) across six categories:

• H1–H6 — Replica connectivity and role: detect disconnected replicas, resolving state, unhealthy synchronization health, replicas not synchronizing, last-connect errors, and failover mode mismatches
• H7–H11 — Data loss and recovery time: flag estimated data loss, excessive recovery time, secondary lag, redo queue buildup, and log send queue buildup
• H12–H16 — Throughput and performance: detect stalled redo rate, stalled log send rate, rate mismatch causing queue accumulation, multiple databases lagging on the same replica, and commit latency signals on sync-commit replicas
• H17–H22 — Configuration: async replica in unexpected position, no automatic failover replica, single-replica AG, missing listener, and automatic seeding in progress (H21 is retired — read-only routing absence is covered by sqlag-review F15)
• H23–H27 — Modern AG features: Contained AG DML misrouting, Cloud Witness inaccessible, Parallel Redo saturation, Read-Scale secondary missing RCSI, AG without database-level health detection (SQL 2012–2022+)
• H28 — Seeding and initialization integrity: database stuck in INITIALIZING synchronization state, particularly after a failover

Input

Accept any of:

• File path — path to a saved text/CSV file containing the DMV query output
• Inline paste — DMV result grid pasted directly into chat (tab- or pipe-delimited)
• Natural language description — description of AG symptoms ("secondary is 90 seconds behind", "replica shows NOT_HEALTHY")

Capture Query

Run the following on the primary replica to collect the required columns:

SELECT
    ag.name                              AS ag_name,
    ar.replica_server_name,
    ar.availability_mode_desc,
    ar.failover_mode_desc,
    ars.role_desc,
    ars.connected_state_desc,
    ars.synchronization_health_desc,
    ars.last_connect_error_number,
    ars.last_connect_error_description,
    drs.database_name,
    drs.synchronization_state_desc,
    drs.synchronization_health_desc      AS db_sync_health,
    drs.log_send_queue_size,
    drs.log_send_rate,
    drs.redo_queue_size,
    drs.redo_rate,
    drs.secondary_lag_seconds,          /* SQL Server 2016+ only; NULL on 2014 and earlier */
    drs.estimated_data_loss_seconds,
    drs.estimated_recovery_time_seconds
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar
    ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_availability_replica_states ars
    ON ar.replica_id = ars.replica_id
JOIN sys.dm_hadr_database_replica_states drs
    ON ar.replica_id = drs.replica_id
ORDER BY ar.replica_server_name, drs.database_name;