SQL Data Sync is a powerful service provided by Azure that allows for data synchronization across multiple Azure SQL databases and on-premises SQL Server databases. In this article, we will explore the base concepts of how the SQL Data Sync service works, as well as its requirements and limitations.

How SQL Data Sync Works

SQL Data Sync utilizes a hub-spoke topology to synchronize data. In a sync group, one database is designated as the Hub database, which must be an Azure SQL database. The other databases within the sync group are referred to as member databases. Data synchronization occurs between the Hub and individual member databases only.

The member databases can be Azure SQL databases, on-premises SQL Server databases, or SQL Server instances on Azure virtual machines. The synchronization direction can be bi-directional, where data syncs in both directions, or it can be one-way, either from the Hub to a member database or from a member database to the Hub.

To synchronize data between an on-premises SQL Server and the Azure (Hub) database, the Local sync agent needs to be installed on the local machine. This agent facilitates communication between the Hub and the on-premises SQL Server database.

All member databases, the Hub database, and the sync agent together form a sync group. The sync group should be defined in the same region as the Hub database. Additionally, a separate database, known as the Sync database, is required to store all metadata and logs related to the synchronization process. The Sync database should also be located in the same region as the Sync group.

Data Synchronization Process

When a sync group is created, certain database objects are automatically generated in the production database to track changes for each table being synchronized. These objects include update, insert, and delete triggers. A tracking table is also created to monitor other changes made to the tables. The tracking table has the same primary key as the tracked table and includes columns such as last_change_datetime and sync_row_is_tombstone.

When a new record is inserted into a tracked table, a corresponding record with the same primary key is created in the tracking table to track the changes. Similarly, if a row is deleted from the tracked table, the sync_row_is_tombstone column in the tracking table is updated to indicate that the row has been deleted.

SQL Data Sync also creates stored procedures to select and apply changes, as well as a user-defined table type for bulk changes.

The hub database is synchronized with each member database separately. Changes from the hub database are downloaded to the member databases, while changes from the member databases are uploaded to the hub database. In case of conflicts, there are options to resolve conflicts based on whether the Hub or the Member wins.

Requirements and Limitations

There are certain requirements and limitations to keep in mind when using SQL Data Sync:

• Every table that needs to be synchronized must have a primary key column.
• If there is a need to change the primary key value, the column must be deleted and then recreated with the new primary key value.
• Snapshot isolation needs to be enabled for the database.
• An identity column that is not a primary key cannot be used in tables that need to be synchronized.
• The datetime data type cannot be used as a primary key.
• The maximum number of tables in one sync group is 500.
• The maximum number of columns that one table can have in one sync group is 1000.
• The minimal sync interval is 5 minutes.
• SQL Data Sync does not support Azure Active Directory authentication.

Permissions

When using SQL Data Sync, certain permissions are required for synchronization operations:

• Create table permissions for creating metadata tables and tracking tables.
• Alter table permissions to create triggers on tables that need to be synchronized.
• Create procedure permissions to create the procedures used by SQL Data Sync.
• Select permissions for various tables.
• Insert, Update, and Delete permissions for tables that need to be updated during the synchronization process.
• Execute permissions for the stored procedures used by SQL Data Sync.

By understanding the concepts, requirements, and limitations of SQL Data Sync, you can effectively synchronize data across multiple databases and ensure data consistency and integrity.