Replica lag trade‑off in Azure SQL Database read‑scale‑out

Answer: Maximum tolerable replica lag

The replica lag that can be tolerated is the amount of extra latency your application can accept on a read. If the observed lag exceeds that tolerance, reading from the secondary adds more delay than reading from the primary, making read‑scale‑out counter‑productive.

Confirmed facts

• In Premium/Business Critical tiers the secondary replica is read‑only and shares the same compute as the primary; its base read latency (Ls) is usually close to the primary read latency (Lp).
• Replica lag (Lg) is exposed as the Azure Monitor metric "Replica Lag" or via the DMV sys.dm_geo_replication_link_status (column replication_lag_sec).
• There is no SLA‑bounded maximum lag; it varies with write rate, workload mix, and network conditions.

Likely explanation / trade‑off quantification

Effective latency when reading from the secondary is:

EffectiveLatency = Ls + Lg

Read‑scale‑out is beneficial only when:

EffectiveLatency < Lp

Re‑arranging gives the maximum tolerable lag:

Lg,max = Lp - Ls + ToleratedExtraLatency

Since Ls ≈ Lp in the same tier, the formula simplifies to:

Lg,max ≈ ToleratedExtraLatency

Thus, the lag you can allow is essentially the extra latency your application can tolerate.

Steps to quantify the trade‑off for your workload

1. Measure primary read latency (Lp) – run a representative read query with Application Intent=ReadWrite and capture average execution time via Query Performance Insight or extended events.
2. Measure secondary read latency (Ls) – run the same query with Application Intent=ReadOnly (or connect to the secondary directly) and record average latency.
3. Collect replica lag (Lg) over the same interval – enable the Azure Monitor "Replica Lag" metric or query sys.dm_geo_replication_link_status.
4. Compute effective secondary latency: Ls + Lg.
5. Compare to Lp. If Ls + Lg > Lp by more than your tolerated extra latency, route reads to the primary; otherwise keep them on the secondary.

Recommended thresholds and monitoring

• Define the application’s tolerated extra latency (e.g., +50 ms).
• Set an alert on replica lag at 80 % of that value (e.g., ≥40 ms) to proactively switch reads to the primary before lag becomes harmful.
• Use Azure Monitor alerts or Logic Apps to automatically update connection strings or routing rules when the threshold is crossed.
• Monitor overall DTU/vCore utilization on the primary; heavy read workloads on the secondary can still impact write throughput in Premium/Business Critical tiers.

Missing diagnostic detail

To compute a concrete threshold, we need the maximum additional read latency your application can tolerate. Please provide that value (e.g., "our reads may be up to 30 ms slower than primary reads") so a specific lag alert threshold can be defined.