Scale Reads in Azure SQL Hyperscale with Readable Secondary Replicas

Problem: Read‑heavy SaaS workloads overwhelm the primary

Many SaaS applications experience bursts of read traffic that compete with write operations on the same Azure SQL Database primary replica. When the primary becomes CPU‑ or I/O‑bound, query latency rises and write throughput suffers. Scaling up the primary can be costly and may still leave idle capacity during off‑peak periods.

How Hyperscale readable secondaries work

Azure SQL Hyperscale separates compute from storage. The primary replica writes transaction log records to a shared log service; readable secondary replicas consume the same log stream and redo changes locally, providing near‑real‑time copies of the data without moving pages. Because secondaries share the storage layer, adding one does not require data copy or long‑running seed operations.

Provision a readable secondary

1. Ensure the database is in the Hyperscale service tier.
2. You need the Microsoft.Sql/servers/databases/write permission (typically contributed by the SQL DB Contributor role).
3. Use Azure CLI, PowerShell, the portal, or T‑SQL to create the replica.

Example CLI command (run in a terminal with Azure CLI installed and you logged in):

az sql db replica create \
  --resource-group  \
  --server  \
  --name  \
  --partner-server  \
  --partner-database  \
  --partner-resource-group 

Replace the placeholders with your actual values. The command creates a readable secondary on the specified partner server; no data movement occurs.

Worked example: verify lag and route reads

After the secondary is online, connect your read‑only workload using a connection string that includes Application Intent=ReadOnly. For example (ADO.NET):

Server=tcp:.database.windows.net;Database=;Authentication=Active Directory Integrated;Application Intent=ReadOnly;

To monitor replication lag, query the dynamic management view on the primary:

SELECT replica_id,
       log_send_queue_size,
       redo_queue_size
FROM   sys.dm_hs_secondary_replicas;

The log_send_queue_size shows how many log KB are waiting to be sent; redo_queue_size indicates work the secondary has yet to apply. Consistently low values (typically under a few MB) suggest the secondary is keeping up.

Trade‑offs and limitations

• Replication lag: Secondaries may trail the primary by a few seconds. Applications that require strict read‑after‑write consistency should route those reads to the primary or use session consistency hints.
• Cost: Each readable secondary consumes vCore licenses on the Hyperscale tier. Evaluate whether the price of extra secondaries is cheaper than scaling up the primary or using the serverless tier.
• Tier restriction: Readable secondaries are available only in the Hyperscale service tier; Standard, Premium, and General Purpose tiers do not support this feature.

Actionable closing

If your SaaS application exhibits read‑spike latency, start by adding a single readable secondary to your Hyperscale database. Monitor lag via sys.dm_hs_secondary_replicas and observe primary CPU/DTU usage. Adjust the number of secondaries based on observed lag and cost, and direct read‑only traffic to them using Application Intent=ReadOnly. This approach provides elastic read capacity without data movement or application redeployment.