How to keep data consistency for read replica in Azure SQL MI

Question

Hi,

I have a question about read replica in Azure SQL MI. The question is how to keep data consistency for read replica? transaction log or something else. Is there any document about this?

best regards

Jindong

Answer 1

Hello, Welcome to MS Q&A

To maintain data consistency for read replicas in Azure SQL Managed Instance (MI), data changes made on the primary replica are propagated to read-only replicas either synchronously or asynchronously, depending on the type of replica. Reads from a read-only replica are always asynchronous with respect to the primary. This means that while reads within a session connected to a read-only replica are transactionally consistent, there can be a variable data propagation latency that affects the visibility of the latest changes.

For applications that require guaranteed data consistency or immediate visibility of committed data, it is recommended to use the primary replica instead of the read-only replicas. Monitoring data propagation latency is also crucial to ensure that the application can accommodate any delays that may occur.

And if you are looking for consistency guaranteed --> I think Transaction Log-Based Replication for Read Consistency as Transactional replication typically starts with a snapshot of the publication database objects and data. As soon as the initial snapshot is taken, subsequent data changes and schema modifications made at the Publisher are usually delivered to the Subscriber as they occur (in near real time). The data changes are applied to the Subscriber in the same order and within the same transaction boundaries as they occurred at the Publisher; therefore, within a publication, transactional consistency is guaranteed.

please check this

References:

• Use read-only replicas to offload read-only query workloads
• Use Transaction Log-Based Replication for Read Consistency

Please let us know if you have any questions

Kindly accept answer if it helps

Thanks

Deepanshu

Answer 2

Hi @Li, Jindong ,

Thanks for your patience.

I have checked with the internal team and below is the reply:

https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/high-availability-sla-local-zone-redundancy?view=azuresql#business-critical-service-tier

They quote : “High availability is implemented using a technology similar to SQL Server https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server?view=sql-server-ver16. The cluster includes a single primary replica that is accessible for read-write customer workloads, and up to three secondary replicas (compute and storage) that contain copies of data. The primary replica constantly pushes changes to the secondary replicas sequentially to ensure that data is persisted on a sufficient number of secondary replicas before committing each transaction. This process guarantees that, if the primary replica or a readable secondary replica become unavailable for any reason, a fully synchronized replica is always available to fail over to. ”

Let us know if this helped or you have a different ask.

Thanks