Using Azure SQL Data Sync to Sync On-Premise and SQL Azure Database

Suppose we have an Azure SQL DB named MyDB which we would like to sync with an On-Prem DB.

Here we’d be using Azure SQL Data Sync. The sync can be bi-directional, one way from Azure to On-Prem and On-Prem to Azure or Azure to Azure DB(s) as well.

In Azure Portal, select the Azure SQL DB and select Sync to other databases and click on Click on New Sync Group.

Specify values for the Data Sync Group.

We have selected existing database MyDB, selected Automatic Sync – On. The Sync frequency can be defined in seconds, minutes, hours and days – It needs to be between 5 minutes to 30 days.

Conflict Resolution can be Member Win or Hub Win. Hub here specifies the Azure SQL DB that we have selected here.

Next click on Add On-Premise database to specify the database to which we want to sync.

Here we need to specify the Data Client Sync Agent, which requires downloading and configuring the Sync Agent first.

Before installing the Data Sync Agent we need to install the following prerequisites

Make sure to download the X86 one.

Run the Data Sync Agent installer, specify the Account that can connect to the On Prem DB and finish the installation.

Back in Azure inside Sync group, click on Create and generate a key and copy the key.

Run the Data Sync Agent and click on Submit Agent Key and specify Agent key along with the credentials to access the Azure SQL DB.

Next click on Register button to register the On-Prem DB as shown below.

Once registered we can select the On-Prem SQL DB registered along with the Sync Directions.

As the last step, select Configure Sync Group and select the Hub Database and the table and column that we want to sync along any of the table of On-Prem DB.

The corresponding tables will be created in the database. For e.g. if we have selected Person table in our Hub Azure SQL Database, it will be created in the our On-Premise Database.

This completes the configuration.

We’d see the Sync running every 5 minutes as we had configured. Update\Insert any data on Azure SQL DB or On-Prem SQL DB, we’d see the same reflecting back in other DB in case of Bi-Directional Sync.