In this post I’m going to share some Azure SQL Data Sync best practice and tips. Most of the information comes from the official documentation which is very comprehensive and quite a read. This is more of a summary for future me to avoid reading through all the documentation again. You can find my 2 other posts on Azure SQL Data Sync here part1 and part2.
Initial Synchronisation
It is best to start the sync group with only one database containing data and the others empty. If the other databases contain rows in the synced tables every row will be treated as a potential conflict during the initial sync. This will lead to back and forth traffic and extra processing to determine if there is a conflict. If rows exist with the same primary key it will be a conflict. The conflict policy will then be applied to resolve the conflict. This leads to even more back and forth traffic on every conflict row.
Queuing Up Synchronisations
If you are using automatic sync make sure your scheduled sync time window is greater than the time it takes to complete a sync. For example if a sync takes 30 minutes to complete but you have it set to sync every 10 minutes the syncs will queue up indefinitely.
Stale Databases And Sync Groups
If a database is offline for 45 days it will be considered stale and will be removed from the sync group. When you reconnect this database to the sync group it will be marked as “out of date”. To start syncing again you have to remove it from the sync group and add it again. This will trigger an initial sync which can take very long.
If there is a problem for instance a schema mismatch, preventing the sync group from syncing successfully to all databases for 45 days the sync group will become stale. There is no way to recover from out of date sync groups. You’ll have to delete the sync group and create it again.
Synchronisation Loops
Sync loops are basically a circular reference within the same database or across databases. It usually happens when you have multiple sync groups and they are configured to sync the same rows in a table. Every time a change from one sync group is synced to the hub the other sync group will see it as a modified row. The second sync group will perform a sync on the same row and the first sync group will then see that as a row modification and start a sync of its own. This will go on indefinitely. The same can happen if a single database is registered with more than one agent. The tool is capable of handling databases and tables that are part of multiple sync groups but a row should never take part in more than one sync group. If you have to use multiple sync groups apply row filters to limit the rows for each sync group.
Azure SQL Firewall
It may seem obvious but if you are syncing on-premise databases the sync agents have to connect to the hub and sync meta databases in Azure. You have to configure the Azure SQL firewall on both of them to white list your agent IP addresses.
Troubleshooting
SqlException Error Code: -2146232060 – SqlError Number:3952 Message: Snapshot isolation transaction failed…
For Azure SQL Data Sync to work ‘Allow Snapshot Isolation’ and ‘Read Committed Snapshot’ must be on. In my case ‘Allow Snapshot Isolation’ was off and setting it using ALTER DATABASE statements failed but I was able to modify this setting in the properties window of the database in SQL Server Management Studio.
