Availability Groups

Overview

An Availability Group is a two-stage pipeline, per database, per secondary. Log records are hardened on the primary and sent, then they are hardened on the secondary and redone. Two queues sit between those stages, and the two numbers a DBA is actually accountable for are one queue each:

• How much committed work a failover would lose right now. That is the send side.
• How long the survivor would spend catching up before it could serve anybody. That is the redo side.

Neither is a column in any DMV. Both are derived, which is the whole reason this report exists.

What the product had before this page was an Overview panel listing replicas, roles and synchronization health. That is a complete answer to is it up and no answer at all to how far behind, which is the question asked during an incident.

There is no auto-refresh. A redo queue moves in seconds, but so does the cost of asking, and a page that requeried six DMVs on a timer would spend more of the primary’s time than it saved. Refresh is a button.

---

Where to find it

Route	How
Server tree	Right-click the server → Instance Level Reports → Availability Groups

The page title reads Availability Groups for <server name>.

---

Requirements

• SQL Server 2012 or newer. The availability views do not exist before it.
• Always On enabled on the instance. It is switched on per instance in SQL Server Configuration Manager and the service has to be restarted.
• VIEW SERVER STATE on the instance, for the HADR DMVs.
• The query timeout is 60 seconds. It reads six DMVs and normally answers instantly.

Every one of those can be absent and the page still answers rather than failing. Each is a different message, because they are different problems.

Nothing on this page changes anything. There is no resume, no failover, no button that touches a production replica. Every action copies a script for you to read somewhere else.

---

Which replica you are connected to

This is the single most important thing to understand about the page.

The send queue only means anything on the primary. sys.dm_hadr_database_replica_states returns rows on a secondary too, but the columns describing what has not yet been sent are the primary’s view of the world. A secondary can see rows for its siblings, and what it reports about how much log has not yet reached them is not its to report.

Connected to a secondary, the page says so in the notice line, names the primary, and marks the queues unknown rather than drawing them at zero:

SQL02 is a secondary, so the send and redo queues are not readable from here – they are the primary’s view of the world. Connect to SQL01 to see how far behind each copy is. Roles, states and failover readiness below are correct as shown.

It does not open a second connection to the primary on its own. Connecting somewhere the user did not ask for is not something a monitoring tool should do quietly.

The Readiness view still works from a secondary. Roles, states and the readiness checks are correct wherever you are connected.

---

The two views

The toolbar switches between them, and they are different grains answering different questions.

View	Grain	Question
Lag	One row per database per secondary	How far behind is each copy, and what would a failover cost?
Readiness	One row per replica	Would a failover succeed right now?

A group with twenty databases and two secondaries has forty independent answers in the lag view, and any one of them can be the reason a failover is messy. That is why the lag view is not one row per replica.

---

Reading the lag view

One lane per database per secondary, under the heading BEHIND BY. The scale is logarithmic and anchored at current, which is what lets a copy that is two seconds behind and one that is forty minutes behind appear on the same chart without either becoming invisible.

Each lane carries both numbers: what would be lost, and how long redo would take.

The bands

Band	Meaning
Current	Caught up. Nothing would be lost.
Behind	Behind, but inside the thresholds.
Past threshold	Past the data loss or redo threshold this report judges by.
Broken	Data movement is not happening. Not a slow copy, a stopped one.
Seeding	Automatic seeding is still in progress. Behind on purpose, and temporarily.
Unknown	Not measurable from here, usually because you are on a secondary.

Seeding is its own band rather than a breach. A replica that is being seeded is supposed to be behind, and colouring it red would train you to ignore red.

Two pieces of arithmetic worth knowing

A synchronous replica that is SYNCHRONIZED has no data loss, by definition. The report says zero rather than deriving a number from two last_commit_time stamps, because that subtraction produces a fraction of a second of clock noise, and a page that reports four hundred milliseconds of exposure on a synchronous replica is teaching its reader to stop believing the column.

A redo queue that is not moving has no recovery estimate, and the absence is the finding. Dividing work by a rate of zero gives infinity, which sorts beautifully and means nothing. A stalled redo thread is named as stalled and sorted to the top with the worst rows rather than to the bottom with the unreadable ones:

2 copies have work waiting in the redo queue and a redo rate of zero. There is no recovery estimate for those rows because redo is not running, which is the finding rather than a gap in it.

The lag grid

Column	What it is
Availability Group	The group name.
Database	The database, named from the cluster state view rather than from DB_NAME().
Replica	The replica holding this copy.
Role	Primary or secondary.
Mode	Sync or Async.
State	The synchronization state of this database on this replica.
Send Queue	Log hardened on the primary and not yet sent, in KB.
Redo Queue	Log received by the secondary and not yet redone, in KB.
Would Lose	How much committed work a failover to this copy would lose.
Redo Time	How long this copy would spend catching up before it could serve.
Status	The band, in words.

Database names come from sys.dm_hadr_database_replica_cluster_states rather than DB_NAME(). The rows describe databases on other replicas, and DB_NAME resolves ids in the local catalog, so on any group whose replicas were built in a different order it returns a confidently wrong name.

---

Reading the readiness view

One row per replica, asking the pre-patching question rather than the incident question: would a failover succeed right now?

It is drawn as a matrix, one column per check, because a column of failures under one check reads as one cause rather than as four coincidences.

The checks

Check	What it asks
Connected	Is the replica connected to the primary.
Operational	Is the replica online.
Data movement	Is data movement running rather than suspended.
Databases synchronized	Is every database on this replica synchronized.
Failover ready	Is every database marked failover ready.
Failover mode	Automatic or manual.
Cluster quorum	Does the cluster still hold quorum.
Required synchronized secondaries	Are enough synchronous secondaries caught up for the primary to still be accepting commits.
Session timeout	Is the session timeout sane.
Listener	Does the group have a listener.
Read-only routing	Is read-only routing configured.
Automatic page repair	Have pages been repaired automatically from another replica.

Each check answers Ready, Check, Not ready or Unknown, and the legend carries a fifth state, Not answerable here, for the questions a secondary cannot answer.

Required synchronized secondaries is the one people are surprised by. If a group requires more synchronous secondaries than are currently caught up, the primary stops accepting commits. That is a configuration decision made once and forgotten, and it turns into an outage without anything failing over.

The readiness grid

Column	What it is
Availability Group	The group name.
Replica	The replica, with (this instance) on the local one.
Role	Primary or secondary.
Mode	Availability mode and failover mode.
Sync Health	The replica’s synchronization health.
Databases	How many databases are on this replica.
Checks Passed	How many of the checks came back Ready.
Verdict	Ready, Check, Not ready or Unknown.
Needs Attention	The checks that did not pass, named.

---

Thresholds are this report’s opinion

Log shipping stores a per-database threshold the DBA chose, so that page can judge each leg against its own promise. An Availability Group stores no equivalent anywhere. There is no setting in SQL Server that says how far behind a secondary is allowed to be.

So the report uses its own defaults, and says so on any page that is judged by them:

An Availability Group stores no lag threshold of its own, so these rows are judged against this report’s defaults – 1 minute of data loss and 2 minutes of redo. Neither is a setting SQL Server knows about.

Threshold	Default	Setting
Data loss	60 seconds	AgRpoThresholdSeconds
Redo time	120 seconds	AgRtoThresholdSeconds

Both live in the settings file rather than being hard-coded, so a shop whose recovery objective is thirty seconds is not told it is fine at fifty. The notice line disappears once you override them, because at that point the numbers are yours rather than the report’s.

---

The toolbar

Button	What it does
Lag	One row per database per secondary.
Readiness	One row per replica.
All	Every row. Lag view only.
Broken	Only the copies where data movement is not happening.
Past threshold	Only the copies past a threshold.
Seeding	Only the copies still being seeded.
Unknown	Only the copies that are not measurable from here.
Refresh	Re-read now.

A filter button with nothing behind it turns itself off rather than offering an empty page.

The band filters only appear in the lag view. The readiness view is a different grain with a different vocabulary, and offering Broken over a list of replicas would mean something else on each page.

---

Interactions

Gesture	Result
Click a lane	Selects that row in the grid
Double-click a lane or grid row	Copies the query that reproduces that row
Right-click a lane	The row menu, plus Copy Chart to Clipboard
Right-click empty chart space	Copy Chart to Clipboard only

The row menu

Item	What it gives you
Copy Availability Group Lag Query to Clipboard	The query behind the lag numbers for this row.
Copy Failover Readiness Query to Clipboard	The query behind the readiness checks.
Copy Availability Group Configuration Query to Clipboard	Groups, replicas and their settings.
Copy Resume Data Movement Statement to Clipboard	The ALTER DATABASE ... SET HADR RESUME statement, as text.
Copy Database Name to Clipboard	Just the database name.
Copy Replica Name to Clipboard	Just the replica name.

Double-clicking takes the query rather than performing an action. Resuming data movement or failing a group over changes the state of a production replica, and those belong in a window the DBA is looking at rather than behind a double-click on a monitoring page. Even the resume statement is handed over as text.

---

How to read the report

1. Check the notice line first. If you are on a secondary, the queues are blank for a reason, and the line names the primary to connect to.
2. Read the headline. It names the worst exposure on the instance, which is the sentence you came for.
3. Look for Broken before Past threshold. A stopped copy is a different problem from a slow one, and the headline reports broken ahead of breached for that reason.
4. Check for stalled redo. A redo queue with work in it and a rate of zero has no estimate, and that is worse than a large estimate.
5. Switch to Readiness before any planned failover. The lag view answers what a failover would cost. The readiness view answers whether it would work.
6. Read down the readiness columns, not across. A whole column failing is one cause.
7. Copy the query for anything you want to keep watching, and run it where you can watch it.

Common patterns

Everything Current, all replicas Ready. The answer you want. Note the thresholds being used, and set your own if the defaults are not your recovery objective.

One database Past threshold, the rest Current. Not the network and not the replica. Something specific to that database, usually a large index rebuild or a bulk load generating log faster than it can be sent.

Every database on one replica behind. That replica or the path to it, not the databases. Check the network and the replica’s disk.

Data movement suspended on one copy. Someone suspended it, or SQL Server did after an error. It will never catch up on its own, and the send queue grows until the primary’s log cannot truncate.

Automatic page repair count above zero. Corruption was found and papered over from another replica. The notice line says it plainly: automatic page repair hides corruption rather than fixing its cause. Run DBCC CHECKDB on both ends.

Required synchronized secondaries failing. The primary may already be refusing commits. This is an outage with nothing obviously down.

---

Where the data comes from

One batch per load, returning six result sets: the header and the cluster, then cluster members, groups, replicas, the database grain that carries the lag, and the listeners.

• sys.availability_groups, sys.availability_replicas, sys.availability_group_listeners
• sys.dm_hadr_availability_replica_states and sys.dm_hadr_database_replica_states
• sys.dm_hadr_database_replica_cluster_states for database names
• sys.dm_hadr_cluster and sys.dm_hadr_cluster_members for quorum
• sys.dm_hadr_auto_page_repair for the repair count

Everything lands in a temp table before anything is selected out of it, and each block sits in its own TRY with its own flag. The final six selects run whatever happened, so the reader always gets six result sets in the same order: on SQL Server 2008 where none of these views exist, on an instance where Always On was never enabled, and on a login without VIEW SERVER STATE. Every one of those is a legitimate answer rather than an error, and only the last is worth an error message.

Why the column list is built at runtime

The availability DMVs have grown columns over a decade, and a bind error is raised when the batch compiles, before any TRY block runs. So every optional read goes through sp_executesql against a column list built from what the catalog says is actually present:

Column	Arrived in
secondary_lag_seconds	2016 SP2
seeding_mode_desc	2016
cluster_type_desc, required_synchronized_secondaries_to_commit	2017
is_contained	2022

secondary_lag_seconds is why this is checked against the catalog rather than a version number: it arrived in a service pack rather than a release, so a major version comparison would either lose it on 2016 SP2 or bind-error on 2016 RTM.

Where it is missing, data loss is derived from commit times instead, and the subtitle says so:

data loss from commit times – this build has no secondary_lag_seconds

Nothing is stored. There is no history behind this page.

---

Settings

Setting	Default	Values
AgReplicaView	lag	lag or readiness
AgRpoThresholdSeconds	60	Any positive number of seconds
AgRtoThresholdSeconds	120	Any positive number of seconds

Per-user rather than per-instance. The band filter and the grid sort are not remembered.

---

Messages you may see

On SQL Server 2011 or older:

This version of SQL Server does not have Availability Groups. Always On Availability Groups were introduced in SQL Server 2012. SQL01 is running an older build.

When the feature was never switched on:

Always On Availability Groups are not enabled on this instance. The feature is switched on per instance in SQL Server Configuration Manager, and the service has to be restarted before SQL01 can host a group.

When Always On is on but nothing has been built:

No Availability Groups are configured on this instance. Always On is enabled on SQL01, but no group has been created on it.

When groups exist but no secondary has a database joined:

No secondary copies to measure on this instance. … but no secondary replica has a database joined. There is nothing to be behind. The Readiness view still works.

When part of the data could not be read, the page shows what it got and says what it did not, including what SQL Server said:

Part of the Always On data could not be read, so this page is showing part of the picture. SQL Server said: …

When the query times out:

The Availability Group lookup did not finish in time. It reads six DMVs and normally answers instantly; try Refresh.

---

Related reports

Report	Why you would go there
Log Shipping	The other way a secondary copy falls behind, with thresholds SQL Server does store.
Backup Status	Where the backup preference of a group actually lands.
Last DBCC CheckDB Known Good	After an automatic page repair, on both ends.
Waits	HADR_* waits, when synchronous commit is the thing slowing the primary down.
Disk Space	A send queue that cannot drain stops the primary’s log truncating.

---

Frequently asked questions

Why are the queue columns blank? Because you are connected to a secondary, and the send and redo queues are the primary’s view of the world. The notice line names the primary to connect to.

Why does a synchronous replica show zero data loss rather than a small number? Because a synchronous replica that is SYNCHRONIZED has none, by definition. Any small number would be clock noise between two commit timestamps.

Why does a row have no redo time? Because its redo queue has work in it and a redo rate of zero. Redo is not running, so there is nothing to estimate. That is the finding, not a gap in it.

Where do the thresholds come from? This report. SQL Server does not store a lag threshold for an Availability Group anywhere. Override them in the settings file and the notice line goes away.

Can I resume data movement or fail over from here? No. The resume statement is offered as text to take somewhere you can watch what it does. Nothing on this page changes the state of a replica.

Why is a seeding replica not marked as a breach? Because it is supposed to be behind. It gets its own band so that red keeps meaning something.

Does this replace the Always On dashboard in SSMS? No. It answers a different question. The dashboard says whether things are healthy; this says how far behind each copy is and what a failover would cost.

---