Prashanth Jayaram
Create Data sync group

How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server

January 20, 2021 by

In this article, you learn how to set up Azure Data Sync services. In addition, you will also learn how to create and set up a data sync group between Azure SQL database and on-premises SQL Server.

In this article, you will see:

  • Overview of Azure SQL Data Sync feature
  • Discuss key components
  • Comparison between Azure SQL Data sync with the other Azure Data option
  • Setup Azure SQL Data Sync
  • More…

Azure Data Sync

Azure Data Sync —a synchronization service set up on an Azure SQL Database. This service synchronizes the data across multiple SQL databases. You can set up bi-directional data synchronization where data ingest and egest process happens between the SQL databases—It can be between Azure SQL database and on-premises and/or within the cloud Azure SQL database. At this moment, the only limitation is that it will not support Azure SQL Managed Instance.

Components of Azure Data Sync

Hub database: This repository database keeps track of all the changes made on the spoke databases. The responsibility of the hub database is to synchronize the data between individual members of the sync group.

Member or Spoke databases: They are the ones that are part of the sync group where member database should take part in the data synchronization between SQL databases. In this case, it’s between the Azure SQL database and the database in on-premises SQL Server.

Metadata database: This is a critical piece of the configuration as it holds metadata details and logs of the Data Sync service.

  • The Metadata database must be an Azure SQL Database it should exist in the same region as that of Hub Database
  • They allow only one sync metadata database per region and each subscription
  • To clean up the metadata database, you must delete the sync group and agents

Sync Group: This is the controller of the Azure Data Sync service where you configure everything related to data sync services. To configure the Data Sync service, you need to configure the Sync Group. This process intends to define all the configuration and hub database settings, configure metadata database and member database.

The properties of a sync group:

  • Sync Schema – Describes the schema considered for the data synchronization process
  • Sync Direction – It defines the data traversal direction—bi-directional or uni-direction. The direction is from Hub -> Member, Member ->Hub, or both
  • Sync Interval – the frequency of the data sync process to occur
  • Conflict Resolution Policy – It’s a sync group policy that describes who can be the winner in case of a conflict. We can define it as Hub wins or Member wins. During the data synchronization process, we can see several data sync conflicts because all the hubs and member databases are writable. If you’ve worked extensively in SQL Server merge replication, this is no different. You can define the policy to define which data to accept and what data to discard. If the option you have chosen is “Hub wins” then during the data sync process, if there is a conflict, then member data is discarded. If you opt for the other option “Member wins” then hub data is discarded

Automatic Sync: The Data Sync is of two types. The first one manual sync and the second is automatic sync, during the configuration of the Azure Data Sync group you can select the modes based on the requirement.

Sync Agent: When you choose an on-premises SQL server as a member database then you need to configure and install Microsoft Data Sync Agent. After the installation, you need to register the database using the key and metadata database credentials to sync the agent.

How to set up Data Sync between Azure SQL and On-Premises SQL Server

In this section, you will see step by step details to carry out to set up data sync between Azure SQL and on-premises SQL Server.

  • Login to the Azure Portal
  • Browse the Azure SQL Server and Azure SQL database
  • On the left blade, under the settings, locate the Data Sync service. Click Sync to other databases

Sync to other database data sync service option

Next, create a sync group in the Azure portal. The sync group bridges the Azure SQL hub database and on-premise member database.

Now, Create a sync group as highlighted in the below image

Create Data sync group

Type in the values as per the request to create a data sync group.

  • Note: If you already configured the sync group, then the option “sync metadata database” field will be greyed out. Azure data sync will only allow having one Data Sync for any Azure region. Let us consider an example, the server “Azuredb1” hosted in Region -South Central US and you configure the Azure Data Sync. For the first time, it is allowed to configure metadata database “azuredatasync” if at all there is no prior azure data sync configuration

Now, if you try to configure Azure Data Sync service for another server “Azuredb2” which is located in South Central US then you will have default azuredatasync on sqldb. By default, the option is greyed out. In case if you want to create an Azure Data sync service, then you need to delete the underlying configuration and sync the metadata database azuredb1 completely or start building the solution in the new region.

How to create data sync group

Next step is to add sync members. After the deployment of mydatadyncdemo sync group, proceed further to configure sync members.

Select the sync members

In this case, we are adding SQL Server. To add an SQL Server database select Add an On-Premises Databases

  • Note: If you are using a sync group with a combination of Azure SQL and an on-premises instance then you must complete the pre-requisite of install and configure a local sync agent. To download the data sync agent follow the Azure SQL Data Sync Agent link

Configure the new agent and generate the key. Use this key to authenticate on-premises sql server.

Create Sync Agent - On-Premise

Login to the on-premises server and install the Microsoft Data Sync Agent

install SQL Data Sync Agent 2.0

It is straightforward. You just need to follow the instruction to complete the setup.

Installation completion status

After the installation, test the Sync Metadata Database Configuration using the key. In addition, you need to type in the credentials of the metadata database.

Sync metadata database configuration connection test

Next, register the SQL Server database with Microsoft SQL Data Sync 2.0 service. After the registration, Click on the Save button.

SQL Server configuration - Azure Data Sync

Now, you can see that the registered database is listed in the configuration pane.

Register SQL Server database to SQL Data sync service

Back to the portal and select the on-premise member database. You need to type in the Sync Member Name, select the database and choose the Sync Directions.

Configure onpremise database as a  data sync member

The final step is to configure the sync group. Choose the table that you want to sync and click save. Data Sync page opens. Select Sync.

Create the schema

To run manual sync, locate the database sync group in the Azure portal and click Sync. Verify the logs to see more details about the data sync event.

Sync the schema

Conclusion

So far, we have learned how to set up the Azure SQL Data Sync service and how to configure sync service between Azure SQL database and On-premise SQL Server instance. I will discuss more on how to manage the data sync in the upcoming article. In addition, you need to understand the common scenarios where data sync can be helpful.

Azure Data Sync use-case scenario along with other Azure data options.

Use-Case

Azure Data Sync

Other Azure Data option

The application that requires a Hybrid data sync technique

Yes

No

Globally distributed application support

Yes, It is easy to manage and synchronize the data across the regions.

Azure geo-replication can be set up to support globally distributed applications but this is uni-direction and it supports secondary read-only replicas to manage workloads.

Disaster Recovery(DR) strategy

No

Azure geo-redundant backups

Setup Read Scale

Not an ideal candidate for read scalability

By default, the read scale-out feature available in the Azure SQL database, elastic pool database, and managed instances that are configured with Premium and Business Critical service tiers. The primary intent is to offload the workload on the primary read-write-replica. Based on the connection string value of the ApplicationIntent—”readwrite” or “readonly”, the queries are redirected for the read-only or read-write workloads of the available replicas. With the read-scale out feature, the applications can get maximum performance benefits using the additional compute instance at no extra cost.

ETL load (OLTP to OLAP)

No

Azure Data Factory or SQL Server Integration Services

Database migration from On-Premise SQL Server to Azure SQL Database

No

Database Migration Assistance(DMA) or Azure Database Migration Service(DMS), Import or export, Transactional Replication are a good option

Table of contents

IT Cloud Automation using PowerShell
Deep dive into IT Cloud Automation using PowerShell
Getting started with Azure Automation
Getting started with Azure SQL Database using Azure CLI
Provisioning SQL Server 2019 Azure Container Instance using PowerShell
Four different methods to copy your Azure SQL database
Azure SQL Database vs SQL Server on Azure VMs
How to provision Azure SQL Database using Ansible
Quick start guide to Geo-restore in Azure SQL Database
Different ways to login to Azure automation using PowerShell
How to perform Azure SQL database Import/Export operations using PowerShell
How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server

Prashanth Jayaram
Azure, SQL Azure

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB. View all posts by Prashanth Jayaram

9,646 Views