Readable Secondaries, Read-Only Routing, Backups, Lag, Seeding, and Application Intent

Learning outcomes

Once ServiceHub has an advanced AG, teams often try to “use the secondary for reporting and backups.” That can be useful, but it creates a new correctness contract. A readable secondary exposes data only after redo. A backup preference is a policy hint to jobs, not enforcement. Read-only routing happens only for qualifying client connections through the listener. Automatic seeding consumes the same network and storage resources the workload depends on.

1. A readable secondary is not a synchronous cache

Secondary reads see the state that redo has applied. Even a synchronous-commit secondary can have a redo queue because commit acknowledgment is tied to hardening, not read visibility after redo. Long-running queries can also interact with row versioning on readable secondaries. Report consumers must decide whether seconds/minutes of lag are acceptable and whether “read your own write” semantics are required.

SELECT DB_NAME(database_id) AS database_name,       synchronization_state_desc,       synchronization_health_desc,       log_send_queue_size AS log_send_queue_kb,       redo_queue_size AS redo_queue_kb,       last_hardened_time,last_redone_time,       DATEDIFF(second,last_redone_time,SYSUTCDATETIME()) AS seconds_since_last_redoFROM sys.dm_hadr_database_replica_statesWHERE is_local=1;GO

A queue size is a point-in-time observation, not an SLA by itself. Combine it with log generation rate, redo rate, latency, workload and timestamps. A large remote queue can be network-latency or throughput pressure; a redo queue can be secondary CPU/I/O/redo pressure.

2. Read-only routing is a four-part contract

For automatic read-only routing, the AG needs a listener; the secondary must allow read connections; replicas need read-only routing URLs/lists; and the client must connect to the listener with a database in the AG plus ApplicationIntent=ReadOnly. Without those pieces, SQL Server cannot infer that an ordinary client connection should be redirected.

# Microsoft.Data.SqlClient style examples# Primary/read-write intent:Server=tcp:ServiceHubListener;Database=ServiceHubLab;Encrypt=True;TrustServerCertificate=False;ApplicationIntent=ReadWrite;# Qualifying read-only routing request:Server=tcp:ServiceHubListener;Database=ServiceHubLab;Encrypt=True;TrustServerCertificate=False;ApplicationIntent=ReadOnly;MultiSubnetFailover=True;

SELECT ag.name AS ag_name,agl.dns_name,agl.port,agl.is_conformantFROM sys.availability_groups AS agJOIN sys.availability_group_listeners AS agl ON agl.group_id=ag.group_id;GOSELECT ar.replica_server_name,ar.secondary_role_allow_connections_desc,       ar.read_only_routing_url,ar.backup_priorityFROM sys.availability_replicas AS ar;GO

3. Backup preference is advisory, not enforcement

AUTOMATED_BACKUP_PREFERENCE records where you prefer backups to run. SQL Server does not stop a backup command that violates that preference. Your Agent or external scheduler must query the preference and decide whether to proceed. Microsoft provides sys.fn_hadr_backup_is_preferred_replica() for this reason.

DECLARE @db sysname=N'ServiceHubLab';SELECT @db AS database_name,       sys.fn_hadr_backup_is_preferred_replica(@db) AS is_preferred_here;IF sys.fn_hadr_backup_is_preferred_replica(@db) <> 1BEGIN    PRINT 'Not the preferred backup replica; exit this job successfully.';    RETURN;END;PRINT 'Preferred here; continue with the approved backup procedure.';GO

In a real fleet, deploy the same job logic to candidate replicas and make media paths/credentials available where needed. Remember the Standard Basic AG limitation: it does not allow backups on its secondary, so this offload pattern is for supported advanced-AG topologies.

4. Secondary workload isolation is an HA concern

Read scale can compete directly with recovery readiness. Reporting queries consume CPU, memory grants, buffer-pool pages and storage bandwidth on the secondary. Backups consume I/O and CPU, especially with compression/encryption. Redo needs CPU and storage to apply incoming log. If reporting saturates the replica and redo falls behind, the very secondary intended as a failover target can become less ready for failover. Resource governance and workload scheduling therefore belong in the HA design, not only the performance chapter.

Use Query Store and normal performance evidence on readable replicas where supported, but interpret it with role awareness. A plan that is fine on the primary may encounter different cache warmth, hardware, statistics timing or concurrent workload on a secondary. Read-only routing lists can distribute connections, yet SQL Server does not provide a general-purpose load balancer with application session affinity guarantees. Test the actual reporting workload and connection pattern.

SELECT @@SERVERNAME AS connected_instance,       DB_NAME() AS database_name,       DATABASEPROPERTYEX(DB_NAME(),'Updateability') AS updateability,       sys.fn_hadr_is_primary_replica(DB_NAME()) AS is_primary_replica;GOSELECT wait_type,waiting_tasks_count,wait_time_msFROM sys.dm_os_wait_statsWHERE wait_type IN(N'REDO_THREAD_PENDING_WORK',N'DB_MIRROR_SEND',N'HADR_SYNC_COMMIT',N'WRITELOG', N'PAGEIOLATCH_SH',N'RESOURCE_SEMAPHORE')ORDER BY wait_time_ms DESC;GO

Some diagnostics and waits are cumulative since reset/startup, so capture deltas over a known interval. A high cumulative wait after months of uptime is not proof of a current incident. Likewise, do not solve secondary pressure by disabling protection settings or moving all backups to the primary without measuring the new failure and capacity tradeoffs.

5. Automatic seeding is an initialization workload

Automatic seeding avoids manually restoring a full/log chain to initialize a secondary, but SQL Server must still transfer the database and create it on the target. Large databases can saturate network links, consume I/O and extend backup/redo queues. Check target free space, throughput, seeding state and error logs before assuming a slow join is “stuck.”

SELECT local_database_name,role_desc,internal_state_desc,       transfer_rate_bytes_per_second,transferred_size_bytes,       database_size_bytes,start_time,completion_time,failure_state_descFROM sys.dm_hadr_automatic_seedingORDER BY start_time DESC;GO

6. Production judgment

Readable secondaries are capacity and consistency tools, not zero-lag caches. Define maximum tolerated reporting staleness and verify it from redo evidence. Configure clients with current drivers, TLS validation, listener names and appropriate connection timeout/retry behavior. In multisubnet deployments, use driver guidance for MultiSubnetFailover. Protect secondary resources so reporting, backups and seeding do not starve redo.

The final lesson turns these mechanics into drills: quorum loss, service/node/network failure, planned failover, automatic failover and forced failover—with client reconnect evidence and explicit data-loss authorization.

Authoritative references

• Connect to an Availability Group listener — listener, routing and ApplicationIntent
• Configure read-only routing — routing prerequisites and metadata
• sys.fn_hadr_backup_is_preferred_replica — backup job gating
• Initialize an AG using automatic seeding — automatic seeding workflow