SQL Server Hybrid Data Synchronization (Azure & On-Premise)

The estimated reading time for this post is 4 minutes

As you know, the On-premise & Azure data synchronization feature is just released by Microsoft Azure team as preview version; therefore I want to have a try on this feature to check the limitations and advantages over other traditional techniques and technologies regarding data synchronization. This blog will go through Azure SQL Database synchronization setup and on-premise component configurations.

Prior starting the technical section; I would like to explain a little bit about what is Data Sync in Azure ecosystem. Data Sync is the feature to synchronize SQL Server databases either on Azure SQL / VM or On-Premise instance. It allows you to have Bi-Directional synchronization within databases and has Auto-Conflict detection and resolution. Overall it is quite similar to P2P Replication.

Basically Data Sync feature has ‘Data Sync Group’ configuration which groups the databases which are required to be sync together, be aware that currently it supports up to 5 databases either on Azure or On-Premise. Prior to configure Data Sync Group, you are required to understand the following properties:-

  • The Sync Schema describes which data is being synchronized.
  • The Sync Direction can be bi-directional or can flow in only one direction. That is, the Sync Direction can be Hub to Member or Member to Hub, or both.
  • The Sync Interval is how often synchronization occurs.
  • The Conflict Resolution Policy is a group level policy, which can be Hub wins or Member wins.

Data Sync uses a hub and spoke topology to synchronize data. You need to define one of the databases in the group as the Hub Database. The rest of the databases are member databases. Sync occurs only between the Hub and individual members. Remember that Hub Database must be an Azure SQL Database and the member databases can be either Azure SQL Databases, on-premises SQL Server databases, or SQL Server instances on Azure VM.

The Sync Database contains the metadata and log for Data Sync. Therefore Sync Database has to be an Azure SQL Database located in the same region as the Hub Database. The Sync Database is customer created and customer owned.

You may use Data Sync feature if you are required to have:-

  • Hybrid Synchronization Databases
  • Distributed Applications
  • Globally Distributed Applications

So far you read about theoretical and concepts of Data Sync, Lets start the technical section by implementing Hybrid Data Sync between Azure SQL Database and On-Premise SQL Server Database. You are required to connect to your Azure portal and create an Azure SQL Database as Hub Database, and then click on Hub Database, and click on ‘Sync to Other Databases’ as it shows in the following figure:-

So you are required to create ‘Sync Group’, the following figure shows the steps of creating ‘Sync Group’:-

Take note that minimum ‘Sync Frequency’ for Azure SQL and On-Premise database synchronization is Five (5) minutes. Once you create ‘Sync Meta Database’ and ‘Sync Group’, then you are required to add sync members or databases. The following figure shows the configuration windows:-

At first, you need to select the Hub Database and then click on ‘Add an On-Premises Database’ button to this type of members, to accomplish this task; you need to create a new agent for data synchronization. Enter the agent name and click on generate key button. and finally download the Client Sync Agent application.

You might face an error during installation, which mentions that setup is not able to grant ‘Logon as Service’ to windows user. You are required to add the grant the permission manually through Group Policy Editor.

Once the application is installed, you are required to copy and paste the Generated Agent Key into the Client Sync Agent application. As shown in the following figure:-

Add the client-IP into the Azure SQL Database firewall to be able to connect to Azure.

Once the Sync Meta Database is connected to Client Sync Agent application, then it is time to register on-premise database as Sync Member by clicking on Register button and provide the SQL Server connection credentials as following figure:-

The next step is to add the on-premise database as Sync Member and configure the synchronization modes through Azure portal as following:-

Keep in mind that the Port 1433 needs to be accessible as outbound and inbound in on-premise machine. Congradulation, you have setup the Hybrid data synchronization. Now it is time to test the data sync. Lets have a look at the dashboard, it shows that there is no tables are synchronized yet;

Now it is time to test the data sync feature by creating a table at on-premise database; please take note that tables with primary key is eligible to be used by Data Sync feature. The following figure shows that Data Sync feature works fine and performing Hybrid Data Synchronization.

I hope this blog post was informative for you, please share it with others if you think it worth to share. Stay tuned for the up coming blog posts.

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:
FacebookLinkedIn