Offloading Reports to a Readable Secondary

Reports can crowd transactions on the primary. A readable secondary gives those reports a separate place to run. That sounds attractive, but a secondary is a separate workload host with replication lag and licensing considerations. Move only reports whose freshness and behavior fit that path.

What Read Offload Changes

An availability group secondary maintains a separate copy of database pages by receiving and redoing log records. Readable replicas can run read-only queries under supported configurations. Reports consume CPU, memory, tempdb, and storage on the secondary instead of the primary, although the primary still sends changes. The secondary does not turn reporting into a zero-cost operation.

I measure the primary before moving anything. If reports are not its bottleneck, the move can add architecture without improving user transactions. The right candidate is a report whose resource use interferes with important primary work and whose results can tolerate replica behavior. How stale can this report be before its answer becomes misleading?

Define Freshness Requirements

The secondary can lag behind committed primary data, especially under heavy log generation or slow redo. Synchronous commit does not mean every query on a readable secondary sees the latest committed row immediately. Hardening and redo are separate steps. Reports that must include the transaction just submitted should stay on the primary or use an explicit consistency strategy.

Set a freshness objective in seconds or minutes and monitor send and redo queues against it. I ask business owners which reports can show slightly older data. A dashboard saying current without a timestamp invites confusion when its replica is behind.

Configure Read-Intent Routing to a Readable Secondary

Read-only routing requires a listener, replica routing configuration, read access on the secondary, and a client connection that requests read-only intent. Connecting directly to a node or omitting the intent can bypass the desired route. Verify the driver’s behavior and the actual server reached, rather than assuming the connection string did what it said.

The sample connection string is illustrative and contains no credentials. Replace the listener and database names in the application configuration. Test the connection after failover because routing targets can change.

REM Command line
sqlcmd -S ag-listener.example.local -d SalesDB -K ReadOnly -Q "SELECT @@SERVERNAME AS connected_instance"

Observe Lag on the Readable Secondary

Monitor log send and redo queues, synchronization state, and the time at which report data becomes visible. Queue size is a useful clue but not a direct clock. A large report on the secondary can compete with redo for resources, causing freshness to worsen precisely when reporting demand rises.

This read-only query shows local database replica state, including queue sizes. Collect samples over time and pair them with an application-visible freshness marker.

SELECT DB_NAME(database_id) AS database_name,
       synchronization_state_desc,
       log_send_queue_size,
       redo_queue_size,
       last_redone_time
FROM sys.dm_hadr_database_replica_states
WHERE is_local = 1;

Expect Different Query Plans

A secondary has its own memory state and workload. Databases on a readable replica are read-only, and SQL Server can create temporary statistics for optimization where needed. Plans and performance can differ from the primary. A report that was fast on the primary does not automatically stay fast after routing. Secondary hardware and indexes inherited from the primary also matter.

I test the same report parameters on both sides and compare actual duration, CPU, reads, and result correctness. Index changes must be made through the primary and replicated. A query hint that helps one replica can behave differently on another, so verify across the topology.

Review Licensing and Edition

Readable replicas can require their own SQL Server licenses. Feature support and limits vary by version and edition. Confirm current official terms and the organization’s agreement before counting a passive disaster recovery server as free reporting capacity. Adding read workload can change the licensing and operational classification of that replica.

Include secondary hardware, storage, backups, monitoring, and maintenance in the cost. I compare this with simpler options such as tuning the report, caching a stable result, or scheduling it off peak. The most elegant routing diagram is not always the least expensive solution.

Keep Reports on a Readable Secondary Read-Only

Some reports write as they read. They fill a work table in the source database, update a last-run marker, or call a procedure with side effects. None of that runs on a read-only secondary without redesign. tempdb remains writable for temporary work, but the availability database does not accept writes. Check hidden writes inside stored procedures and reporting tools.

I inventory each candidate report’s dependencies before switching its connection. A SELECT-looking dashboard can trigger a logging procedure or session setup statement. The move should be deliberate, with a clear failure message or fallback if the secondary is unavailable.

Handle Failover and Routing Failure

After failover, the former primary can become a readable replica, or a replica can be unavailable. The listener and routing list must direct read-intent connections to a healthy eligible target. Decide whether reports should fall back to the primary, wait, or show a freshness warning. That policy depends on service priorities.

Test a planned failover with real reporting clients. I measure reconnection time and verify the server name and data timestamp after the transition. A route that works on a normal day but fails during the event it was built to survive needs repair before release.

Measure the Net Benefit

Compare primary CPU, I/O, transaction latency, secondary resource use, report latency, and replica lag before and after. A slower report can be acceptable if primary transactions improve and freshness remains within the agreed limit. A report that saturates redo and falls behind is not a successful offload.

A readable secondary is a useful tool when workload isolation and data freshness are planned together. Keep a timestamp in reports and a test for the route. The read can be offloaded, but responsibility for the answer remains with the application.

The secondary also needs its own capacity budget. A large sort or scan can consume memory and tempdb there while redo tries to keep up. Run the report at its peak size and watch queue growth, not just the report’s completion time. If freshness slips beyond the agreed limit, cap or reschedule the report.

Related reading on this blog: Read Only Routing Error: Client Unable to Establish Connection Because an Error was Encountered During Handshakes Before Login and Always On Secondary Replica Huge Redo Queue: Version Store is Full. New Version(s) Could Not be Added.

A readable secondary is not a free report server, it is a replica with measured freshness and operating cost.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.