Offload Reporting to Azure SQL Hyperscale Read Replicas

The problem: your monthly report just slowed down checkout

Your application's primary Azure SQL Database handles customer transactions all day. Every Monday morning, the reporting team runs large ad-hoc queries against that same database. Those scans compete with the transactional workload, latency spikes, and support tickets arrive.

The useful takeaway: if your database is on the Hyperscale service tier, you can add readable secondary replicas. Reporting connects to a replica with ApplicationIntent=ReadOnly, leaving the primary free for writes. The trade-off is asynchronous replication: a replica can show data that is seconds or minutes behind the primary, so it is not suitable for read-your-writes consistency.

How readable secondaries work in Hyperscale

Azure SQL Hyperscale separates compute from storage. The primary writes transaction log records to a distributed storage layer, and page servers serve data files from that same layer. A readable secondary is a separate compute node that reads from the same storage, not a separate copy of the database you manage.

Changes move from the primary to the secondary asynchronously through log replay. Because the path is not synchronous, a commit on the primary is not immediately visible on the secondary. Read replicas are designed to offload read-only work such as reporting, analytics, and ad-hoc queries. They do not increase primary write throughput, and they are not automatic failover targets.

Connecting to the replica

Applications reach a readable secondary through the database listener by setting ApplicationIntent=ReadOnly in the connection string. When this flag is present, the gateway routes the connection to an online readable secondary if one exists. Without it, the connection goes to the primary as usual.

Example read-only connection string:

Server=tcp:myserver.database.windows.net,1433;
Database=mydb;
ApplicationIntent=ReadOnly;
User ID=dbuser;
Password=YourPassword;
Encrypt=True;
TrustServerCertificate=False;
Connection Timeout=30;

Test this from your application server or with sqlcmd on a client machine that has network access to the Azure SQL endpoint. The login needs at least read permissions on the database. A write statement such as INSERT or UPDATE executed on this connection will fail because the database is read-only on the secondary.

Worked example: separate reads from writes

Use two connection strings in your application: one for the transactional path and one for reporting.

• Primary (read-write): omit ApplicationIntent or set it to ReadWrite.
• Replica (read-only): set ApplicationIntent=ReadOnly.

To verify which role a connection is using, run:

SELECT @@READONLY AS IsReadOnly;

A result of 1 means the session is connected to a read-only database, which is expected on a readable secondary. A result of 0 means you are on the primary.

To observe replication lag, insert a row with a timestamp on the primary, then poll the same table from a read-only connection until the row appears. The elapsed time is your observable lag for that workload. Do not use this pattern for features that require the user to see their own write immediately.

Trade-offs, limits, and verification

• Stale reads: read-your-writes consistency is not guaranteed. Recent primary writes may not yet be visible on the secondary.
• Not high availability by itself: a readable secondary is not an automatic failover target. Use failover groups if you need planned or unplanned failover for the primary.
• Cost: readable secondaries carry hourly vCore charges in addition to the primary.
• Workload impact: heavy or long-running queries on a replica can increase log replay lag and affect page server storage usage.

Practical check after provisioning: in the Azure portal, confirm the replica shows as online. Then run a read-only query and SELECT @@READONLY; to confirm routing. Compare SELECT MAX(ModifiedDate) from the same table on the primary and on the secondary after a recent write to measure lag.

Removing a replica

Adding a readable replica provisions a billable resource. To stop charges, remove the replica in the Azure portal or through your automation tooling. There is no data to back out on the primary; you are simply de-provisioning the secondary compute node.

Closing

If your reporting queries are contending with OLTP traffic on a Hyperscale database, readable secondaries are a purpose-built way to separate the workloads. Confirm current replica limits, pricing, and regional availability in the Azure portal before you design around them, and treat replica data as potentially stale.