Offloading Read Workloads with Azure SQL Hyperscale Named Replicas

The Problem: Reporting Queries Slowing Down OLTP

You run a busy e-commerce database on Azure SQL Hyperscale. During peak hours, long-running analytics queries — think daily sales rollups or inventory snapshots — start contending with the primary's OLTP traffic. Lock waits climb, P99 latency spikes, and the checkout flow suffers. Scaling up the primary compute helps, but you're paying for capacity you only need during reporting windows.

Hyperscale named read-scale replicas let you point read-only workloads at a separate compute endpoint that shares the same underlying data pages. The primary keeps serving writes while replicas handle reports, ad-hoc queries, or Power BI refreshes. This post walks through when that separation pays off, how to provision a replica, and the trade-offs you'll hit in production.

How Named Replicas Differ from Geo-Replicas

Geo-replication creates a full copy in another region for disaster recovery. Named replicas stay in the same region, attach to the same page servers, and expose a distinct connection string. Each replica gets its own service objective (vCore count, generation) so you can size compute for read throughput without touching the primary's SKU.

Because the storage layer is shared, creating a replica takes seconds — no data copy. The replica's buffer cache warms up as queries run, and you can pause it when the reporting window closes to stop compute charges. Storage cache (RBPEX) costs persist while paused, but they're a fraction of vCore pricing.

Provisioning a Named Replica: Portal and T-SQL

In the Azure portal, open your Hyperscale database, select Named replicas under Settings, and click Create. Pick a name, service objective (for example, HS_Gen5_4 for 4 vCores), and optionally a different maintenance window. The replica appears with its own endpoint: myapp-replica.database.windows.net.

Equivalent T-SQL on the primary (run in master):

ALTER DATABASE [SalesDB] ADD SECONDARY ON SERVER [my-logical-server] 
  WITH (NAME = 'SalesDB_Reporting', SERVICE_OBJECTIVE = 'HS_Gen5_4');

Permissions: you need dbmanager role on the logical server or be the subscription owner. The replica inherits the primary's collation, containment, and firewall rules.

Worked Example: Nightly Inventory Snapshot

A retailer runs a 45-minute inventory aggregation every night at 02:00 UTC. The primary runs on HS_Gen5_16 (16 vCores) for daytime OLTP. Instead of scaling the primary to 32 vCores for that window, they create a named replica SalesDB_Inventory at HS_Gen5_8.

1. Create replica at 01:45 UTC via Azure CLI: az sql db replica create -g rg-prod -s sql-prod -n SalesDB --partner-name SalesDB_Inventory --service-objective HS_Gen5_8
2. Update the reporting connection string to point at SalesDB_Inventory.database.windows.net.
3. At 03:00 UTC, pause the replica: az sql db replica update -g rg-prod -s sql-prod -n SalesDB --replica-name SalesDB_Inventory --pause

Result: the primary never sees the scan load. Compute cost for the 8-vCore replica runs ~75 minutes (including warm-up), roughly 15% of keeping the primary at 32 vCores 24/7. Storage cache charges continue while paused but are negligible compared to vCore hours.

Replication Lag and Read Consistency

Replication is asynchronous. Under steady write volume, lag is typically sub-second. During a bulk load or index rebuild on the primary, log generation can outpace the log service's dispatch to page servers, and replicas may see stale data for seconds to minutes.

Check lag on the primary with:

SELECT replica_id, database_id, role_desc, synchronization_state_desc,
       last_commit_time, last_received_time, last_hardened_time
FROM sys.dm_hadr_database_replica_states;

If synchronization_state_desc shows NOT SYNCHRONIZING or the time delta exceeds your SLA, the replica is falling behind. Applications that need read-your-writes consistency must stay on the primary or implement a retry-with-backoff that verifies the expected row version.

Cost Traps and Operational Limits

• Idle replicas still bill storage cache. A paused HS_Gen5_16 replica incurs RBPEX charges (~$0.10/GB/month in most regions). Delete the replica if you won't need it for weeks.
• Replica count caps vary. As of 2024, most regions allow up to 4 named replicas per primary in the standard Hyperscale tier; the Hyperscale Premium tier raises this. Verify current limits in the pricing calculator before designing a fan-out topology.
• Long-running transactions on the primary pin log records. A 6-hour ETL transaction prevents log truncation, growing the Hyperscale log volume and increasing storage costs for all replicas until commit or rollback.
• No automatic failover to read-write. If the primary goes down, you must manually promote a geo-replica (not a named replica) for write traffic. Named replicas are strictly read-only.

Verification Checklist Before Production

1. Confirm regional replica limit and SKU availability in the Azure pricing calculator.
2. Run the DMV query above during a representative write load; ensure lag stays within your application's tolerance.
3. Test connection routing: update one reporting job's connection string, monitor query performance and error rates for a full cycle.
4. Set up Azure Monitor alerts on replication_lag_seconds (available via Log Analytics) to catch lag spikes.
5. Document the pause/resume schedule or automation (Azure Automation, Logic Apps) so replicas don't run unattended.

Closing: Start Small, Measure, Then Scale

Named replicas shine when you have identifiable read-heavy windows — nightly reports, dashboard refreshes, data-science notebooks — and you want to isolate that load without duplicating storage. Create one replica for your noisiest report, measure lag and cost for two weeks, then decide whether to add more or resize. The portal and CLI make iteration fast; the main discipline is remembering to pause or delete what you're not using.