Reducing Primary Node Pressure with Azure SQL Read-Scale Out

The Resource Contention Bottleneck

A common failure pattern in high‑traffic Azure SQL databases is the "reporting spike." You have a primary node handling critical transactional writes, but a heavy read‑only report or a complex API aggregation suddenly consumes 90% of the CPU. This creates a bottleneck where write operations queue up, latency spikes for end‑users, and the database feels sluggish despite having adequate overall capacity.

The solution is to stop treating your primary node as a catch‑all for every query. By leveraging Read‑Scale Out, you can offload read‑heavy workloads to a secondary replica, reserving the primary node exclusively for writes and critical reads.

How Read‑Scale Out Utilizes Existing Infrastructure

In the Premium and Business Critical service tiers, Azure SQL Database maintains a set of high‑availability replicas to ensure your data survives a node failure. Normally, these replicas sit idle. Read‑Scale Out allows you to use one of these existing replicas to handle read‑only traffic.

Because this uses the built‑in HA (High Availability) infrastructure, there is no additional hourly cost for the replica itself. You are essentially getting a second compute node for the price of one, provided you are on a supported tier.

Implementing the Read‑Only Route

To use Read‑Scale Out, you do not change the server address. Instead, you modify the connection string to tell the Azure Gateway to route the request to the read‑only replica.

Configuration Example

Add ApplicationIntent=ReadOnly to your connection string. Below is a comparison of the two connection types:

Target	Connection String Fragment	Behavior
Primary Node	Server=tcp:myserver.database.windows.net...	Read‑Write access; default routing.
Read Replica	Server=tcp:myserver.database.windows.net...;ApplicationIntent=ReadOnly	Read‑Only access; routed to secondary.

Implementation Note: This change is made at the application level. You should create two separate connection pools in your application code: one for ReadWrite operations and one for ReadOnly operations.

The Consistency Trade‑off

Read‑Scale Out uses asynchronous replication. This means there is a slight delay (usually milliseconds) between when a transaction is committed on the primary node and when it appears on the read‑only replica. This is known as eventual consistency.

If your application follows a "Write‑then‑Read" pattern (e.g., a user updates their profile and is immediately redirected to a page that reads that profile), the user might see the old data for a fraction of a second if the read is routed to the replica. For these specific "read‑your‑own‑writes" scenarios, you must route the query to the primary node.

Verification and Diagnostics

To verify that your traffic is actually hitting the replica and not the primary, you can query the sys.dm_os_server_memory or check the node identity. However, a practical way to test the routing is to attempt a write operation using the Read‑Only connection string.

-- Run this using a connection with ApplicationIntent=ReadOnly
CREATE TABLE #TestTable (Id INT);

Expected Result: The command should fail with an error indicating that the database is read‑only. If the table is created successfully, your connection is hitting the primary node, and your ApplicationIntent setting is not being honored.

Monitoring Impact

To measure the success of the offload, monitor the primary node's CPU and IO wait stats using sys.dm_os_wait_stats. You should see a decrease in SOS_SCHEDULER_YIELD or IO‑related waits on the primary node after shifting reporting queries to the replica.

Limitations to Consider

• Tier Restrictions: This feature is unavailable in General Purpose or Standard tiers. If you are on those tiers, you would need to implement a manually managed read‑replica or upgrade your tier.
• Temporary Objects: Because the replica is strictly read‑only, you cannot create temporary tables or use SELECT INTO. You must use Table Variables or perform data manipulation in the application layer.
• Replication Lag: Under extreme write loads, the replica may lag. While this doesn't stop the primary from working, it increases the window of eventual inconsistency.

Summary Checklist

1. Confirm service tier is Premium or Business Critical.
2. Split application connection pools into ReadWrite and ReadOnly.
3. Append ApplicationIntent=ReadOnly to the read‑only pool.
4. Identify "read‑your‑own‑writes" paths and keep them on the primary node.
5. Verify routing by attempting a write on the read‑only connection.