Hello everybody, this is our first step-by-step post about how to configure SQL Azure Data Sync in order to synchronize data between a SQL Server on-premise and an Azure SQL database.
SQL Azure Data Sync enables creating and scheduling regular synchronizations between SQL Azure and either SQL Server (on-premise) or other SQL Azure databases. Is a cloud-based data synchronization service built on the Microsoft Sync Framework technologies. It provides bi-directional data synchronization and data management capabilities allowing data to be easily shared across SQL Azure databases within multiple data centers.
The current release is SQL Azure Data Sync Preview and is not intended to use in production environments.
We’ll configure a very simple example: we’re going to synchronize the local database AzureDataSync_OnPremises, which contains only one table (the Productions.Products table from AdventureWorks)
with a SQL Azure database (AzureDataSync_Cloud).
The AzureDataSync_Cloud database already exists on SQL Azure, but does not contains any object.
Step by Step:
Let’s go on with our step-by-step procedure. First of all we need to provision the Data Sync Server. You can have only one Data Sync Server per subscription. On the Windows Azure Platform Management Portal you can provision the server clicking on Provision Data Sync Preview Server:
You have to provide the region and the subscription under which the server will be created. It’s recommended to setup the sync server on a region close to the majority of your databases to reduce latency. Currently (in the preview version) the available regions are:
- North Central US
- West Europe
Once the server is provisioned we’ll setup a new data synchronization. In this sample we'll setup a sync between a on-premise SQL Server and a SQL Azure database through the Windows Azure Platform Management Portal (note the highlighted option):
The portal launches a wizard with several steps. In the first step you have to provide a unique name to the sync group:
A sync group is a collection of SQL Azure and SQL Server databases that are configured for mutual synchronization by the SQL Azure Data Sync service. A sync group is comprised of a hub database and one or more member databases. The hub database must be a SQL Azure database.
Second step: since we are setting up a on-premise and Azure sync, in this step we must provide the SQL Server database by clicking on the database icon:
In this dialog we’ll check the option Add a new SQL Server database to the sync group and set the Sync Direction to Bi-Directional:
The available options for the sync direction are:
- Sync to the Hub
- Sync from the Hub
Which are self describing.
By clicking on the Next button a dialog is shown where we’ll select the Install a new Agent option:
The SQL Azure Client Sync Agent sits between the SQL Server database and the SQL Azure hub database. As part of the SQL Azure Data Sync service the Client Sync Agent enables bi-directional HTTPS based communication between the on-premise SQL Server database and the SQL Azure hub database.
After clicking on the Next button we’ll see a dialog where we can download the Client Sync Agent setup package, which we must install on a server with connectivity to the desired SQL Server database. The setup process for the sync agent is very straightforward. In this same dialog we must provide a unique agent name and generate an agent key, necessary to configure the installed agent.
The SQL Azure Data Sync supports the following:
- SQL Server 2005 SP2 or later.
It works best with SQL Server 2008 R2 and later as support for SQL Azure was added to SQL Server Management Studio in SQL Server 2008 R2.
- Windows Server 2008 or later, Windows Vista, and Windows 7 operating systems.
- x86 and x64 platforms.
Be sure that service account under which the sync agent is running has permissions to connect to the on-premise database you want to register.
Once installed the agent and configured with the provided agent key we must register the on-premise database by clicking on the Register agent ribbon icon:
A dialog is shown where we can enter the connection options for the on-premise SQL database. We’re going to select the local server and the AzureDataSync_OnPremises database:
In this sample I’m using Windows authentication since I provided an account with enough access privileges to the database on the agent setup. In other case, if available by configuration, we can use SQL authentication in order to access to the database.
Back to the Windows Azure Platform Management Portal, and with our client correctly configured, we can proceed to the next step where we’ll select the database from the agent (after refreshing the available databases list by clicking on the Get Database List button):
The third step in the wizard is the selection of the Sync Hub database:
The sync hub database is where the member databases (all the other databases on the sync group) uploads and downloads the changes.
After clicking on the database icon a dialog is shown where we must provide the server name, the database name (it must exists previously on the SQL Azure server), and the credentials. In our case, we’ll choose the AzureDataSync_Cloud database:
In the fourth step we can configure a sync schedule and how the sync conflicts are resolved:
A sync conflict occurs when changes are made to the same piece of data in two or more databases between synchronizations. You can choose between the following options for the conflict resolution:
- Client Wins
- Hub Wins
In this sample I didn’t configure a sync schedule so the synchronization occurs on-demand.
In the fifth step we’ll define the dataset which will be synchronized by clicking on the Edit Dataset button:
Here we can select the tables and fields to sync and apply filters at row level:
In this example we’re going to select the unique existing table: Production.Product.
We’re almost done! The sixth step is the deployment of the sync group to the sync server by clicking on the Deploy button:
We can manually start a synchronization clicking on the Sync Now ribbon button:
Once the sync is complete we can see the results in the hub database:
In addition to the Products table, we can see that other tables were created. These tables, with DataSync schema, are used by the sync service for change tracking and were created on the on-premise SQL Server too.
In order to further test our newly configured service, we can make some change in the cloud database executing something like:
UPDATE [Production].[Product] SET [Name] = [Name] + ' - Modified on cloud' WHERE [ProductID] = 1
After manually synchronizing the sync group, we can check that the changes were applied to the on-premise database:
Voilà! The changes were propagated from SQL Azure to our local SQL Server.