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.]

SQL-Azure-Data-Sync-Intro

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.

SQL-Azure-Data-Sync-Setup-AzureDB

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.

SQL-Azure-Data-Sync-Setup-LocalDB

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.

SQL-Azure-Data-Sync-Tables

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.

SQL-Azure-Data-Sync-Tables-Order

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.

SQL-Azure-Data-Sync-Summary

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.

SQL-Azure-Data-Sync-Process

SQL-Azure-Data-Sync-Progress

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.

SQL-Azure-Data-Sync-Results

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!

  • http://www.brainiation.com/ David Cater

    Thanks for the informative article. I’m just starting to look at SQL Azure Data Sync, and I have one major question. If I’m creating a retail app with many potential customers on many devices, how do I deal with the SQL Azure firewall settings? As you pointed out, I have to specifically add my development PC’s IP to the firewall to even see the database. How would synchronize work in the production scenario where you won’t know all of the IPs? Are you just supposed to open it wide, or is there going to have to be some sort of proxy involved, where clients talk to the proxy and the proxy is allowed to talk to the database?

    Thanks,

    David Cater

  • http://localhost:36251/ Cory Fowler

    @David – The Firewall settings are only required for computers that are directly connecting to your SQL Azure database. If your application is deployed to Windows Azure you can enable the MicrosoftServices Firewall settings which will enable any instance of your application running in Azure to connect to the database. In your scenerio your clients will be connecting to your application, and therefore don’t need their IPs assigned in the firewall in order to connect, as your application will be accessing the database. If your application is on-premise you will need to enable your Web/Application Server to access the SQL Azure Instance by setting it’s IP in the firewall settings screen.

  • SQIAR BI

    Tableau Contractor in London

    SQIAR (http://www.sqiar.com/consultancy/tableau/) is a leading Business intelligence Company.SQIAR specialize in transforming raw data into meaningful and useful information by leveraging our profound understanding of data and visualization.SQIAR experts create custom dashboards that allow decision makers to discover and act on new insights quickly – while minimizing demands on IT staff.SQIAR provide clients with a choice of leading business intelligence tools Tableau Software.