Keep local MS SQL 2008 DB table and remote SQL Azure DB table in sync

Question

Hi there, I have a dedicated server which hosts a Windows Service which does a lot of very heavy load stuff and populates a number of SQL Server database tables.

However, of all the database tables it populates and works with, I want only one to be synchronised with a remote SQL Azure DB table. This is because this table holds what I called Resolved data, which is the end result of the Windows Service's work.

I would like to keep a SQL Azure database table in sync with this database table.

As far as I understand, my options are:

• Move everything onto Azure (but that involves a massive development overhead and risk)
• Have another Windows Service on the dedicated server which essentially looks at changed records since the last update and then manually update the SQL Azure table

Answer 1

Conversely, you could have a daily backup performed on your main database tables that only backs up changes. Then you could transfer the backup file to the SQL Azure database host and Restore from the file. At that point, you would have them in sync through daily scheduled tasks that shouldn't take very much effort. The BACKUP and RESTORE T-SQL commands should accomplish what you're looking for, and SQL Azure says it supports T-SQL.

Answer 2

Microsoft has the SQL Azure Data Sync used with the Sync framework:

http://www.microsoft.com/windowsazure/developers/sqlazure/datasync/

This can be used from T-SQL and the SQL Agent:

http://blogs.msdn.com/b/sync/archive/2010/03/25/initiating-sync-to-sql-azure-using-sql.aspx

Answer 3

Try SQL Examiner Suite http://www.sqlaccessories.com/SQL_Examiner_Suite/

This tool can synchronize your local database with SQL Azure database (both schema and data)