Synchronizing a Local Database with the Cloud using SQL Azure Sync Framework

I’ve been looking at different aspects of Windows Azure over the past few months, and have been having a lot of fun getting a grip on Cloud Computing and Microsoft’s Cloud Computing Platform. One of my past posts gives a step-by-step tutorial on how to connect to SQL Azure using Microsoft SQL Server 2008 R2 (November CTP). If you haven’t already done so I would suggest reading Making Data Rain Down from the Cloud with SQL Azure to follow along with this tutorial.

I received an email a few days ago from Apress Books suggesting me the book to the left due to my previous purchase of Introducing Windows Azure.  Both books cover the Windows Azure Storage Services API, and SQL Azure. The Introducing Windows Azure book covers Azure .NET Services, which was renamed to the AppFabric Services which is covered in Windows Azure Platform. This name change was announced during the first keynote [View Keynote] of PDC 2009. Along with Codename “Dallas” [Data as a Service], Microsoft Pinpoint Marketplace.

Codename Dallas is built on top of Windows Azure and SQL Azure. Dallas will supply developers with Data as a Service, as part of the Open Data Movement in the United States. If this doesn’t get you excited about Microsoft’s Cloud Computing Platform, I’m not sure what will.  To ensure that you can develop on the cloud and use the SQL Azure Database Platform to your advantage read forward as I’ll be showing you how to Migrate a database to the cloud with SQL Azure Data Sync, which is an extension of the Microsoft Sync Framework.


To begin you will have to download the Microsoft Sync Framework 2.0 Software Development Kit (SDK) and the Microsoft Sync Framework Power Pack for SQL Azure November CTP (32-bit) and if you don’t already have a SQL Azure Account you can get one here at the SQL Azure Data Sync Developer Quick Start page.

Once you have your SQL Azure Account, you will need to provision a new database in the cloud and change the firewall settings so the local computer can connect to and modify the database.  These steps are provided in Making Data Rain Down from the Cloud with SQL Azure. Now that we’ve got our SQL Azure firewall database set up we’re ready to sync a local database into the cloud.

When you first fire up SQL Azure Data Sync you will see this Introduction screen. [Hopefully future releases of this tool will allow you to check a box to skip this screen, like other SQL Server Tools.]


Hitting next on the Introduction screen will take you to the SQL Azure configuration screen where you will need to enter the details for your SQL Azure Account. For this step you will need to login to your SQL Azure account to figure out what server your database server is hosted on. Fill out the database information required, like you see below.


A good thing to note here is that this database should not be created in the cloud already, the data sync tool will be creating the database from the local instance you will be setting the details up for in the next step. So lets click next and enter the information for our local Database.


Once you have both databases configured [and tested] you’re ready to select the tables you wish to synchronize or create in the cloud database.


Due to the fact Relational Databases have Foreign Keys to link data from one table to the next you may need to synchronize the database in a particular order to maintain data integrity. If you have data in your tables and your tables are related to one another be sure to place the Primary Key table, before the Foreign Key table. Here is the screen that will allow you to order your tables in order they should be synchronized.


After you have reorganized the order in which your tables are to be synchronized, you will be shuffled off to a Summary page. This page outlines the Database Server, and asks if it is to create a 1GB Database, or a 10GB Database in the cloud.


After Reviewing the information and selecting the database size that should be created. Click on the Process button to Copy the local Microsoft SQL Server database to SQL Azure.



Once this process is complete the Microsoft SQL Server has been created on the specified SQL Azure Database server. The Synchronization is carried out by SQL Server Agent, be sure to copy down the Job name from the Finish Setup screen.


You may need to Configure SQL Server Agent before the first time the Synchronization is run.

SQL Azure Data Sync is a great way to deploy your current databases into the cloud. You can use the cloud as a method of backing up your current infrastructure or you can start expanding into the cloud as a more economical solution of expanding your current data centers. As we count down to the New Year it seems fitting to think about moving into the future of Technology. Happy New Years!