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!

Working with Windows Azure Development Storage

During the Development Cycle it is necessary to connect to a database to ensure that your data is getting stored in Windows Azure Storage Services. Microsoft was nice enough to give us this functionality out of the box so we don’t actually need a Windows Azure account before beginning development on an application. Of course you will need to Initialize your Development Storage Service on your development machine before you get going.  Once you’re done setting up the Development Storage Service you will need to configure the Development Storage in the ServiceConfiguration.cscfg file.  You will need to add the following ConfigurationSettings to each Role element in the ServiceConfiguration.cscfg [Please note that these are default values and may be subject to change].

   1:  <ConfigurationSettings>
   2:        <Setting name="DiagnosticsConnectionString" 
                      value="UseDevelopmentStorage=true" />
   3:        <Setting name="AccountName" value="devstoreaccount1" />
   4:        <Setting name="AccountSharedKey" 
                      value="Eby8vdM02xNOcqFlqUwJPLlmEtlCDXJ1OUzFT50uSRZ6IFsu
                             Fq2UVErCz4I6tq/K1SZFPTOtr/KBHBeksoGMGw==" />
   5:        <Setting name="BlobStorageEndpoint" value="http://localhost:36251" />
   6:        <Setting name="QueueStorageEndpoint" value="http://localhost:36251" />
   7:        <Setting name="TableStorageEndpoint" value="http://localhost:36251"/>
   8:  </ConfigurationSettings>

Once you have these configuration settings in place you will be ready to interact with the Windows Azure Development Storage Service on your Development Machine.  Stay tuned for my next blog Series which will describe the different between Blob Storage, Queue Storage, and Table Storage and how you will go about interacting with the different storage spaces.

For more information please refer to:

Setting up the Development Storage Service

If you are setting up your Development Environment for Windows Azure and want to avoid installing SQL Server Express. I didn’t want to use SQL Server Express because I’ve already installed SQL Server 2008 R2 CTP so I can interact with my SQL Azure Database in the Cloud.

From the command line, Navigate to the Windows Azure SDK directory.

cd "C:\Program Files\Windows Azure SDK\v1.0\bin\devstore"

Then you will Launch the Development Storage Initialization Tool [DSInit.exe]. You will need to set the SqlInstance [/sqlinstance:] that you wish to create the tables on, and you will need to force [/forceCreate] the tool to create the tables.

DSInit.exe /sqlinstance:<YourDatabaseName>/forceCreate

This will launch the Initialization Tool.

Development Storage Initialization
If you receive the error message below you it is most likely because the instance name was improperly set. Ensure to be use only the InstanceName do not include periods or slashes.

Development Storage Initialization Error

After your Development Storage is set up make sure you  go into your Development Fabric and Start the Development Storage Service.

Happy Coding!

Azure at PDC 2009: Replays Now Available!

Did you miss out going to Microsoft PDC this year? Want to keep up with what went on in the Windows Azure Sessions? Be sure you check out the Windows Azure Team Blog for the videos of each of the sessions.

For your convince I’ve copied the list below:

If you would like to check out some more Videos on Windows Azure make sure to head over to msdev.com.

Open Source Role Call for Windows Azure

Windows Azure is a very open platform and now has a number of Microsoft supported SDKs.

Windows Azure

In order to test out the Windows Azure platform to it’s full extent I am looking for a few  developers that program in the open source realm to develop a few simple web applications in Java, Python, or Ruby to be hosted on Windows Azure. These applications don’t need to be anything too extravagant, however, I would prefer if the application was to use the Windows Azure Storage API.

You can download the respective storage APIs here: Java, Python, Ruby, and PHP.

If you are an Open Source Developer and would like to try out the Windows Azure Platform contact me for a Windows Azure Token. Once you have deployed your application into the cloud, I will create a blog account  for you on the Canadian Azure Experience Cloud Blog [that I installed using Azure BlogEngine.Net] for you to post your experience with Windows Azure.

All this sounds great, but what’s in it for you? I am not a Microsoft Employee, so I won’t be able to offer up much, but this is a great starting point for the Elance Windows Azure Contest. Plus I will offer up a post about you and your application on my blog for all the world to see [Hey, You found this didn’t you?].

Here are a few resources to get you going with Windows Azure:

Latest Windows Azure Project Conversion…

While Guelph Coffee and Code was participating in the Technology Focus Panel on Windows Azure, I attempted to upload the BlogEngine.Net sample from Code Plex during my demo on how to deploy to the Windows Azure Platform.

During the Publishing Step there were a number of Errors while it was trying to compile the project. These errors are due to a namespace change that you can read about in my blog post Moving from the ‘ServiceHosting’ to ‘WindowsAzure’ Namespace and a follow up post entitled Windows Azure Guest Book – Refactored. [I will be writing a post shortly on the Microsoft.WindowsAzure.Diagnostics namespace which replaced the RoleManager.WriteToLog()].

I will be deploying the BlogEngine.Net application to Windows Azure and am looking for Developers that have been deploying Projects into the Cloud to post their experiences on the blog.  If you would like to guest write on the Canadian Windows Azure Experience Blog please contact me with a link to your Windows Azure application and I will create an account for you.

If you would like to host your blog in the cloud, here are the updated files for the Azure  BlogEngine.Net application for Windows Azure. Feel free to download the project and use it for your own blog in the cloud.

Windows Azure

BlogEngine.Net