Would a Failover Succeed Right Now, and Can Reads Reach the Secondary?
A failover is coming, planned or not, and you need to know what it costs before anyone presses the button. Get-AgFailoverReadiness answers it per database, per replica: can it fail over without data loss, how much log has not reached the secondary yet, and roughly how long redo would take. Get-ReadableSecondaryUsage shows which secondaries accept reads and whether read-only routing can send traffic there. For connected or disconnected, start with AG Replica Role and Synchronization State.
Why Failover Readiness and Secondary Usage Matter
- “Healthy” and “ready to fail over with no data loss” are different claims. An async replica reads HEALTHY while it is only SYNCHRONIZING, and a SYNCHRONIZED replica can still carry a redo queue that delays the database coming online after a failover
- 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
- Read-intent connections through the listener are routed to a secondary only when it has a read-only routing URL and sits in the current primary’s routing list. Allowing reads on the replica is not enough on its own
- 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
- Get-AgFailoverReadiness, before signing off an AG’s DR readiness or a planned failover: numbers per database beat “it looks green”
- Get-AgFailoverReadiness, after finding a replica in
SYNCHRONIZINGrather thanSYNCHRONIZED, to see what a failover right now would cost - Get-ReadableSecondaryUsage, when read-heavy application traffic still hits the primary although a readable secondary exists
- Routine health checks, alongside AG Replica Role and Synchronization State
The Scripts
Get-AgFailoverReadiness: Failover Readiness Per Database
- Tested on: SQL Server 2025 (RTM CU8) 17.0.4075.5, Windows lab instance, standalone with no availability group
- Last verified: 2026-10-06 (saved output from real runs of both scripts, the guard row of each, Get-AgFailoverReadiness-20261006-094030.csv and Get-ReadableSecondaryUsage-20261006-094036.csv). Both AG queries compile on SQL Server 2025; their rows on a live AG follow MS Docs and were not reproduced here
- Permissions: VIEW SERVER STATE, or VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later
- Safety: read-only, impact low
/*
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
HealthCheck : Yes
*/
-- 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;Run it on the primary. There, sys.dm_hadr_availability_replica_states returns every replica, so you get one row per database per replica. On a secondary it returns only local information, and the other replicas drop out of the result. Where no availability group exists, the guard at the top returns one status row instead of an error.
Get-ReadableSecondaryUsage: Can Reads Reach the Secondary
/*
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
*/
-- 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;Run this one on the primary too, where every replica is visible. routing_configured separates a secondary nobody can be routed to from one that is set up for it, a gap that stays hidden because the AG 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
With no availability group to report, Get-AgFailoverReadiness returns one status row:
Get-ReadableSecondaryUsage returns its own status row in the same place: the collection time, the server name, NO_AG as ag_name, and NULL in every other column.
If you get these rows on a server that should host a replica, check SERVERPROPERTY('IsHadrEnabled'). 0 means the Always On feature is off there. 1 means the feature is on but the login that ran the script can see no availability group.
Understanding the Results
readiness_statuscommit_modefailover_modeOK - SYNCHRONIZED is printed for every synchronous-commit secondary that reads SYNCHRONIZED, and there a manual failover loses no data. Automatic failover needs more: this replica and the primary both set to AUTOMATIC, and a cluster with quorum (MS Docs). Read failover_mode before you count on it.Act when the row reads anything other than OK - SYNCHRONIZED or PRIMARY, including OK - SYNCHRONIZING: that label also lands on an ASYNCHRONOUS_COMMIT replica with a short redo queue. Every async replica, and every synchronous one that is not SYNCHRONIZED, can only take a forced failover, with possible data loss.log_send_queue_mbredo_queue_mbredo_rate_kb_per_secestimated_rto_secondsestimated_rto_seconds is the redo queue divided by redo_rate, the redo part of failover time in MS Docs. Detection and failover overhead come on top, so treat it as a floor. redo_rate is an average since the instance started, over the time redo was running, so the estimate is rough. It is filled whenever both are above 0, including on a SYNCHRONIZED secondary: SYNCHRONIZED means the log is hardened there, not that it is redone.Act when the estimate is longer than your RTO, or the redo queue grows between runs.routing_configuredsecondary_role_allow_connections_descread_only_routing_urlNO, READ_ONLY (read-intent connections only) or ALL (any connection, reads only), from sys.availability_replicas. Yes means the replica allows reads and has a routing URL. Routing also needs this replica in the current primary’s list in sys.availability_read_only_routing_lists, which this script does not read, and a client that connects to the listener with ApplicationIntent=ReadOnly (MS Docs).Act when it reads Partial: only direct connections to that replica can read there, nothing is routed to it through the listener.Best Practices
- Don’t rely on
synchronization_health_descalone to judge failover readiness: an async replica reads HEALTHY while SYNCHRONIZING, and a SYNCHRONIZED one can still carry a redo queue that takes minutes to drain - Give every secondary meant for read offload a routing URL, and put it in the routing list of every replica that can become primary, or “configured for reads” and “actually receiving read traffic” stay different states
- 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
MS Docs: sys.dm_hadr_database_replica_states · sys.dm_hadr_availability_replica_states · sys.availability_groups · sys.availability_replicas · Failover and failover modes · Configure read-only routing
Related Scripts
You may also find these scripts useful:
- High Availability (hub)
- AG Replica Role and Synchronization State
- Always On Availability Group Latency
- DBA Scripts: The Complete Guide, the map across every script on this site
Frequently Asked Questions
What’s the difference between this and AG Replica Role and Synchronization State?
AG Replica Role and Synchronization State reports role, connection and health per replica, and Always On Availability Group Latency reports the queues per database. This post puts a price on a failover right now (log not yet sent, seconds of redo) and checks whether a secondary can take read traffic, not just whether it is healthy.
Is OK - SYNCHRONIZED enough for an automatic failover?
No. It covers the data: a synchronous-commit SYNCHRONIZED secondary can fail over without data loss. Automatic failover also needs the primary and this replica both set to AUTOMATIC, the cluster to have quorum, and the failure to meet the AG’s failover condition level (MS Docs).
For the cluster’s own view, is_failover_ready in sys.dm_hadr_database_replica_cluster_states is 1 when the database is marked as synchronized in the cluster and ready for a failover, and 0 when it is not.
Why does an async replica show 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 is a window of data that exists only on the primary. It can read 0 when the primary is quiet. The number to watch is whether that window is growing, not whether it exists at all.
Can a secondary that drops out stop commits on the primary?
Only when the AG is set up to require it. required_synchronized_secondaries_to_commit in sys.availability_groups is the number of secondary replicas that must be synchronized for a commit to complete. Neither script returns it, so read it there when commits stall during a replica problem.
Does it show who is reading from the secondary?
No. Get-ReadableSecondaryUsage reads configuration: whether reads are allowed and whether routing can send them. To see who is connected, run this on the secondary itself and look for sessions in the availability databases: SELECT DB_NAME(database_id) AS database_name, COUNT(*) AS user_sessions FROM sys.dm_exec_sessions WHERE is_user_process = 1 GROUP BY database_id;
Why does it show only one replica when I run it on a secondary?
Because a secondary only reports itself. MS Docs says to query sys.dm_hadr_availability_replica_states on the primary for every replica, and both scripts join to it, so the other replicas drop out. Find the primary with AG Replica Role and Synchronization State and run both scripts there.
What permission does it need?
VIEW SERVER STATE, or VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later.
With less, the guard still returns its status row, but the AG queries fail. Get-AgFailoverReadiness stops at Msg 300 asking for VIEW SERVER PERFORMANCE STATE. Get-ReadableSecondaryUsage stops at Msg 262, “VIEW DATABASE PERFORMANCE STATE permission denied in database ‘master’.” Granting that database permission, as the message suggests, is not enough: the query then fails with Msg 300.
MS Docs also lists VIEW ANY DEFINITION for sys.availability_groups and sys.availability_replicas. A login that cannot see the group would get the status row on a server that has one. That case needs an AG and is documented, not reproduced here.
Summary
A replica reporting HEALTHY is not the same as a failover that loses no data, and a secondary that allows reads is not the same as one that read traffic reaches. Run Get-AgFailoverReadiness on the primary before trusting an AG’s DR story or pressing failover, and Get-ReadableSecondaryUsage when read-heavy traffic keeps landing on the primary.
Leave a Reply