This content is part of the Essential Guide: Evolution of Windows Azure SQL Database
Problem solve Get help with specific problems with your technologies, process and projects.

Keep cloud, on-premises data square with SQL Azure Data Sync

Learn how to use SQL Azure Data Sync to keep SQL Server and SQL Azure databases across dispersed geographic locations using a simple point-and-click interface.

SQL Azure Data Sync lets you synchronize SQL Server and SQL Azure databases across dispersed geographic locations. Built on the Microsoft Sync Framework, it’s easy to set up and maintain. You perform all management tasks through the Windows Azure Management portal, where you’ll find a simple point-and-click interface that lets you quickly configure synchronization jobs and monitor the process.

SQL Azure Data Sync components

SQL Azure Data Sync is based on a hub-spoke topology in which the hub is a SQL Azure database and the spokes are on-premises SQL Server databases or cloud-based SQL Azure databases. The SQL Azure databases can be in the same data center as the hub or in different ones. Figure 1 provides an overview of the hub-spoke topology and the components that make up the SQL Azure Data Sync environment.

Figure 1. SQL Azure Data Sync extends synchronization beyond the cloud.

Access the SQL Azure Data Sync service through the Windows Azure Management Portal in your browser. This is where you set up synchronization jobs, but it’s also the hub database at the heart of the data sync topology. Because the hub is a SQL Azure database, it must reside on a SQL Azure server. That server can be in any Windows Azure data center. In this case, I’ve used the SQL Azure North Central U.S. data center as an example.

All other databases that participate in the synchronization are member databases, whether they’re SQL Server or SQL Azure databases. For on-premises SQL Server databases, you must also install the SQL Azure Client Sync Agent on the SQL Server computer. The agent facilitates communication between the SQL Server member database and the SQL Azure hub database.

You can install the client application as part of the synchronization setup process or before implementing SQL Azure Data Sync. This is the only installation step you need to take; because SQL Azure Data Sync is part of Windows Azure, all other necessary steps are taken care of. Simply log on to the Windows Azure Management Portal and configure your jobs.

SQL Azure Data Sync configuration options

When setting up synchronization, you have several options for configuring the SQL Azure Data Sync service. One of these options (and the first step you must take) is to specify which SQL Azure server should be provisioned as the Data Sync server. This is the SQL Azure server that hosts the database that will be provisioned as the hub. In Figure 1, the Data Sync server is the SQL Azure server in the North Central U.S. data center. A Data Sync server (and by extension the associated hub SQL Azure server) is always associated with a specific data center.

After you provision your Data Sync server, set up a sync group -- a named collection of the SQL Server and SQL Azure databases that will participate in the synchronization. The group must include one SQL Azure database to serve as the hub and one or more SQL Azure or SQL Server databases to serve as the members.


Not surprisingly, because any synchronization operation involves at least two databases, a sync group must include at least two databases.

When you add a member to a sync group, specify the direction of your synchronization. You have three options:

  1. Sync in both directions: Changes are synchronized in both directions between the hub and the member database.
  2. Sync from the hub: Changes are synchronized in only one direction -- from the hub to the member database.
  3. Sync to the hub: Changes are synchronized in only one direction -- from the member database to the hub.

Another option you must configure when setting up synchronization is the sync schedule, which lets you specify when you want your databases to be synchronized. You must also select a conflict resolution option that determines the resolution policy to apply when conflicts occur, which happens when the same data is changed in multiple databases between synchronizations. There are two options for how conflicts should be handled:

  1. The hub wins: The first change written to the hub during synchronization is used and that change is propagated to the other member databases.
  2. The client wins: The last change written to the hub during synchronization is used and that change is propagated to the other member databases.

When setting up a sync job, you must also configure the sync data set, which defines the data that will be synchronized. You can choose which tables to include and which columns in those tables. You can also create filters that limit the rows of data to be synced. Once you’ve set up the data set, you’re ready to deploy your changes.

Deploying your sync job is an automated process that only requires you to click the Deploy button. The service will then provision each database to prepare for the synchronization process.

Making synchronization work

One of your biggest decisions when setting up a SQL Azure Data Sync job is determining which data center should host your hub database. Pick one that minimizes data latency during the synchronization process. For example, if most of your on-premises databases are in Europe, that's where you should put your hub.

The rest of the setup process is straightforward: Choose your configuration options and provision the databases. It’s that simple.

About the author:
Robert Sheldon is a technical consultant and the author of numerous books, articles and training materials related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. Check out his blog, Slipstream.

Next Steps

Zoom in on SQL Azure data sync

SQL Azure Data Sync is unveiled at PASS 2011

SQL Server Denali vs. the Azure cloud: Which one got more attention at Tech Ed?

Get started with Microsoft SQL Azure database

Dig Deeper on SQL Server Migration Strategies and Planning

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.