Offloading Read-Heavy Workloads with Azure SQL Database Read Scale-Out

Reducing Primary Node Contention

When a database experiences high CPU or memory pressure due to a mix of transactional writes and heavy reporting queries, the primary node becomes a bottleneck. In Azure SQL Database, the Read Scale-Out feature solves this by allowing you to route read-only traffic to a secondary replica, preserving the primary node's resources for write operations.

Prerequisites and Tier Compatibility

Read Scale-Out is not available across all service tiers. Before attempting implementation, verify your database is hosted on one of the following:

• Premium
• Business Critical

The General Purpose tier does not support this feature because it uses a remote storage architecture rather than the local SSD-based replicas required for synchronous/asynchronous read-offloading.

Implementing the Read-Only Connection

The infrastructure for Read Scale-Out is enabled by default in supported tiers. The routing logic is handled by the gateway based on the ApplicationIntent property in your connection string.

Configuration Example:

Server=tcp:yourserver.database.windows.net,1433;Initial Catalog=yourdb;Persist Security Info=False;User ID=youruser;Password=yourpassword;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;ApplicationIntent=ReadOnly

Implementation Details:

• Run Location: This change is applied in the application's configuration file or connection string manager, not within the SQL engine.
• Permissions: The database user requires standard CONNECT and SELECT permissions. No special administrative roles are needed to access the read-only replica.
• Risk: If ApplicationIntent=ReadOnly is omitted, the connection defaults to the primary replica, which may exacerbate performance issues on the write node.

Verifying Routing and Constraints

Because both the primary and read-only replicas share the same DNS endpoint, you must query the system to confirm where your session is actually executing.

Step 1: Verify Node Identity

Run the following command on both a standard connection and a ReadOnly connection:

SELECT @@SERVERNAME AS [NodeName];

If the routing is successful, the NodeName returned by the ReadOnly connection will differ from the one returned by the primary connection.

Step 2: Test Write Protection

To ensure the connection is strictly read-only, attempt a data modification on the ReadOnly connection:

UPDATE YourTable SET YourColumn = 'Test' WHERE Id = 1;

The operation must fail with a runtime error indicating that the database is read-only. If the update succeeds, the connection is incorrectly routed to the primary node.

Architectural Trade-offs and Limitations

Factor	Behavior	Engineering Impact
Replication	Asynchronous	Potential for "replication lag"; the secondary may be milliseconds behind the primary.
Operation Scope	SELECT only	Any DDL (schema changes) or DML (inserts/updates) will trigger an error.
Resource Pool	Dedicated	Read-only queries do not consume the CPU/Memory limits of the primary node.

Monitoring Load Distribution

To determine if the offloading is effectively reducing pressure on the primary, monitor the waiting tasks. Run this on the primary node to see if read-related waits have decreased:

SELECT wait_type, waiting_tasks_count
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE '%READ%';

Rollback Procedure

Since this operation only changes the application's connection intent and does not modify the database state or schema, rollback is achieved by removing the ApplicationIntent=ReadOnly parameter from the connection string and restarting the application. This redirects all traffic back to the primary node.