Have you ever wondered how to sync data between multiple Sql server instances? There are many ways to sync the data, such as replication, log shipping, etc. However, I personally like an Azure SQL Database feature called SQL Data sync, which synchronizes the data across multiple SQL & Azure SQL database instances. It is a cloud based data sync service built on Microsoft Sync Framework. It does it all by creating a sync agent on Azure with a sync group where databases and tables can be provided.
The part I like most is, it is not only snycing two Azure SQL server instances but it also can sync the on premises SQL Servers; in other words, it has single as well as bi directional data sync capabilities that makes it super cool.
Bi- directional means the changes made on either on SQL Server on premises or Windows Azure SQL database are automatically synced back and forth.
But how does it work?
Let us say I have two databases, one is the Azure DB & another is an on premise SQL Server DB.
So to sync the on premises DB & our DB hosted at Azure, we will be using SQL Data sync, so it will be something like below,
The HUB database can keep the Cloud database as well as on premises database in sync, whereas sync db has a metadata database that keeps track of all synchronizations.
Things to remember,
- Each table must have a primary key
- Name of objects (database, tables, columns) cannot contain a period (.), “[“, “]” & brackets.
Scenarios in which SQL Data Sync synchronizes the data,
- Cloud to cloud; i.e., Windows Azure SQL db to Windows azure SQL db.
- SQL Server on premises to cloud; i.e., Windows Azure SQL Database.
- Cloud (Windows Azure SQL Database) to SQL Server on premises to cloud.