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

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

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

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchSQLServer.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchSQLServer.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

Premium Access

Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

This was first published in February 2012

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.

More on SQL Azure and SQL Data Sync

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

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.

Note

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.

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top