This is a part of series “Stairway to being an Azure SQL DBA“, where I will be covering all the topics that an Azure SQL DBA should know about.
SQL Data Sync is a service built on Azure SQL Database that lets you synchronize the data you select bi-directionally across multiple databases, both on-premises and in the cloud. – ms-docs
What is SQL Data Sync?
SQL Data Sync is the ‘eventual” syncing of two or more databases together. Eventual because the sync is not real-time or transactional like Transactional Replication but the changes from one database are moved to another database at some point, there won’t be any data loss but it’s just that any change made to one database won’t be implemented on the others the same time.
Components of SQL Data Sync
Hub database: This is the database which acts like repository for all the changes happening in all the spoke databases, and is responsible to sync with each member individually.
Member/Spoke Databases: These are the databases which we want to be in sync with some other database. It could be either Azure SQL Database or a database in an instance of SQL Server
Metadata Database: This database holds every metadata information of SQL Data Sync group and members.
Sync Group: This is the starting point and also the “box” where everything exist. To start with SQL Data Sync you need to create a Sync Group where you define all the settings and configure the Hub, Metadata and Member database.
Conflict Resolution: As all the hubs and spokes are writable, there could be a scenario when the changes are made to one which is conflicting with the data already existing at other end, in such case you have the option to choose which data will be discarded and which will be stored.
The options available are “Hub wins”, which is, during sync if the data in Spoke is conflicting with the one in Hub, Spoke’s data is disregarded and the values from Hub is copied into the Spoke db. The other option is “Member wins” in which data in Hub is overwritten by the one from member (Spoke db)
Automatic Sync: In SQL Data Sync you have the option to either set it for only manual sync or set it for Automatic Sync where you define the frequency when sync automatically triggers. It could be anything from 5 minutes to 30 days.
Sync Agent: When having a member which is a database from a SQL Server Instance you need to install Sync Agent, and connect it with your Sync Group using agent key (you will find this when adding On-Premise database)
This is used for building secure connection between Sync group and the On-Premise database sitting behind the firewall.
Creating SQL Data Sync
For having data sync between different database first we need to setup the group which will hold the whole Data Sync ecosystem.
When creating the New Sync Group you need to provide the name of your group, Name of the Metadata database, the option to either have Automatic Sync or Manual and the last is Conflict Resolution.
If you select Automatic Sync you will also need to provide the frequency in seconds, minutes, hours or days. It could only be in between 5 minute or 30 days.
Once the 1st step is processed you will need to provide the below details in the 2nd tab.
a) The username and password to connect to the Hub database’s SQL Server.
b) Azure Database/On-Premise database details.
For adding an Azure SQL Database, select “Add an Azure Database”, select the database that you to be in sync and choose the direction for data movement which could be, ‘From the Hub”, “To the Hub” or ‘Bi-Directional Sync”. Username and password will be the existing credentials for the server.
If you need to have On-Premise database check this link, the important thing is you will require to install the Sync Agent on the On-Premise server.
In the next tab you will be given the option to select the tables that you want to sync, only the tables with Primary Key can be synced.
Once you are setup, to begin Sync for your database go to the Sync Group and click on “Sync”.
Working of SQL Data Sync
SQL Data Sync uses hub and spoke topology, spokes could be either Azure SQL Database or any SQL Server Instance but Hub can only be Azure SQL Database.
SQL Data Sync uses Triggers and Tables to track the changes happening to the tables part of the Sync. When a Sync is triggered the tables are checked for the data to be moved.
1. To track all the changes happening in the tables three triggers are created one each for Delete, Insert and Update in every table.
2. These trigger insert the values into the table created in the same database in DataSync schema to track the changes these tables are named as DataSync.tablename_dss_tracking.
3. There are some other tables created to keep record of the sync group and tables being synced.
PowerShell commands for SQL Data Sync
I tried configuring SQL Data Sync using just PowerShell but dropped the idea in between as the process which took me 15 minutes using GUI was taking more 1 hour using PowerShell.
The only command that I am going to use is Start-AzSqlSyncGroupSync.
So I’ll prefer configuring SQL Data Sync using GUI and then automating the Sync not based upon some frequency but my usage by using the Start-AzSqlSyncGroupSync cmdlet.
Start-AzSqlSyncGroupSync
[-SyncGroupName] <String>
[-ServerName] <String>
[-DatabaseName] <String>
[-ResourceGroupName] <String>
[-Confirm]
[<CommonParameters>]Conclusion
SQL Data Sync is a powerful feature, to know about, for providing easy sync between the mix of Azure SQL and On-premise databases.
being a dba 