In this post I’m going to take a quick look at Azure SQL Data Sync. What is does, how it works, scenarios where you would use it and recent updates.
What Does It Do
As the name implies you use Azure SQL Data Sync to sync data between MS SQL databases via Azure. The databases can be on-premise MS SQL or Azure SQL databases. Take note that this is data only, not schema changes or other database objects, just data in tables. The data sync can be configured to run on a schedule with the smallest recurrence of 5 minutes or it can be a manual sync. Individual tables and columns can be selected for sync.
How Does It Do It
Azure SQL Data Sync uses a hub and spoke model. The hub in this case is an Azure SQL database, the spokes or member databases are connected to the hub. Changes will flow from a member database to the hub and then to other member databases. Azure SQL databases can connect directly to the hub but on-premise databases have to use Sync Agents which you install on-premise. When you connect a member database you can choose the sync direction:
- bi-directional
- to the hub
- from the hub
To handle conflicts you select a conflict policy, the options are, hub wins or client wins.
Usage Scenarios
By choosing different sync directions you can utilise Azure SQL Data Sync for a variety of scenarios:
- Bi-directional sync for all members can be used to keep all connected databases and the hub in sync
- Syncing to the hub for all members can be used to collect all records from member database into the hub. For example creating a central aggregated database for reporting
- Syncing from the hub for all members can be used to create a single source of truth in the hub. Member databases will have a local copy of the data
- Setting one member to sync to the hub and another to sync from the hub will keep the read only member database in sync with the transactional member database. This is handy when moving a database that is constantly being updated into Azure SQL. You can continue to use the on-prem database and make the switch with very little down time and without restoring from a backup or bulk copy procedure. UPDATE: Transactional Replication is now available for Azure SQL and is the preferred method to sync database changes in a migration scenario link.
It is important to remember Azure SQL Data Sync will sync only data, schema changes are not handled automatically by the tool. In a future post I will dig deeper into handling schema changes.
Azure SQL Data Sync Update
Previously Azure SQL Data Sync was only available in the old Azure portal. After the update it is now available in the new Azure portal. It also received PowerShell cmdlets and Rest API enhancements, previously everything had to be done manually in the UI. The hub used to have a shared database maintained by the Azure SQL Data Sync service. Users will now have to provision their own database in their own subscription and use it as the hub.
Resources
The Azure SQL Data Sync team did a very good job providing comprehensive documentation which can be downloaded here link.
Thank you for sharing this information. I am relatively new to Azure database sync and I was confused as to why schema changes at the member side would result in sync errors on the Azure side.
Now I understand that the schema change has to be manually implemented at each end point. A pain, to be honest, but it works and that’s the main thing!