Azure SQL Database Data Sync Group

Introduction

Azure Data Sync is Microsoft service announced on June 18, 2018, and it is used to sync the data from Azure SQL to another Azure SQL Database or from SQL Server on-premises to Azure SQL Bi-Directional this services used for data sync only and you should not use it as disaster recovery or as migration tool or as read-only replica IF you need to know when you should use azure data sync check this Microsoft article, and if you need to know What is the best option for Azure SQL Database Migration, replication and read-only check this link you will find very useful articles

Azure Data Sync Concept

Sync Group: is one of the databases that you want to synchronize
Hub Database: is one the synchronize Databases
Member database: another database in the data sync synchronization is member databases
Synchronization: Happened between Hub Database and member databases this means when you do change on one of the member databases the changes reflected on the other member databases through the Hub database

Sync Group Properties

Sync Schema: Contain the description of the data you need to sync it and you can select multiple tables or you can specify some columns in certain tables
Sync Direction: we have two option Bi-Directional or can be only one direction IF we select the Bi-Directional this means when changes happened on the Hub database it will be reflected on all member database and the reveres is correct
Sync interval: With Sync interval you can decide How and when synchronization occurs.
Conflict Resolution Policy: it is group policy used to fix the issues can be happened in case a conflict happened between Hub database and Member database and we have two options here Hub Wins or Member Wins
Hub Wins: IF you selected this option as Conflict resolution this means the change from Hub database is persisted
Member Wins: IF you selected this option as Conflict resolution this means he changes from the member database is persisted when a conflict arises.

Azure Data Sync Tips and Considerations

Hub Database and Sync Database must be an Azure SQL Database
Hub Database and Sync Database must be hosted on the same Azure region
Sync Database created Automatic when you are configuring Azure data sync
Member database can be SQL Server on-premises, Azure SQL Database or SQL Server database on Azure VM
IF you will use SQL Server on-premises as Member database in Azure data sync you must install Azure Data Sync agent on this on-premises server. Download it from here
Azure data sync not supported till this moment Azure SQL Managed instance IF you need to know more about Managed instance check this article
Azure Data Sync used SQL triggers (insert, Update, Delete) to track the changes and to move it to the member databases and it is created side tables in member databases for change tracking
Any table you need to add it in Azure data sync must have a primary key
Snapshots isolation must be enabled
The table you will add it in Data Sync if it is containing identity column should be primary key but identity column and non-primary key this column cannot be included in Azure data Sync
Columns with user-defined data types are not supported
Automatic Sync can be configured between 5 minutes till 30 days

For more information about Azure Data Sync limitation that you should consider it before implementation Check Microsoft documentation

How Azure Data Sync Work

As I mentioned in Azure tips that Azure data Sync depend on Triggers to tracking the data and the Hub will sync the data to the members For more information check this Link

Demo introduction

Let us assume that we have Azure SQL Single Database named “Member01” and this DB hosted on logical Server “memberlogicaldbserver.database.windows.net” and inside this DB You have some tables and you need to build Sync between this DB and another Azure Single DB named “Member02” hosted on logical Server “member02logicaldbserver.database.windows.net”, So whatever any update, insert, delete happened on certain tables or certain columns in Member01 should be reflected on Member02 database and vice versa

What we Should do achieve these requirements

IF you are in the demo we should build two SQL Server Azure Single Databases Member01 and Member02 and the schema of the table should be created on both databases
We need to create a Hub Database that will be used to move the changes between the two members databases
We need to create Sync Database it will contain the Sync Configuration, history, actions this database created automatically when we are creating the Database Sync Group Services, add in your note sync database should be hosted on the same region of the Hub database
Based on this Configuration we will have 3 Azure Single logical Server and 4 Databases

Memberdb01, Member02, and Hubdb01 all of them are normal Azure Single Database deployment for more information about how to provision Azure Single Database check this article
Syncdb01 will be created through the upcoming configuration we will do it to create Database Sync Group
Memberdb01 and member02 databases you can host them in one logical server no problem for example “memberlogicaldbserver.database.windows.net”
Hubdb01 and Syncdb01 can be hosted on separate Azure Logical Server only the requirements are both databases should be hosted in the same region.
You Should Enable the Server Firewall by adding local machine IP on all Azure SQL Database to be able to connect to them through the SQL Server Management Studio
Allow Azure services and resources to access this server from the Server Firewall Configuration to allow the Hub and member databases to communicate to sync the data.

As we can see we have 3 Logical Server each server contains Azure Single Database. On the next step, we will create Database Sync Group and during the configuration process we will create the Sync database on HublogicalServer

Create Database Sync Group between two Azure SQL Single Database

Check below GIF File to know How we can create Database Sync Group and add the member databases on the syn group then sync the changes between the members and Hub.

create-database-sync-group-between-two-azure-sql-single-database Download