AG Readable Secondaries for Reporting Offload

An Availability Group secondary sitting there in Synchronous Commit mode, fully caught up, doing nothing but waiting to become primary someday, is capacity going to waste. Readable secondaries let you point reporting and ad-hoc query load at that hardware instead, without touching the primary at all. It's one of the more underused parts of AG, mostly because "wait, can I actually write to it?" makes people nervous enough to never try.

What "Readable" Actually Means

A secondary replica configured with ALLOW_CONNECTIONS = READ_ONLY (or ALL, with routing) accepts client connections and runs SELECT queries against a live, continuously-updating copy of every database in the AG. It is not a static snapshot, changes from the primary apply in near real time via the same log-shipping mechanism that keeps the secondary ready for failover.

Connection property	Behavior on a readable secondary
Writes (`INSERT`/`UPDATE`/`DELETE`)	Rejected outright, the database is read-only from the client's perspective
Isolation level	Always effectively snapshot isolation; row versioning is enabled automatically, readers never block on the redo thread
tempdb usage	Row versions for the secondary's snapshot isolation live in the secondary's own tempdb, size it accordingly
Statistics	Read-only; the optimizer can create temporary statistics in tempdb on the secondary itself if it needs ones that don't exist

Routing Reporting Traffic There

Read-only routing is configured per availability replica with READ_ONLY_ROUTING_LIST. Once set, any client connecting through the listener with ApplicationIntent=ReadOnly in its connection string gets automatically redirected to a readable secondary, no application code change needed beyond that one connection string property.

ALTER AVAILABILITY GROUP [MyAG]
MODIFY REPLICA ON N'PrimaryNode' WITH
(
    SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY),
    PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = ('SecondaryNode', 'PrimaryNode'))
);

ALTER AVAILABILITY GROUP [MyAG]
MODIFY REPLICA ON N'SecondaryNode' WITH
(
    SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY),
    PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = ('SecondaryNode', 'PrimaryNode'))
);

What to Expect

Data lag depends on availability mode. Synchronous secondaries are essentially current; asynchronous secondaries can lag by however long log redo is behind, seconds under normal load, more under heavy write volume or redo thread contention. Reporting that needs to-the-second accuracy shouldn't be routed to an async secondary.
Blocking works differently, not absently. Readers don't block writers and don't get blocked by them (row versioning handles that), but a long-running report can still delay redo application if it holds locks the redo thread needs, in extreme cases this shows up as growing replication lag caused by the reporting query itself.
Index and statistics needs don't disappear. A query that's slow on the primary for lack of an index is exactly as slow on the secondary; offloading changes where the load lands, not whether the query itself is efficient.

This is a different case from the SSRS catalog. SQL Server Reporting Services on AlwaysOn Availability Groups explicitly warns against pointing report server traffic at a readable secondary, because the ReportServer/ReportServerTempDB catalog is written to constantly (execution history, session state, cache). That guidance is about SSRS's own metadata database. Routing a reporting workload's queries against business data to a secondary, the topic here, is a completely different and generally safe use case, don't confuse the two.

Common Gotchas

Someone points a write at the secondary. It fails cleanly with an error rather than silently succeeding, but if the application doesn't handle that error gracefully, it can look like an outage rather than what it actually is: a routing mistake.
Forgetting a symmetric routing list. READ_ONLY_ROUTING_LIST needs to be defined on every replica that could become primary, if only the current primary has it configured, routing breaks silently after a failover.
tempdb undersized on the secondary. Since row versioning for snapshot isolation happens in the secondary's tempdb, a reporting workload that was fine on the primary can create unexpected tempdb pressure on a secondary sized for a passive standby role rather than active query load.
Treating it as a scale-out cluster. A readable secondary is still one replica; it doesn't automatically load-balance across multiple secondaries unless you explicitly list more than one in the routing list and accept round-robin behavior, it's not a substitute for horizontal scale-out.

The Bottom Line

If you already have a synchronous secondary sitting idle for failover readiness, routing reporting and ad-hoc read traffic to it is close to free capacity, one connection string property and a routing list, no new infrastructure. The trade-offs are the same ones any read-replica pattern carries: replication lag, tempdb sizing, and making sure writes never accidentally land there. None of that requires new hardware to solve, just the routing configuration to actually use what's already running.

← Back to Blog