Synchronizing Hybrid Cloud Data with Azure SQL Data Sync

SQL Server includes a number hybrid cloud technologies including Backup to URL that lets you backup on-premise SQL Server databases to Azure and the Stretch Database feature that enables you to seamlessly extend a local database to the cloud. However, what if you want to synchronize the contents of two databases that are on separate servers where one is in the cloud and the other one is local? SQL Server replication is able to replicate database changes but it is not designed to work with the cloud. To address this problem of hybrid cloud database synchronization Microsoft provides their Azure SQL Data Sync service.

Azure SQL Data Sync is useful where data needs to be unidirectionally or bidirectionally synchronized across several Azure SQL Databases or SQL Server databases. It enables hybrid SQL deployment where you can split different workloads across multiple servers. For example, if you have a large production database in the cloud you can use Azure SQL Data Sync to copy the data to another SQL Server instance where you might run reporting or analytics workloads on the data. You can also use it to facilitate global data access where you might have multiple databases in different regions around the world synchronized with each other enabling each region to use its own local data minimizing network latency and possible connection failures.

How SQL Azure Data Sync Works

The core of Azure SQL Data Sync is the Sync Group. A Sync Group is essentially a group of databases that you want to synchronize with another SQL Server instance. Azure SQL Data Sync tracks changes using insert, update, and delete triggers. The changes are recorded in a table in the user database. Azure SQL Data Sync uses a hub and spoke model to synchronize data between different servers. The hub and spoke model consists of the following elements:

• Hub Database – The Hub syncs with each member in the group individually. Changes from the Hub are downloaded to each member and changes from each Member are uploaded to the Hub. The Hub must be an Azure SQL Database.
• Member databases – The Member databases can be Azure SQL Databases, on-premises SQL Server instances, or SQL Server instances on Azure VMs. On-premises database require a local sync agent.
• Sync Database – The Sync database contains the metadata and log that controls and tracks the synchronization process. The Sync Database must be an Azure SQL Database located in the same Azure region as the Hub Database.

Azure SQL Data sync requires that each table must have a primary key and that snapshot isolation must be enabled. Data is synchronized according to the interval that you configure. One of the thorny issues with bidirectional data synchronization is what to do when you have data conflicts. Azure SQL Data Sync offer two options for conflict resolution: Hub wins or Member wins. You can configure Azure SQL Data Sync using either the Azure Portal or PowerShell.

Although it is a service, Microsoft doesn’t charge for the Azure SQL Data Sync service. However, there are charges for data storage and data transfer. You can learn more about how Azure SQL Data Sync can help support your hybrid cloud scenarios at Sync data across multiple cloud and on-premises databases with SQL Data Sync.