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.

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

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

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

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

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

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

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

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

This entry was posted in SQL Server 2008. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s