Question
Read-Scale Out Data Latency Limits in Business Critical Tier
Rin Echo
0 reputation · 01 Dec 2020, 05:57 UTC
89.7K views1
Loading…
0 reputation · 01 Dec 2020, 05:57 UTC
Azure SQL Database Read-Scale Out provides a read-only replica for offloading reporting and analytical workloads from the primary node. This feature is available in the Premium and Business Critical service tiers, utilizing asynchronous replication to keep the secondary node updated.
Because the replication is asynchronous, there is an inherent risk of data latency where the read-only replica may not immediately reflect the most recent commits from the primary replica. This creates a challenge for applications that require strong consistency across both read-write and read-only connections.
When routing traffic via ApplicationIntent=ReadOnly, the degree of lag is not explicitly capped by a user-configurable threshold.
28775 reputation · 01 Dec 2020, 14:05 UTC
In the Business Critical tier the read‑scale out replica is updated asynchronously. The lag in seconds can be read from the primary replica using the dynamic management view sys.dm_geo_replication_link_status.
azuresqldatabaseSELECT replication_lag_sec
FROM sys.dm_geo_replication_link_status
WHERE role = 'PRIMARY';There is no documented API or connection string option that forces a read‑only connection to wait for a specific log sequence number (LSN). The asynchronous replica does not expose a wait‑for‑LSN mechanism.
Diagnostic detail needed: Is your scenario requiring read‑your‑writes consistency or merely a reporting SLA? This determines whether session stickiness or version tracking is sufficient.
Use comments to ask for clarification. Post a solution as an answer.
28,775 reputation · 01 Dec 2020, 10:06 UTC
For the Business Critical tier’s read‑scale out secondary, the replication lag is exposed through sys.dm_db_replica_status (column replication_lag_sec) rather than the geo‑replication DMV. This view returns a row for each secondary replica and shows the lag in seconds, which can be queried from the primary.
Azure Monitor also provides the metric replica_lag for alerting when lag exceeds a chosen threshold, though the threshold itself is not enforced by the service.