Azure SQL Database Read Scale-Out: Offloading Reads Without Breaking Consistency

The problem: reporting queries are starving your OLTP workload

Your transactional application is healthy until the monthly report runs. Suddenly, the primary replica’s CPU spikes, user-facing queries time out, and the database looks sick. In Azure SQL Database Premium and Business Critical tiers, you have a built-in escape hatch: Read Scale-Out. Instead of forcing every read to compete with writes on the primary node, you can send read-only queries to a secondary replica. The catch is that the secondary is updated asynchronously, so you trade some freshness for headroom.

What Read Scale-Out actually does

Read Scale-Out is not a separate SKU or an add-on. In Premium and Business Critical service tiers, Azure SQL Database already provisions a secondary read-only replica. By default, all traffic—reads and writes—lands on the primary replica. When you enable Read Scale-Out routing, read-only queries can be directed to that secondary node, reducing CPU, memory, and I/O pressure on the primary.

The mechanism is simple: you add ApplicationIntent=ReadOnly to your connection string. The Azure SQL Database gateway then routes that connection to the secondary replica rather than the primary. If the property is absent or set to ReadWrite, the connection goes to the primary as usual.

Two constraints matter immediately. First, the secondary replica is read-only. Any attempt to run an INSERT, UPDATE, DELETE, or DDL statement over that connection will fail. Second, the replica is updated asynchronously. A commit that just succeeded on the primary may not be visible on the secondary for a short period.

A worked example: proving you hit the secondary

Before you refactor application code, verify that routing works. You need a connection string that targets your Azure SQL Database and includes the read-only intent.

Run the following in SQL Server Management Studio (SSMS), Azure Data Studio, or any T-SQL client. Replace the placeholders with your actual server and database names. You need standard database connection permissions; no special administrative role is required for this check.

Server=tcp:<myserver>.database.windows.net,1433;
Initial Catalog=<mydatabase>;
ApplicationIntent=ReadOnly;
Authentication=Active Directory Default;
Encrypt=True;
TrustServerCertificate=False;

Once connected, execute:

SELECT @@SERVERNAME;

The expected check is that the returned node name differs from the name you see when connecting without ApplicationIntent=ReadOnly. If the names match, the gateway did not route you to the secondary—double-check that your tier is Premium or Business Critical and that the connection string was parsed correctly.

Next, confirm read-only enforcement. On the same connection, run:

UPDATE dbo.SomeTable SET SomeColumn = 'test';

You should receive an error indicating the operation is not permitted on a read-only replica. If the update succeeds, you are still connected to the primary.

To gauge staleness, monitor replication lag using dynamic management views. A common starting point is sys.dm_geo_replication_link_status, but DMV names and columns evolve across service updates, so verify the current view in your environment before relying on it in production monitoring.

The trade-off: eventual consistency and a narrower feature set

Read Scale-Out solves a capacity problem, but it introduces a consistency problem. Because replication is asynchronous, a user who writes a row and immediately reads it back over a read-only connection may not see the change yet. This “stale read” pattern breaks read-your-writes workflows. It is usually acceptable for dashboards, exports, and analytical queries that aggregate over minutes or hours. It is rarely acceptable for shopping-cart summaries or profile pages that must reflect the latest commit.

The secondary replica also does not support every primary-node capability. Certain administrative operations and write-heavy locking scenarios are unavailable there. Treat the secondary as a query engine, not as a failover peer for administrative tasks.

When to use it—and when to leave it alone

Use Read Scale-Out when your read workload is genuinely read-only and tolerant of short lag. Reporting databases, BI extracts, and ad-hoc SELECT queries are good candidates. Avoid it when application logic depends on immediate visibility of writes, or when the query requires features that exist only on the primary.

If the secondary replica becomes unavailable, connections using ApplicationIntent=ReadOnly may fail rather than silently falling back to the primary. Your application should handle that exception and retry against a primary connection if business continuity requires it.

Actionable checklist

• Confirm your database is in the Premium or Business Critical tier; Read Scale-Out is not available in General Purpose or Standard.
• Add ApplicationIntent=ReadOnly only to connection strings used by read-only workloads.
• Verify routing with SELECT @@SERVERNAME and confirm the node differs from your primary connection.
• Test read-only enforcement with a harmless UPDATE that you expect to fail.
• Monitor replication lag through the appropriate dynamic management view for your service version.
• Document which application features tolerate lag and which must remain on the primary.

Read Scale-Out is a targeted tool, not a universal accelerator. Point it at the right queries, respect the replication delay, and it will keep your primary replica responsive under load.