Check AG Replica Health with sys.dm_hadr_availability_replica_states

calendar Sep 13, 2026 / · 9 min read · sql server sys.dm_hadr_availability_replica_states sys.dm_hadr_availability_group_states sys.availability_replicas sys.availability_groups sys.dm_hadr_database_replica_cluster_states sys.dm_hadr_database_replica_states always on synchronization health failover readiness scripts  ·
Share on: linkedin copy

An Always On availability group can show green across every tile in the graphical dashboard and still be one dropped connection away from a failover nobody planned for. sys.dm_hadr_availability_replica_states is the dynamic management view behind that dashboard tile, returning role, operational state, and synchronization health for every replica the local instance can see. Querying it from the right replica — the primary — is what turns that partial view into the full-group one this script is built around.

Purpose and Overview

On SQL Server, a replica's health lives in four paired columns on sys.dm_hadr_availability_replica_states: role_desc reports whether the local instance is currently Resolving, Primary, or Secondary; operational_state_desc reports where that replica sits in its own online/offline lifecycle; synchronization_health_desc rolls up to Not Healthy, Partially Healthy, or Healthy; and recovery_health_desc reports whether recovery on that replica's databases is still in progress or already online. Each of the four ships as a plain integer plus a readable _desc twin — the exact code tables behind them are laid out column by column in SQLShack's replica-and-database DMV walkthrough, and they're worth knowing before trusting any single value in isolation.

The role_desc and operational_state_desc pairing is the one most worth memorizing, because the same operational-state value means something different depending on the role sitting next to it. A Primary replica's operational_state_desc moves through Online, Pending, or Failed. A Secondary replica shows Online, Failed, or NULL — a Secondary doesn't carry a Pending state the way a Primary does. A replica stuck at role Resolving, the state a replica falls into mid-failover or when the underlying Windows Server Failover Cluster can't reach quorum, reports Offline, Pending_failover, Failed, or Failed with No Quorum instead. Reading operational_state_desc without checking role_desc first is how a perfectly normal Secondary NULL gets mistaken for a problem.

Where a replica sits also decides how much of the group a single query can see. That same SQLShack walkthrough notes that the graphical Always On dashboard — built directly on top of these DMVs — shows every replica in the group when launched from the primary, but only the one connected secondary's own view when launched from a secondary. A script against sys.dm_hadr_availability_replica_states inherits the identical scope: run it on the primary for the full-group picture the rest of this post assumes, not a partial one. The view also returns connected_state_desc, pre-resolved to a readable value the same way its three siblings above are, which is why the query below includes it without needing a CASE expression of its own.

Code Breakdown

The first query is the per-replica detail pass — one row per replica, decoded and joined back to the replica's server name and its availability group. The second rolls the same health picture up to a single row per group, the number worth checking first before drilling into which specific replica is the problem.

 1SELECT
 2    ag.name                                AS availability_group_name,
 3    ar.replica_server_name,
 4    ars.role_desc,
 5    ars.operational_state_desc,
 6    ars.connected_state_desc,
 7    ars.synchronization_health_desc,
 8    ars.recovery_health_desc
 9FROM sys.dm_hadr_availability_replica_states AS ars
10INNER JOIN sys.availability_replicas AS ar
11    ON ars.replica_id = ar.replica_id
12INNER JOIN sys.availability_groups AS ag
13    ON ar.group_id = ag.group_id
14ORDER BY ag.name, ar.replica_server_name;

Joining replica_id and group_id back to a name

sys.dm_hadr_availability_replica_states identifies a replica only by replica_id and its group only by group_id — neither is a human-readable name on its own. sys.availability_replicas supplies replica_server_name for the join on replica_id, and sys.availability_groups supplies the group's name for the join on group_id — the same two-catalog-view pairing SQLShack's own published queries use to resolve both IDs at once.

synchronization_health_desc and recovery_health_desc

synchronization_health_desc on this view is the replica's own value — Not Healthy, Partially Healthy, or Healthy — not yet aggregated across the group. recovery_health_desc is a rollup one layer down: per SQLShack's column notes, it mirrors the database_state column on sys.dm_hadr_database_replica_states, the database-level view one step further down the hierarchy. A replica can report recovery_health_desc as Online while an individual database inside it is still working through recovery — which is exactly why the two views are meant to be read together rather than either alone.

 1SELECT
 2    ag.name                                AS availability_group_name,
 3    ags.primary_replica,
 4    ags.primary_recovery_health_desc,
 5    ags.secondary_recovery_health_desc,
 6    ags.synchronization_health_desc
 7FROM sys.dm_hadr_availability_group_states AS ags
 8INNER JOIN sys.availability_groups AS ag
 9    ON ags.group_id = ag.group_id
10ORDER BY ag.name;

Rolling replica health up to one row per group

sys.dm_hadr_availability_group_states extends the same join pattern SQLShack publishes for it — an inner join to sys.availability_groups on group_id — and answers a different question than the first query: not which replica has a problem, but whether the group as a whole does. primary_replica names the server instance currently hosting the primary; primary_recovery_health_desc and secondary_recovery_health_desc report each side's recovery status independently, and read NULL on whichever side isn't applicable to the local replica. synchronization_health_desc at this level is explicitly documented as a rollup of every replica's own synchronization_health: Not Healthy means none of the replicas in the group are healthy, Partially Healthy means some but not all are, and Healthy means every replica in the group reports healthy. Reading this single row first, then dropping to the per-replica query only when it isn't a clean Healthy, is a faster triage path than reading every replica row cold every time.

Key Benefits and Use Cases

  • One query answers "which replica, which problem" — role_desc paired with operational_state_desc names the specific replica and its exact lifecycle state instead of a single traffic-light value.
  • The group-level rollup triages first — sys.dm_hadr_availability_group_states's synchronization_health_desc collapses every replica's status into one row, so a clean run needs no further drilling.
  • Follows the DMV's own hierarchy — replica health, database recovery health, and group-level rollup are read in the order the documentation defines them, rather than guessing which view owns which fact.
  • No configuration required — both queries run against a stock Always On deployment with no trace flag, Extended Events session, or extra setup.
  • Built for the primary — scoping the run to the primary replica matches documented behavior of the same dashboard these DMVs feed, not a guess.
  • Extends cleanly to database-level drill-down — the same replica_id/group_id keys that resolve a replica's name also resolve into sys.dm_hadr_database_replica_states for database-level detail when a replica-level flag alone isn't enough.

Performance Considerations

  • Permission floor is documented for the group-level view specifically: the reference for sys.dm_hadr_availability_group_states requires VIEW SERVER STATE on SQL Server 2019 and earlier, and the narrower VIEW SERVER PERFORMANCE STATE from SQL Server 2022 on; a companion view one layer down, sys.dm_hadr_database_replica_states, is documented elsewhere as needing the same VIEW SERVER STATE floor — confirm the exact requirement against each specific view's own page rather than assuming every Always On DMV shares one blanket permission.
  • Memory-only, resets on restart: like the rest of the sys.dm_hadr_* family, none of this is retained on disk — a service restart clears it, and history has to be captured by a scheduled job writing to a table, not read back from the view itself.
  • is_failover_ready cannot be trusted blindly: a real disaster-recovery walkthrough on sys.dm_hadr_database_replica_cluster_states found a forced-quorum failover where the surviving replica's synchronization state read SYNCHRONIZED and its is_failover_ready flag looked clean — but the replica was actually stale, because it had dropped to asynchronous during a network failure minutes earlier, and the guidance behind that DMV confirms is_failover_ready for a database is only meaningful if that database's host replica was up at the moment of the actual failure. A green flag captured after the fact does not retroactively confirm the state at the moment that mattered.
  • A 0 on an asynchronous replica is not automatically a problem: the same walkthrough shows a database on an asynchronous-mode secondary correctly reporting as not failover-ready by design — asynchronous replicas are never eligible for a no-data-loss failover, so checking availability_mode_desc before reading is_failover_ready avoids a false alarm on a replica that was never supposed to show ready in the first place.
  • synchronization_health_desc and role_desc together, not alone: a Resolving-role replica's synchronization_health can look identical to a lagging Secondary's, but Resolving points at the cluster itself being mid-failover or without quorum — a materially different situation from a Secondary that just needs time to catch up.

Practical Tips

  • Run both queries from the primary and schedule them as a recurring SQL Server Agent job writing to a history table, since neither view retains anything across a restart.
  • When the group-level query returns anything other than Healthy, drop to the per-replica query next, and only then to sys.dm_hadr_database_replica_states for the specific database's log-send and redo-queue detail if the replica-level flags alone don't explain the lag.
  • Before treating an is_failover_ready flag of 0 as an alert, check availability_mode_desc on the same replica first — asynchronous replicas are expected to show not-ready, and that's not the same signal as a synchronous replica failing the same check.
  • After any forced-quorum or manual failover, re-run the per-replica query rather than trusting the pre-failure dashboard state — the walkthrough above is a documented case of a stale replica reporting a synchronized state right up until the moment it actually mattered.
  • Alert on role_desc = 'RESOLVING' specifically, separate from a plain unhealthy synchronization_health_desc — a Resolving replica points at the underlying Windows Server Failover Cluster rather than at data movement, and the two need different responders.

Conclusion

sys.dm_hadr_availability_replica_states, joined back to sys.availability_replicas and sys.availability_groups and run from the primary, turns a dashboard's green tiles into the specific role, state, and health behind each one — and pairing it with the group-level rollup in sys.dm_hadr_availability_group_states gives a one-row triage check before ever reading a replica row individually. The is_failover_ready caution above is worth keeping close: none of these columns substitute for confirming replica state at the actual moment a failover happens, only for catching a problem well before that moment arrives.

References

Posts in this series