Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Playing With SQL Azure Data Synch

Microsoft has provided a free tool that allows you to easily create a SQL Azure database, and then synchronize the data periodically (on a table by table basis) with an on-premises SQL Server database. You could use this to do an initial data load to SQL Azure, or to keep whatever tables you want synchronized between SQL Azure and the on-premises copy of the database. I would think about the use case for the latter example, by considering how volatile the data might be and how much bandwidth you might use (remember SQL Azure charges for bandwidth going in and out of the Microsoft data center(s)). The cost is $0.10 in / $0.15 out / GB (except in Asia, where it is $0.30 in / $0.45 out / GB).

The SQL Azure Data Synch Developer Quick Start page has instructions and links to the Microsoft Synch Framework 2.0 Software Development Kit (SDK) and the Microsoft Sync Framework Power Pack for SQL Azure November CTP (32-bit) which are needed to make this tool work.  Even if you are using a 64-bit version of Windows, you should install the x86 version of the Power Pack.  It also gives you a Visual Studio 2008 Template that allows you to take an existing SQL Azure database offline, this new template simplifies the task of creating an offline data cache within SQL Compact.

After you have installed the Microsoft Synch Framework 2.0 SDK and the Synch Framework Power Pack, you can run the wizard to easily create a SQL Azure database and synchronize your on-premises data with SQL Azure. You have to have SQL Agent available and running on your local instance of SQL Server.  I’ll walk though the wizard below:

This allows you to specify the SQL Azure information, including the server name, database name, and login information. You need to use the complete SQL Azure Server name.

image

This allows you to specify your local server and database information.

image

This allows you to choose which tables (from the local database) to synchronize.

image

This allows you to specify the order of the table synchronization.

image

This shows the settings that will be used for the synchronization.

image

This shows the tool processing and creating the local SQL Agent job.

image

This shows the SQL Agent job that has been created to keep the data in Synch between your local instance and SQL Azure.

image

SQL Agent Job has been created in specified local instance of SQL Server. This job is scheduled to run once a day at 12:00AM by default, (which you could easily change). It creates an Operating System(CmdExec) job step that calls the SynchLocalSqlAzureDatabase.exe that is installed by the Power Pack for the SQL Azure November CTP

image

Here is the CmdExec text, (with the password hash altered):

"C:\Program Files (x86)\Microsoft Sync Framework\Power Pack For SQL Azure November CTP\SyncLocalSqlAzureDatabase.exe" -localServer GlennAcer\SQL2008R2 -localDb AdventureWorksLT2008R2 -SqlAzureDb AdventureWorksLT2008R2 -scope Sync_AdventureWorksLT2008R2 -SqlAzureServer zr21iryh9s.database.windows.net   -SqlAzureUser GlennAlanBerry -SqlAzurePassword password16bxxxxxxxxxxxxxxxxxxxxxxxxxxx -ConflictResolutionPolicy LocalServerWins

Running the newly created SQL Agent job for the first time creates the database in SQL Azure, and synchronizes the data (which means all of the data in the selected tables gets copied to Azure the first time).

Comments

Posted by Dugi on 19 February 2010

Yep it is very nice tool for synchronizing data between Azure DB and local DB. I used it for testing and it works perfect!

Posted by Steve Jones on 19 February 2010

I'd like to see you, or someone use it for real in some application.

Posted by Anonymous on 19 February 2010

Pingback from  SQL Server Central «  Invitation codes

Posted by Glenn Berry on 19 February 2010

The tool does create some extra tables and stored procedures in the SQL Azure database, that it uses for data synchronization purposes.

I would probably rip those out after the initial data load if that was all I wanted to do

Posted by Anonymous on 19 February 2010

Pingback from  cpanvote: a Perl Mini-Project | The Pythian Blog | Perl WebDev Insider

Leave a Comment

Please register or log in to leave a comment.