In a recent article published on this forum, we described main characteristics of Azure SQL Data Sync. This service, which has been lingering in the Preview mode since its introduction seven years ago, had been until recently one of few offerings that still required the use of the Azure classic portal. Fortunately, this is no longer the case and you can finally access it by using the current Azure portal (with the Data Sync classic portal interface retired on July 1, 2017). In this article, we will step through the process of implementing Azure SQL Data Sync with the most common configuration settings by using this interface.
For the sake of clarity, let’s review the basic characteristics of Azure SQL Data Sync. This Azure-resident service, based on Microsoft Sync Framework specifications, provides synchronization functionality across multiple database instances, with support for both SQL Server (residing on-premises or in the cloud) and Azure SQL Database. All databases within the scope of synchronization are members of the same Sync Group, with a SQL Database instance serving the role of the hub and all the remaining ones (referred to as Reference databases) forming spokes in the synchronization topology. The hub also provides the Data Sync Service responsible for coordinating synchronization activities. Each Sync Group is associated with a specific Azure region, which determines the location of the hub and the SQL Data Sync database, containing sync metadata and handling sync activities.
To start our sample implementation, we will provision two Azure SQL Databases via the Azure portal. The first of them will be serving as the hub of our sync group. The second one will be operating as a sync group member. For instructions describing this procedure, refer to the Azure online documentation.
Once the databases have been provisioned, navigate through the Azure portal interface to the blade of the hub database and click Sync to other databases. This will display another blade with the option labeled + New Sync Group in the toolbar. Clicking on it will open another blade, guiding you through the creation of a new sync group. As part of this process, you will need to specify the following settings (divided into three categories):
- Creating sync group
- Sync Group Name – this is simply a label designating the sync group within your subscription.
- Sync Metadata Database – this can be an existing database, although Microsoft recommends creating a new one. If you follow that recommendation, you will need to configure the relevant database settings, including its name, resource group, target server, pricing tier, and collation. If you decide to create a new server, keep in mind that the sync metadada database must be located in the same region as the hub.
- Automatic Sync – you can either enable this setting or keep it disabled (default) and trigger synchronization on demand. If you decide to turn it on, you will need to specify Sync frequency, which can range between 5 minutes and 30 days.
- Conflict resolution – you have the choice of using either the Hub win or the Member win approach.
- Adding sync members
- Hub Database credentials – these designate the existing admin login on the server hosting the hub database.
- Member Database – this can be either an Azure SQL Database (referenced in the interface as simply Azure Database or a database hosted on a SQL Server instance running either in an on-premises location or in a virtual machine hosted in Azure or, for that matter, any other cloud provider. For each Azure SQL Database you want to include in the sync group, you will need to assign the Sync Member Name and associate it with an existing database. As part of this configuration step, you also have to specify Sync Directions, which can take the value of Bi-directional Sync, To the Hub or From the Hub. You will also need to specify the username and the corresponding password that can be used to connect to the member database. If you want to include a database on a SQL Server instance, you will need to first install a Sync Agent Gateway on the system hosting that instance. You have the option of carrying out that installation directly from the portal once you click the Add an On-Premises Database entry on the Select sync members blade. This will open the Configure On-Premises and the Select Sync Agent blades, where you will find the Client Sync Agent Download link and where you will be able to assign the Agent Name and Generate an agent key.
- Configure sync group
- Tables – this blade allows you to select tables that exist in either the Hub database or any of the members databases and designate tables that you intend to sync. Note that tables without the primary key are not supported. For each table, you can select individual fields you intend to sync.
Once you completed this configuration, the newly created sync group should appear on the Sync to other databases blade. Clicking its name will open the corresponding blade, from which you can perform such actions as enabling or disabling automatic sync, triggering on-demand synchronization, or viewing synchronization logs.
This concludes our walkthrough illustrating the procedure of implementing a sample Azure SQL Data Sync via the Azure portal. In upcoming articles, we will look into more advanced scenarios as well as cover some of the most common synchronization troubleshooting techniques.
See all articles by Marcin Policht
