在SQL Azure和多个本地SQL server数据库之间实现数据同步的最佳方式是什么?

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

我有一个场景如下所述，我需要实现最好的数据同步方法。

I have a centralized SQL Azure database (master Database)
我有一个集中式的SQL Azure数据库(主数据库)
There are about 20 (this will increase in future) on-premises SQL Server Databases. These database are not necessarily always connected to the internet.
目前大约有20个(将来还会增加)基于前提的SQL服务器数据库。这些数据库不一定总是连接到internet。
All master and on-premises DB's will have the same schema/table structures.
所有主数据库和本地数据库都具有相同的模式/表结构。
I would like to do bidirectional data sync between all on-premises databases with SQL Azure and vice-versa.
我希望使用SQL Azure在所有内部数据库之间进行双向数据同步，反之亦然。
Data Sync frequency will be once in a day.
数据同步频率一天一次。
Each on-premises DB size is reasonable(not too big and not too small).
每个on-premises DB size是合理的(不太大也不太小)。

These below options I have explored:

以下是我探索过的选项:

SQL Azure Data Sync
SQL Azure数据同步
Microsoft Sync Framework
微软同步框架
SQL Server 2008 Change Data Capture
SQL Server 2008更改数据捕获
SQL Server Change Tracking
SQL Server变更跟踪

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

我想知道达到这个目的的最好方法。

3 个解决方案

#1

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数据同步、Microsift同步框架和SQl server更改跟踪。我不知道如何更改数据。

Sql azure data sync.

Sql azure数据同步。

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.

这是实现数据同步最简单的方法。这是一个配置问题。但不幸的是，在预览版和微软还没有推荐生产。我们一直在使用同步分布在不同地理位置的20个数据库，到目前为止效果很好。不需要编码。但是当你使用这项服务时，你将来可能需要付费。目前它是免费的。

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

微软同步框架是为开发人员准备的。开发人员可以将同步框架用作API并开发同步应用程序。Sql azure数据同步在内部使用同步框架。要实现与azure的数据同步，需要使用WCF实现n层架构。您需要在azure web站点或虚拟机中托管WCF服务。需要大量的开发时间，请参见下面的链接，了解来自Microsoft的示例实现。一旦开发了，就可以很容易地配置和使用同步多个数据库。数据库同步:SQL Server和SQL Express N-Tier与WCF。

SQL Server Change Tracking

SQL Server变更跟踪

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.

您需要手动为数据syn编写每个表，并且您需要在每个sql服务器之间建立链接服务器设置。要使用azure数据库设置link server，需要打开一些特定的端口。

#2

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.

列表中的项目#3和#4不是真正的同步解决方案，只是其中的一部分。SQL CDC和SQL CT都允许您跟踪更改。您必须输入额外的代码来获取这些更改并将/sync应用到另一个数据库。

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.

如果您不想编写代码，SQL数据同步服务将是您最好的选择。请注意，直到今天(尽管它在预览很长时间)，数据同步仍然处于预览模式。

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

如果您发现正在编写代码，那么Sync Fx也是一个不错的选择(SQL Data Sync在内部使用Sync框架)。

#3

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

Azure SQL数据同步现在已经达到了通用可用性(GA)，如下面的Microsoft文章所示。

Announcing the general availability of Azure SQL Data Sync

宣布Azure SQL数据同步的一般可用性

#1