What is the best way to achieve data sync between SQL Azure and Multiple On-Premises SQL server databases?

3
votes

I have a scenario as explained below and I need to implement the best Data Sync method.

  1. I have a centralized SQL Azure database (master Database)
  2. There are about 20 (this will increase in future) on-premises SQL Server Databases. These database are not necessarily always connected to the internet.
  3. All master and on-premises DB's will have the same schema/table structures.
  4. I would like to do bidirectional data sync between all on-premises databases with SQL Azure and vice-versa.
  5. Data Sync frequency will be once in a day.
  6. Each on-premises DB size is reasonable(not too big and not too small).

These below options I have explored:

  1. SQL Azure Data Sync
  2. Microsoft Sync Framework
  3. SQL Server 2008 Change Data Capture
  4. SQL Server Change Tracking

I would like to know the best possible method to achieve this.

3
sqlsql-serverazure-sql-database
When you say "options I have explored", what were the results of the exploration? – Mike Goodwin
any way to sync the schema and its changes such as stored procedures, views, sequences etc? – user1866308

3 Answers

4
votes

I have been working with SQl azure data sync, Microsift sync framework and Sql server change tracking. I have no idea about change data capture.

Sql azure data sync.

This is the easiest way to implement data sync. It is a matter of configuration. But unfortunately still in preview and Microsoft no recommended for production yet. We have been using to sync 20 databases spread around different geographical location and so far works good. No coding required. But you may have to pay in future when you are using this service. At the moment it is free.

Microsoft Sync Framework

Microsoft sync framework is for developers. Developers can use Sync framework as an API and develop sync application. Sql azure data sync use sync framework internally. To implement data sync with azure you need to implement N-Tier architecture with WCF. And you need to host your WCF service in azure web site or virtual machine. Considerable development time required and see the following link for sample implementation from Microsoft. Once you develop you can easily configure and use for sync multiple databases. Database Sync:SQL Server and SQL Express N-Tier with WCF

SQL Server Change Tracking

You need to manually programme the each table for data syn and you need to have link server setup between each sql server. To setup link server with azure database you need to open some specific port.

2
votes

items #3 and #4 in your list are not really synchronization solutions, just part of it. Both SQL CDC and SQL CT simply allows you to track the changes. you have to put in extra code to grab those changes and apply/sync to another database.

SQL Data Sync service will be your best option if you don't want to write code. Note that up until today (despite the fact its in preview for so long), Data Sync is still in Preview Mode.

If you're find writing code, Sync Fx is a good option as well (SQL Data Sync internally uses Sync Framework).

0
votes

Azure SQL Data Sync has now reached general availability (GA) as shown on the following Microsoft Article.

Announcing the general availability of Azure SQL Data Sync