Part of the DBA-Tools Project.
Would a Failover Actually Succeed Right Now, and Is Anyone Using the Secondary?
AG Replica Role and Synchronization State tells you the current health of every replica. This post answers two sharper, more specific questions on top of that: if the primary failed right now, would the failover actually succeed, and what would it cost in lost data and downtime? And separately, is the secondary replica actually configured for read access, and is anyone routed to it?
AG Failover Readiness quantifies RPO (data loss exposure) and RTO (time to recover) per database, per replica, from real queue and redo-rate numbers, not a guess. Readable Secondary Usage checks whether a secondary allows connections and whether read-only routing is actually configured, since a synchronized secondary that nobody can reach is not delivering the value it’s there for.
Why Failover Readiness and Secondary Usage Matter
- “Healthy” and “ready to fail over with zero data loss” are different claims, a replica can show
SYNCHRONIZINGand still have a redo queue that would take minutes to drain - RPO (how much data you’d lose) and RTO (how long recovery takes) are the two numbers that actually matter in an incident, not an abstract health percentage
- A secondary configured for reads but with no read-only routing URL set silently sends nobody there, application traffic stays on the primary even though the secondary is available
- Async replicas are explicitly a data-loss trade for geographic distance or reduced primary overhead, not a lesser version of sync, but that trade needs to be visible, not assumed
When to Run These Scripts
- Before signing off on an AG’s DR readiness, quantified numbers beat “it looks green”
- After finding a replica in
SYNCHRONIZINGrather thanSYNCHRONIZED, to estimate whether a failover right now would actually complete quickly - When investigating why read-heavy application traffic still hits the primary, despite a secondary existing
- Routine health checks, alongside AG Replica Role and Synchronization State
The Scripts
Get-AgFailoverReadiness — Quantified RPO and RTO, Per Database
/*
Script Name : Get-AgFailoverReadiness
Category : high-availability
Purpose : Per-AG, per-database failover readiness with quantified RPO and RTO estimates.
Answers "would a failover succeed RIGHT NOW and what would it cost?"
RTO = estimated seconds to drain redo queue at current redo rate.
RPO = log send queue size (data that would be lost if primary fails now).
Author : Peter Whyte (https://sqldba.blog/dba-scripts-get-ag-failover-readiness-and-readable-secondary-usage/)
Requires : VIEW SERVER STATE, VIEW ANY DATABASE
*/
-- Blog: https://sqldba.blog/dba-scripts-get-ag-failover-readiness-and-readable-secondary-usage/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
IF NOT EXISTS (SELECT 1 FROM sys.availability_groups)
BEGIN
SELECT 'This instance is not a member of an Availability Group (or AG feature is disabled).' AS info;
END
ELSE
BEGIN
WITH ag_readiness AS (
SELECT
ag.name AS ag_name,
ar.replica_server_name,
agl.dns_name AS listener_name,
agl.port AS listener_port,
DB_NAME(drs.database_id) AS database_name,
drs.is_local,
ars.role_desc,
ars.operational_state_desc,
ars.connected_state_desc,
ars.synchronization_health_desc AS replica_health,
drs.synchronization_state_desc AS db_sync_state,
drs.database_state_desc,
-- RPO exposure: data that could be lost if primary fails right now
drs.log_send_queue_size AS log_send_queue_kb,
CAST(drs.log_send_queue_size / 1024.0 AS DECIMAL(10,2)) AS log_send_queue_mb,
-- RTO estimate: seconds to drain the redo queue at current redo rate
drs.redo_queue_size AS redo_queue_kb,
CAST(drs.redo_queue_size / 1024.0 AS DECIMAL(10,2)) AS redo_queue_mb,
drs.redo_rate AS redo_rate_kb_per_sec,
CASE
WHEN drs.redo_rate > 0 AND drs.redo_queue_size > 0
THEN CAST(drs.redo_queue_size / drs.redo_rate AS INT)
ELSE NULL
END AS estimated_rto_seconds,
drs.secondary_lag_seconds,
drs.last_hardened_time,
drs.last_received_time,
-- Readiness: SYNCHRONIZED = ready for synchronous failover
-- (is_failover_ready removed in SQL 2022+; derived from sync state)
-- COLLATE DATABASE_DEFAULT on catalog columns avoids collation conflicts with literals
CASE
WHEN ars.role_desc COLLATE DATABASE_DEFAULT = 'PRIMARY'
THEN 'PRIMARY — this is the source'
WHEN drs.database_state_desc COLLATE DATABASE_DEFAULT <> 'ONLINE'
THEN 'CRITICAL — database is ' + (drs.database_state_desc COLLATE DATABASE_DEFAULT) + ' on this replica'
WHEN drs.synchronization_state_desc COLLATE DATABASE_DEFAULT = 'SYNCHRONIZED'
AND ar.availability_mode_desc COLLATE DATABASE_DEFAULT = 'SYNCHRONOUS_COMMIT'
THEN 'OK — SYNCHRONIZED; ready for automatic/manual failover (zero data loss)'
WHEN drs.synchronization_state_desc COLLATE DATABASE_DEFAULT = 'SYNCHRONIZING'
AND drs.redo_rate > 0
AND drs.redo_queue_size / drs.redo_rate < 60
THEN 'OK — SYNCHRONIZING; est. RTO ' +
CAST(drs.redo_queue_size / NULLIF(drs.redo_rate, 0) AS VARCHAR) + 's to drain redo queue'
WHEN drs.redo_queue_size > 1048576
THEN 'WARN — redo queue > 1 GB; RTO will be minutes at best'
WHEN ar.availability_mode_desc COLLATE DATABASE_DEFAULT = 'ASYNCHRONOUS_COMMIT'
THEN 'INFO — async replica; manual forced failover only (data loss likely: ' +
CAST(CAST(drs.log_send_queue_size / 1024.0 AS INT) AS VARCHAR) + ' MB RPO exposure)'
ELSE 'INFO — ' + (drs.synchronization_state_desc COLLATE DATABASE_DEFAULT)
END AS readiness_status,
-- Numeric sort key avoids collation conflict in ORDER BY string comparison
CASE
WHEN ars.role_desc COLLATE DATABASE_DEFAULT = 'PRIMARY' THEN 5
WHEN drs.database_state_desc COLLATE DATABASE_DEFAULT <> 'ONLINE' THEN 1
WHEN drs.redo_queue_size > 1048576 THEN 2
WHEN drs.synchronization_state_desc COLLATE DATABASE_DEFAULT = 'SYNCHRONIZED' THEN 4
ELSE 3
END AS sort_priority,
ar.availability_mode_desc COLLATE DATABASE_DEFAULT AS commit_mode,
ar.failover_mode_desc COLLATE DATABASE_DEFAULT AS failover_mode
FROM sys.availability_groups AS ag
JOIN sys.availability_replicas AS ar ON ar.group_id = ag.group_id
JOIN sys.dm_hadr_availability_replica_states AS ars ON ars.replica_id = ar.replica_id
JOIN sys.dm_hadr_database_replica_states AS drs ON drs.replica_id = ar.replica_id
LEFT JOIN sys.availability_group_listeners AS agl ON agl.group_id = ag.group_id
)
SELECT * FROM ag_readiness
ORDER BY sort_priority, ag_name, database_name, replica_server_name;
END;
readiness_status is deliberately worded as a real answer (“ready for automatic/manual failover, zero data loss” or “manual forced failover only, data loss likely: X MB”), not just a raw sync-state label, so the output can go straight into an incident channel without translation.
Get-ReadableSecondaryUsage — Is the Secondary Actually Reachable for Reads?
/*
Script Name : Get-ReadableSecondaryUsage
Category : high-availability
Purpose : Shows Availability Group replica connection modes and read-only routing
configuration. Identifies which replicas allow readable secondary access
and whether routing is configured. Returns a status row on standalone
instances (no AG).
Author : Peter Whyte (https://sqldba.blog/dba-scripts-get-ag-failover-readiness-and-readable-secondary-usage/)
Requires : VIEW SERVER STATE
*/
-- Blog: https://sqldba.blog/dba-scripts-get-ag-failover-readiness-and-readable-secondary-usage/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
IF NOT EXISTS (SELECT 1 FROM sys.availability_groups)
BEGIN
SELECT
GETDATE() AS collection_time,
@@SERVERNAME AS server_name,
'NO_AG' AS ag_name,
NULL AS replica_server_name,
NULL AS role_desc,
NULL AS secondary_role_allow_connections_desc,
NULL AS read_only_routing_url,
NULL AS connected_state_desc,
NULL AS synchronization_health_desc,
NULL AS redo_queue_kb,
NULL AS routing_configured;
RETURN;
END
SELECT
GETDATE() AS collection_time,
@@SERVERNAME AS server_name,
ag.name AS ag_name,
ar.replica_server_name,
ars.role_desc,
ar.secondary_role_allow_connections_desc,
ar.read_only_routing_url,
ars.connected_state_desc,
ars.synchronization_health_desc,
drs_agg.total_redo_queue_kb,
CASE
WHEN ar.secondary_role_allow_connections_desc IN ('READ_ONLY', 'ALL')
AND ar.read_only_routing_url IS NOT NULL
THEN 'Yes — connections and routing configured'
WHEN ar.secondary_role_allow_connections_desc IN ('READ_ONLY', 'ALL')
AND ar.read_only_routing_url IS NULL
THEN 'Partial — readable but no routing URL set'
ELSE 'No — secondary not configured for reads'
END AS routing_configured
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ar.group_id = ag.group_id
JOIN sys.dm_hadr_availability_replica_states ars ON ars.replica_id = ar.replica_id
OUTER APPLY (
SELECT SUM(drs.redo_queue_size) AS total_redo_queue_kb
FROM sys.dm_hadr_database_replica_states drs
WHERE drs.replica_id = ar.replica_id
) drs_agg
ORDER BY ag.name, ars.role_desc DESC, ar.replica_server_name;
routing_configured distinguishes “configured for reads but nobody’s routed there” from “actually reachable,” a gap that’s easy to leave unnoticed since the AG itself still reports healthy either way.
How To Run From The Repo
Clone DBA Tools, initialize and run either script:
# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools
# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1
# Quantified RPO/RTO failover readiness, per database, per replica:
.\run.ps1 Get-AgFailoverReadiness
# Readable secondary connection and routing configuration:
.\run.ps1 Get-ReadableSecondaryUsage
# To run against a remote sql server:
.\run.ps1 Get-AgFailoverReadiness -ServerInstance SQLSERVER01
These scripts live in the repo at:
sql/high-availability/always-on/Get-AgFailoverReadiness.sqlsql/high-availability/always-on/Get-ReadableSecondaryUsage.sql
Example Output
Real output from this lab instance, not staged:
info
This instance is not a member of an Availability Group (or AG feature is disabled).
That’s the honest, correct result: this lab box has never had an Availability Group configured. Get-ReadableSecondaryUsage returns the equivalent standalone-instance status row, ag_name = 'NO_AG', every other column NULL. On an instance with a real AG, Get-AgFailoverReadiness returns a row like this:
| ag_name | database_name | role_desc | readiness_status | log_send_queue_mb | estimated_rto_seconds |
|---|---|---|---|---|---|
| SalesAG | SalesDB | SECONDARY | OK — SYNCHRONIZED; ready for automatic/manual failover (zero data loss) | 0.00 | 0 |
Understanding the Results
- readiness_status — read this column directly, it’s already the answer (“ready,” “RTO Xs,” “data loss likely: X MB”), not a raw state code needing translation
- estimated_rto_seconds — only populated when actively
SYNCHRONIZINGwith a nonzero redo rate;NULLon aPRIMARYrow or a fullySYNCHRONIZEDsecondary (no redo backlog to drain) - log_send_queue_mb — the RPO exposure number, how much committed data on the primary hasn’t yet reached this replica
- routing_configured = “Partial” — the secondary allows read connections but has no
read_only_routing_url, application traffic won’t be routed there automatically even though direct connections would work
Best Practices
- Don’t rely on
synchronization_health_descalone to judge failover readiness, a “healthy”SYNCHRONIZINGsecondary can still carry a redo queue that takes minutes to drain - Set an explicit routing URL on any secondary meant for read offload, “configured for reads” and “actually receiving read traffic” are different states without one
- Treat an async replica’s RPO exposure as a real, standing number to monitor, not a one-time acceptance during setup
- Re-run failover readiness after any maintenance window that could have left a replica catching up, don’t assume it’s caught up just because the window is over
Related Scripts
You may also find these scripts useful:
- High Availability (hub)
- AG Replica Role and Synchronization State
- Always On Availability Group Latency
Frequently Asked Questions
What’s the difference between this and AG Replica Role and Synchronization State?
That script reports current health and sync state per replica. This post goes further: it quantifies what a failover would actually cost right now (RPO in MB, RTO in estimated seconds) and separately checks whether a secondary is actually reachable for read traffic, not just healthy.
Why does an async replica always show a nonzero RPO exposure?
That’s the nature of asynchronous commit, the primary doesn’t wait for the secondary to harden the log before committing, so there’s always some window of data that exists only on the primary. The number to watch is whether that window is growing, not whether it exists at all.
Summary
A replica reporting “healthy” isn’t the same as a failover being ready to succeed with an acceptable data-loss and downtime cost, and a secondary being technically readable isn’t the same as read traffic actually reaching it. These two scripts turn both into real, specific numbers instead of a green checkmark.
Run Failover Readiness before trusting an AG’s DR story, and check Readable Secondary Usage any time read-heavy traffic seems to be hitting the primary despite a secondary existing.
Leave a Reply