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
- Azure subscription and Azure SQL database.
- Below is the link for creating the Azure free trial account.
- 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.
- In the Network connectivity tab, enable the Public endpoint option and add the current client IP address as yes.
In the Additional Settings page, change ‘Use existing data’ to Sample as shown below.
- Once SQL database is created login into the SQL database by giving Username and Password, created in Step 2.
- Create one Sample table in the SQL database.
- Navigate to ‘Sync to other databases‘ in the created SQL database.
- 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.
- 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.
- 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.
- Next, Navigate to Add an On-Premises database option.
- 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.
- 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.
- 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.
- The Gateway created earlier will appear as shown below.
- Next, select the On-premises SQL database.
- 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.
- Select the Sync Agent Gateway created earlier and the On-premises SQL database as shown.
- 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.
- 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.