Scaling Reads on Azure SQL Hyperscale: What You Gain and What to Watch

The problem: read‑heavy workloads on a single primary

When a reporting dashboard, a BI tool, or a background job starts hammering the same Azure SQL Database that also serves OLTP traffic, the primary’s CPU and buffer pool become a bottleneck. Adding more vCores helps, but you still pay for compute that sits idle during quiet periods. Azure SQL Database Hyperscale with read scale‑out offers a different lever: spin up read‑only replicas that share the same storage layer, so you can spread the read load without copying data.

How Hyperscale separates compute from storage

Hyperscale stores every 8 KB page in a fleet of page servers backed by Azure Blob Storage. The primary compute node and each read replica attach to the same page servers, so adding a replica does not increase storage consumption. You can provision up to 30 read replicas, each with its own vCore allocation, and pause any replica when it’s not needed. The primary remains the sole write target; replicas are asynchronous by default, with typical replication lag in the sub‑second range.

Routing read‑only traffic

Applications indicate they only need reads by adding ApplicationIntent=ReadOnly to the connection string. The gateway then directs the session to an available replica. Example connection string (run from your app host, not the database):

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

No code changes are required beyond the connection string. If a replica is paused or unavailable, the gateway falls back to the primary, so the application stays online.

Worked example: checking replication lag

To verify that replicas are keeping up, query the DMV sys.dm_hadr_database_replica_states from any replica (requires VIEW DATABASE STATE permission). Run this in Azure Data Studio or SSMS connected to a read‑only replica:

SELECT
    replica_id,
    is_primary_replica,
    last_commit_time,
    DATEDIFF(millisecond, last_commit_time, SYSUTCDATETIME()) AS lag_ms
FROM sys.dm_hadr_database_replica_states
WHERE is_primary_replica = 0;

The lag_ms column shows how far behind each replica is. Values under a few hundred milliseconds are typical; spikes above a second usually coincide with heavy write bursts or maintenance windows.

Trade‑offs and limitations

• Stale reads: Because replication is asynchronous, a read replica can return data that is a few milliseconds (or seconds) old. Queries that require strong consistency must target the primary.
• Replica ceiling: The hard limit is 30 read replicas per database. Beyond that you need to shard or use multiple Hyperscale databases in an elastic pool.
• No serverless tier: Hyperscale only offers provisioned vCore compute. Idle replicas still accrue cost unless you pause them manually or via automation (e.g., Azure Functions + Azure CLI).
• No cross‑region geo‑replication: Only zone‑redundant HA within a single region is supported. For disaster recovery across regions you must fall back to standard tier geo‑replication or manual backup/restore.
• Long‑running transactions: Open transactions on the primary prevent log truncation, inflating page‑server storage and potentially raising the storage bill.

Cost check: compute vs. storage

Because storage is shared, the monthly storage charge stays constant regardless of replica count. Compute cost scales linearly: each replica’s vCore price is the same as the primary’s. A quick sanity check (prices from the Azure pricing page, 2026‑10):

Configuration	vCores	Estimated monthly compute cost*
Primary only (8 vCore)	8	$1,200
Primary + 3 replicas (8 vCore each)	32	$4,800
Primary + 3 replicas, 2 paused 12 h/day	32 (effective 20)	~$3,000

*Rounded, excludes storage and networking. Use the Azure pricing calculator for exact numbers.

Actionable next steps

1. Create a Hyperscale database (or migrate an existing one) in the Azure portal and enable 2–3 read replicas.
2. Update the connection strings of read‑only workloads to include ApplicationIntent=ReadOnly.
3. Schedule a daily Azure Monitor alert on the lag_ms query above; alert if lag exceeds 2 seconds for more than 5 minutes.
4. Implement a simple automation (Azure Function + az sql replica update --capacity 0) to pause replicas during known low‑traffic windows.
5. Review the storage metrics (Data Space Used, Log Space Used) after a week to confirm storage independence from replica count.

By treating read scale‑out as a compute‑only lever, you can keep latency low for analytical queries while preserving the primary’s OLTP throughput—just remember to monitor lag and pause unused replicas to avoid surprise bills.