/sqlhadr-review

name: sqlhadr-review
description: Analyzes sys.dm_hadr_* DMV output to assess Always On Availability Group replica health, synchronization state, secondary lag, redo and log send queue sizes, and configuration gaps. Use this skill when an availability group is behaving unexpectedly, a secondary replica is lagging, data loss is a concern, a database appears stuck initializing after a failover, or you need a SQL-side snapshot of AG health to complement CLUSTER.LOG and ERRORLOG diagnostics. Applies 27 checks (H1–H28, with H21 retired and merged into sqlag-review F15) covering replica connectivity, data loss risk, recovery time, throughput, configuration, SQL 2016–2022 modern AG features, and seeding/initialization integrity.
triggers:
  - /sqlhadr-review
  - /hadr-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;

Also capture listener configuration for H20 (and `sqlag-review` F15, which covers read-only routing — H21 is retired):

SELECT ag.name AS ag_name, agl.dns_name, agl.port,
       aglip.ip_address, aglip.ip_subnet_mask,
       r.replica_server_name, r.read_only_routing_url
FROM sys.availability_group_listeners agl
JOIN sys.availability_groups ag ON agl.group_id = ag.group_id
JOIN sys.availability_group_listener_ip_addresses aglip
    ON agl.listener_id = aglip.listener_id
JOIN sys.availability_replicas r ON ag.group_id = r.group_id;

Column Reference

| Column | Source DMV | Notes | |--------|-----------|-------| | `connected_state_desc` | `dm_hadr_availability_replica_states` | CONNECTED or DISCONNECTED | | `role_desc` | `dm_hadr_availability_replica_states` | PRIMARY, SECONDARY, RESOLVING | | `synchronization_health_desc` (replica) | `dm_hadr_availability_replica_states` | NOT_HEALTHY, PARTIALLY_HEALTHY, HEALTHY | | `last_connect_error_number` | `dm_hadr_availability_replica_states` | 0 = no error | | `last_connect_error_description` | `dm_hadr_availability_replica_states` | Error text when non-zero | | `availability_mode_desc` | `sys.availability_replicas` | SYNCHRONOUS_COMMIT or ASYNCHRONOUS_COMMIT | | `failover_mode_desc` | `sys.availability_replicas` | AUTOMATIC or MANUAL | | `synchronization_state_desc` | `dm_hadr_database_replica_states` | NOT SYNCHRONIZING, SYNCHRONIZING, SYNCHRONIZED | | `db_sync_health` | `dm_hadr_database_replica_states` | NOT_HEALTHY, PARTIALLY_HEALTHY, HEALTHY | | `log_send_queue_size` | `dm_hadr_database_replica_states` | KB of log not yet sent to secondary | | `log_send_rate` | `dm_hadr_database_replica_states` | KB/s sent to secondary (0 = stalled) | | `redo_queue_size` | `dm_hadr_database_replica_states` | KB of log received but not yet redone | | `redo_rate` | `dm_hadr_database_replica_states` | KB/s being redone on secondary (0 = stalled) | | `secondary_lag_seconds` | `dm_hadr_database_replica_states` | Seconds secondary is behind primary | | `estimated_data_loss_seconds` | `dm_hadr_database_replica_states` | Potential data loss if primary fails now | | `estimated_recovery_time_seconds` | `dm_hadr_database_replica_states` | Seconds to redo queued log after failover |

---