Azure SQL Data Sync

SQL Data Sync is a service built on Azure SQL Database that lets synchronize the data you select bi-directionally across multiple databases, both on-premises and in the cloud.

Data Sync is based around the concept of a sync group. A sync group is a group of databases that you want to synchronize. It uses a hub and member topology to synchronize data.

One of the databases in the sync group as the hub database. The rest of the databases are member databases. Sync occurs only between the hub and individual members.

·      The Hub Database must be an Azure SQL Database.

·      The member databases can be either databases in Azure SQL Database or in instances of SQL Server.

Data Sync works By Tracking Data Changes by Using Insert, Update and Delete Triggers in a Separate table in the user Database. Then Sync DB takes care of, merging the tracked data to all other member databases.

Note-

Databases table need to have primary key which is going to participate in data sync. Currently SQL Data Sync does not support Azure SQL Database Managed Instance

While setting up data sync you will have to define the conflict resolution policy.

Conflict Resolution

Select Hub win or Member win.

Hub win means when conflicts occur, data in the hub database overwrites conflicting data in the member database.

Member win means when conflicts occur, data in the member database overwrites conflicting data in the hub database.

Data sync supports both Automatic and manual Sync-

If you choose On, enter a number, and select Seconds, Minutes, Hours, or Days in the Sync Frequency section.

The first sync begins after the selected interval period elapses from the time the configuration is saved.

Used Cases:

Data Sync is useful in cases where data needs to be kept updated across several databases in Azure SQL Database or SQL Server. Here are the main use cases for Data Sync:

·      Hybrid Data Synchronization: With Data Sync, you can keep data synchronized between your databases in SQL Server and Azure SQL Database to enable hybrid applications.

·      Distributed Applications: In many cases, it is beneficial to separate different workloads across different databases. For example, if you have a large production database, but you also need to run reporting or analytics on this data, it is helpful to have a second database for this additional workload. This approach minimizes the performance impact on your production workload.

Configuration Set up for SQL Data Sync between databases in Azure SQL Database and SQL Server in Azure portal is straight forward, you will just need to create a sync group and select the hub database and member database …refer the link below.

How to Set up SQL Data Sync between databases in Azure SQL Database and SQL Server

Ref-Ms Blog