Azure SQL Database Read-Scale Out: Offloading Read-Only Workloads

Solving Primary Node Contention

When a database handles both high-volume transactional writes and complex reporting queries, the primary node often suffers from CPU and memory contention. This leads to increased latency for end-users and timeouts for analytics reports. The most direct solution in Azure SQL Database is Read-Scale Out, which allows you to offload read-only workloads to a dedicated secondary replica without paying for an additional database instance.

The key takeaway is that Read-Scale Out is a built-in feature of the Premium and Business Critical service tiers. It provides a read-only replica that is automatically synchronized from the primary node. To use it, you do not change the server address; instead, you modify the connection string to signal the Azure gateway to route the traffic.

The Routing Mechanism

Azure SQL Database uses a gateway to manage connections. When a client connects, the gateway checks the connection properties. If the ApplicationIntent property is set to ReadOnly, the gateway routes the session to the read-only replica. If the property is absent or set to ReadWrite, the session is routed to the primary node.

Implementation Example

To implement this, you must maintain two separate connection strings in your application: one for transactional operations (Writes) and one for reporting/read-only operations (Reads).

Primary Connection String (Writes):

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;

Read-Only Connection String (Reads):

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;ApplicationIntent=ReadOnly;Connection Timeout=30;

Verification Steps

To verify that your application is actually hitting the replica and not the primary node, run the following diagnostic checks using a SQL client (like SSMS or Azure Data Studio):

1. Identify the Node: Connect using the ApplicationIntent=ReadOnly string and execute:
   

   SELECT @@SERVERNAME AS [NodeName];

   Connect again using the primary string and run the same command. The NodeName should be different for each connection.
2. Test Write Restrictions: While connected to the read-only replica, attempt a write operation:
   

   INSERT INTO YourTable (Column1) VALUES ('Test');

   The system should return a read-only error, confirming the session is correctly routed to the replica.
3. Check Replication Lag: Because replication is asynchronous, there may be a delay between a write on the primary and its visibility on the replica. You can monitor this via Dynamic Management Views (DMVs) to ensure your reporting data is sufficiently current.

Operational Limits and Constraints

Read-Scale Out is not a "silver bullet" for all performance issues. Engineers must account for the following architectural constraints:

• Tier Availability: This feature is only available in Premium and Business Critical tiers. If you use the General Purpose tier, the ApplicationIntent=ReadOnly flag is ignored, and all traffic is routed to the primary node, potentially worsening contention.
• Eventual Consistency: Replication is asynchronous. This creates a "read-after-write" risk: if a user updates their profile (Primary) and immediately refreshes the page (Replica), they may see the old data for a few milliseconds.
• Resource Sharing: While the replica handles the query execution, it is important to note that in some configurations, the replica shares the same resource limits (DTUs/vCores) as the primary. It solves CPU contention for the primary node's execution engine, but it does not increase the overall throughput capacity of the database service tier.

Common Implementation Mistakes

Using a single connection pool: A common error is using a single database connection pool for both read and write operations. Since the routing happens at the connection establishment phase, you must implement two distinct pools in your application code to ensure the ApplicationIntent flag is respected.

Over-reliance on the replica for critical reads: Avoid routing "critical path" reads (like checking a user's balance before a transaction) to the read-only replica. Due to the asynchronous nature of the replication, these reads should always occur on the primary node to ensure strong consistency.