Are you looking to synchronize data between your Azure SQL database and an on-premises SQL Server? Azure Data Sync services can help you achieve this seamlessly. In this article, we will guide you through the process of setting up Azure Data Sync services and creating a data sync group between Azure SQL database and on-premises SQL Server.

Overview of Azure SQL Data Sync Feature

Azure Data Sync is a synchronization service provided by Azure that allows you to synchronize data across multiple SQL databases. It supports bi-directional data synchronization, meaning data can be ingested and egested between Azure SQL databases and on-premises SQL Server.

Key Components of Azure Data Sync

There are several key components involved in setting up Azure Data Sync:

• Hub database: This repository database keeps track of all the changes made on the spoke databases. It is responsible for synchronizing the data between individual members of the sync group.
• Member or Spoke databases: These are the databases that are part of the sync group and participate in the data synchronization between SQL databases. In this case, it would be between the Azure SQL database and the on-premises SQL Server database.
• Metadata database: This database holds metadata details and logs of the Data Sync service. It must be an Azure SQL Database and should exist in the same region as the Hub Database. Only one sync metadata database is allowed per region and each subscription.
• Sync Group: This is the controller of the Azure Data Sync service where you configure everything related to data sync services. It defines the sync schema, sync direction, sync interval, and conflict resolution policy.
• Sync Agent: When using an on-premises SQL Server as a member database, you need to configure and install the Microsoft Data Sync Agent. This agent is responsible for syncing the data between the on-premises database and the Azure SQL database.

Setting Up Data Sync Between Azure SQL and On-Premises SQL Server

Here are the step-by-step instructions to set up data sync between Azure SQL and on-premises SQL Server:

1. Login to the Azure Portal and browse to the Azure SQL Server and Azure SQL database.
2. Under the settings, locate the Data Sync service and click on “Sync to other databases”.
3. Create a sync group in the Azure portal, which bridges the Azure SQL hub database and the on-premises member database.
4. Add sync members, such as an SQL Server database, by selecting “Add an On-Premises Databases”.
5. Download and install the Microsoft Data Sync Agent on the on-premises SQL Server.
6. Register the SQL Server database with the Microsoft SQL Data Sync service using the generated key.
7. Configure the sync group by selecting the tables you want to sync and saving the configuration.
8. Run manual sync or set up automatic sync based on your requirements.

Conclusion

In this article, we have learned how to set up Azure Data Sync services and configure data synchronization between Azure SQL database and on-premises SQL Server. Azure Data Sync provides a seamless solution for synchronizing data across multiple SQL databases. Stay tuned for our upcoming articles where we will discuss more on how to manage data sync and explore common use-case scenarios for Azure Data Sync.