Reporting load on SQL Server: readable secondaries, a reporting copy or the same instance

About this article: AI-assisted writing, reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.

A pattern some business applications show: during the day the system is fine, but when month-end reports run, data entry slows and users complain. Reports read large amounts of data and compete with the application's own work for the same processors, memory and disk.

There are three broad responses. Keep reports on the same server and manage them, send them to a readable secondary in an availability group, or keep a separate reporting copy of the data. This article compares them on what matters to an application owner: cost, how current the data is, and side effects.

Why it matters

Moving reports off the main server adds a second server to license, run and patch. Doing nothing leaves users waiting. The right answer depends on how fresh reports must be, which SQL Server edition you run, and whether the reports themselves can be made cheaper.

How it works

Option 1: the same instance, managed

Before adding servers, find out which reports cause the load and whether they can be made cheaper. Query Store, available in all editions of SQL Server 2022 and 2025, records query plans and their resource use over time, which helps identify the expensive report queries. A missing index or a report scanning years of history it does not need can be the whole problem.

Workload limits are a second tool. Microsoft describes Resource Governor as letting you reserve or limit the amount of CPU, memory, and physical I/O that user query workloads can use. This can reduce contention when reports use sessions that can be distinguished from data-entry sessions and assigned to a suitable workload group. It does not guarantee isolation: shared sessions, locks and system I/O can still interfere. In particular, the documented I/O controls govern user physical reads, not writes performed by system tasks. Verify the effect on data-entry response time under a representative concurrent workload. The edition matters: Microsoft states that from SQL Server 2025 it is available in Standard as well as Enterprise, and that In the previous versions, resource governor is only available in the Enterprise and Developer editions.

Scheduling also counts. Moving reports outside daytime hours can reduce contention, but account for other overnight users, backups and batch jobs.

Option 2: a readable secondary in an availability group

In an Always On availability group with Enterprise edition, a secondary replica can be configured for read access, and reports can be pointed at it. Microsoft notes this Offloads your secondary read-only workloads from your primary replica. Basic availability groups on Standard edition do not allow reading from the secondary.

Read-only routing lets the availability group listener send connections to a readable secondary. The client must connect to the listener and declare that it only reads; Microsoft says the client's connection string must specify the application intent as "read-only." The reporting tool must support that setting.

The side effects are documented:

• Data can lag. Changes appear on the secondary only after its redo process applies them. Microsoft advises that if the workload that cannot tolerate latency, you should run it on the primary.
• Row overhead on the primary. Enabling read access adds 14 bytes to modified rows on the primary to support row versioning, which Microsoft notes can cause page splits and increases storage.
• Reports can hold up the primary's cleanup. Long-running queries on the secondary can block ghost record cleanup on the primary, and Microsoft notes this state also prevents log truncation if it persists.
• Reports compete with redo. Heavy read workloads on the secondary use processor and disk that the redo threads also need.
• Licensing. The 2022 licensing guide states that a secondary serving reads is not a passive replica and must be licensed.

Option 3: a separate reporting copy

A reporting copy is a second database that holds the data reports need, kept current by some process. Microsoft's transactional replication is one such process. Changes are read from the transaction log and delivered to subscribers, and Microsoft says that within a publication transactional consistency is guaranteed. Changes can be delivered continuously or at scheduled intervals.

Transactional replication is available in Standard edition as a publisher and subscriber. It has conditions:

• Tables published for transactional replication must have a primary key. Some application databases have tables without one.
• The subscriber should be treated as read-only; changes made there are not sent back by default.
• Replication adds agents, a distribution database and jobs to monitor. Microsoft's transactional replication page also lists known issues with remote distributors on SQL Server 2025.

A copy can also be refreshed less often, for example by restoring last night's backup to a reporting server, or by reading a log shipping secondary in standby mode between restores. Those copies are hours old, which suits some reports and not others.

A separate copy needs its own licensing, unless it qualifies under Microsoft's terms for a non-production or passive use. Reporting is production use.

What to choose and why

• Start with option 1. Measuring and fixing the expensive reports costs the least and may remove the problem.
• Choose a readable secondary if you already run, or need, an Enterprise availability group for high availability and reports need near-current data.
• Choose a reporting copy if you run Standard edition, if reports can tolerate older data, or if reporting needs different indexes from the application.

Risks and limits

• A secondary for reports is not passive. Check licensing before pointing reports at a standby server.
• Application vendor support. Some vendors restrict replication or extra indexes on their databases. Check before changing anything.
• Test on isolated servers with synthetic or properly masked data. Measure report run times and data-entry response before and after.
• Lag is not fixed. A heavy batch on the primary can widen the lag on any copy. Decide what staleness each report can accept.
• No configuration commands are given here. Microsoft's pages cover setup.

Sources

• Microsoft Learn: About log shipping, source updated 2026-07-20.

Opened 14 November 2026.

• Microsoft Learn: Offload workload to secondary availability group replica, source updated 2026-01-19.
• Microsoft Learn: Transactional Replication, source updated 2026-07-20.
• Microsoft Learn: Publish Data and Database Objects, source updated 2026-09-09.
• Microsoft Learn: Resource Governor, source updated 2026-07-20.
• Microsoft Learn: Editions and supported features of SQL Server 2025, source updated 2026-07-20.
• Microsoft Learn: Editions and supported features of SQL Server 2022, source updated 2026-07-20.
• Microsoft: SQL Server 2022 Licensing Guide, November 2022 (PDF).

Related service: Cloud hosting.

Related future topics

• High availability on SQL Server Standard edition.
• SQL Server licensing in virtual machines.
• Index and statistics maintenance on hosted SQL Server.

Learning notes

• Measure first: find expensive reports and confirm whether workload classification reduces contention; it cannot guarantee isolation.
• Readable secondaries need Enterprise edition, read-only intent in the connection, and a licence.
• A readable secondary adds row overhead on the primary and can hold up its cleanup.
• Transactional replication works on Standard edition but needs a primary key on each published table.
• Every copy lags; decide how old each report's data may be.

This article was originally published on 14 November 2026.