Purpose of the article: It helps to understand how data synchronization is done between the On-premises SQL Server and Cloud SQL Server.

Intended Audience: Microsoft Azure Developers

Tools and Technology: Microsoft Azure, SQL Server

Keywords: Azure SQL database, Sync Agent Gateway, Sync Group

Prerequisites

• Azure subscription and Azure SQL database.
• Below is the link for creating the Azure free trial account.

Link: https://azure.microsoft.com/en-us/free/

Step 1:

• Log in to the Azure Account.
• Go to All Services and search for SQL DATABASES service.

Step 2: Steps to create an Azure SQL database are

Create an Azure SQL database

• If we do not have a Server earlier, create a new one as shown below.
• Need to provide Resource group name, Database name, Server name.

Step 3:

• In the Network connectivity tab, enable the Public endpoint option and add the current client IP address as yes.

Step 4:

In the Additional Settings page, change ‘Use existing data’ to Sample as shown below.

Step 5:

• Once SQL database is created login into the SQL database by giving Username and Password, created in Step 2.

Step 6:

• Create one Sample table in the SQL database.

Step 7:

• Navigate to ‘Sync to other databases‘ in the created SQL database.

Step 8:

• On the Sync to other databases page, select New Sync Group. The New sync group page opens with Create sync group option.

• Provide the Sync Group name, Automatic Sync is on, and Sync Frequency as shown below.

Step 9:

• After creating a sync group, go to step 2 Add Sync members.
• Provide the Username and Password of the SQL database created in earlier steps.

Step 10:

• Next, Navigate to Add an Azure Database option.
• Fill in all the columns like Azure SQL Server name, Azure SQL database, Username, password as shown below.

Step 11:

• Next, Navigate to Add an On-Premises database option.

Step 12:

• Then in Configure On-premises, select Sync Agent Gateway. Prior to that, Sync Agent Gateway must be installed in the system.
• To do the same, Click on the download Client Sync Agent option.

Step 13:

• Open the downloaded file and copy the agent key generated in the above step and paste it into the appeared dialogue box.

• Give Username and password of SQL database which we had created in the starting process.

Step 14:

• After Giving the Agent key, Username, password of the Azure SQL database needs to test the connection.
• After getting Test Connection as successful, the below dialogue box will appear.

Step 15:

• The Gateway created earlier will appear as shown below.
• Next, select the On-premises SQL database.

Step 16:

• Open Microsoft SQL Data Sync and click on Submit Agent Key Configuration.
• Click on any existing database in On-premises SQL Server and click on Register.

Step 17:

• Select the Sync Agent Gateway created earlier and the On-premises SQL database as shown.

Step 18:

• Navigate to the tables option in Sync Group and select the database, there we can see the table, which we created in the Azure SQL database.

Step 19:

• Check if the data in On-premises SQL Server matches with the data with table data which we created in the Azure SQL database.

This way, we can be assured that the data is synchronized between the two databases.