My current development project has two aspects to it. First, there is a public website where external users can submit and update information for various purposes. This information is then saved to a local SQL Server at the colo facility.
我目前的开发项目有两个方面。首先，有一个公共网站，外部用户可以为各种目的提交和更新信息。然后，此信息将保存到colo工具的本地SQL Server。
The second aspect is an internal application which employees use to manage those same records (conceptually) and provide status updates, approvals, etc. This application is hosted within the corporate firewall with its own local SQL Server database.
第二个方面是员工用于管理相同记录（概念上）并提供状态更新，批准等的内部应用程序。此应用程序托管在具有自己的本地SQL Server数据库的公司防火墙内。
The two networks are connected by a hardware VPN solution, which is decent, but obviously not the speediest thing in the world.
这两个网络通过硬件VPN解决方案连接，这是很好的，但显然不是世界上最快的东西。
The two databases are similar, and share many of the same tables, but they are not 100% the same. Many of the tables on both sides are very specific to either the internal or external application.
这两个数据库是相似的，并且共享许多相同的表，但它们不是100％相同。双方的许多表都非常特定于内部或外部应用程序。
So the question is: when a user updates their information or submits a record on the public website, how do you transfer that data to the internal application's database so it can be managed by the internal staff? And vice versa... how do you push updates made by the staff back out to the website?
所以问题是：当用户更新他们的信息或在公共网站上提交记录时，如何将这些数据传输到内部应用程序的数据库，以便内部员工管理？反之亦然......你如何推动员工的更新回到网站？
It is worth mentioning that the more "real time" these updates occur, the better. Not that it has to be instant, just reasonably quick.
值得一提的是，这些更新发生的“实时”越多越好。并非它必须是即时的，只是相当快。
So far, I have thought about using the following types of approaches:
到目前为止，我已经考虑过使用以下类型的方法：
- Bi-directional replication
- 双向复制
- Web service interfaces on both sides with code to sync the changes as they are made (in real time).
- 双方的Web服务接口都带有代码，以便在进行更改时实时同步（实时）。
- Web service interfaces on both sides with code to asynchronously sync the changes (using a queueing mechanism).
- 双方的Web服务接口使用代码来异步同步更改（使用排队机制）。
Any advice? Has anyone run into this problem before? Did you come up with a solution that worked well for you?
任何建议？有没有人遇到过这个问题？你有没有想出一个适合你的解决方案？
5 个解决方案
#1
19
This is a pretty common integration scenario, I believe. Personally, I think an asynchronous messaging solution using a queue is ideal.
我相信这是一个非常常见的集成场景。就个人而言，我认为使用队列的异步消息传递解决方案是理想的。
You should be able to achieve near real time synchronization without the overhead or complexity of something like replication.
您应该能够实现近乎实时的同步，而无需复制等开销或复杂性。
Synchronous web services are not ideal because your code will have to be very sophisticated to handle failure scenarios. What happens when one system is restarted while the other continues to publish changes? Does the sending system get timeouts? What does it do with those? Unless you are prepared to lose data, you'll want some sort of transactional queue (like MSMQ) to receive the change notices and take care of making sure they get to the other system. If either system is down, the changes (passed as messages) will just accumulate and as soon as a connection can be established the re-starting server will process all the queued messages and catch up, making system integrity much, much easier to achieve.
同步Web服务并不理想，因为您的代码必须非常复杂才能处理故障情况。当一个系统重新启动而另一个系统继续发布更改时会发生什么？发送系统是否会超时？它与那些有什么关系？除非您准备丢失数据，否则您需要某种事务队列（如MSMQ）来接收更改通知，并确保它们到达其他系统。如果任一系统关闭，更改（作为消息传递）将只会累积，一旦建立连接，重新启动服务器将处理所有排队的消息并赶上，使系统完整性更容易实现。
There are some open source tools that can really make this easy for you if you are using .NET (especially if you want to use MSMQ).
如果您使用.NET，有一些开源工具可以让您轻松实现这一点（特别是如果您想使用MSMQ）。
- nServiceBus by Udi Dahan
- Udi Dahan的nServiceBus
- Mass Transit by Dru Sellers and Chris Patterson
- Dru Sellers和Chris Patterson的大众运输
There are commercial products also, and if you are considering a commercial option see here for a list of of options on .NET. Of course, WCF can do async messaging using MSMQ bindings, but a tool like nServiceBus or MassTransit will give you a very simple Send/Receive or Pub/Sub API that will make your requirement a very straightforward job.
还有商业产品，如果您正在考虑商业选项，请参阅此处获取.NET选项列表。当然，WCF可以使用MSMQ绑定进行异步消息传递，但是像nServiceBus或MassTransit这样的工具将为您提供一个非常简单的发送/接收或发布/订阅API，这将使您的需求变得非常简单。
If you're using Java, there are any number of open source service bus implementations that will make this kind of bi-directional, asynchronous messaging a snap, like Mule or maybe just ActiveMQ.
如果您正在使用Java，那么有许多开源服务总线实现可以使这种双向异步消息快速发布，如Mule或者只是ActiveMQ。
You may also want to consider reading Udi Dahan's blog, listening to some of his podcasts. Here are some more good resources to get you started.
您可能还想考虑阅读Udi Dahan的博客，听他的一些播客。这里有一些更好的资源可以帮助您入门。
#2
3
I'm mid-way through a similar project except I have multiple sites that need to keep in sync over slow connections (dial-up in some cases).
我在一个类似的项目中途，除了我有多个站点需要通过慢速连接保持同步（在某些情况下拨号）。
Firstly you need to track changes, if you can use SQL 2008 (even the Express version is enough if the 2Gb limit isn't a problem) this will ease the pain greatly, just turn on Change Tracking on the database and each table. We're using SQL Server 2008 at the head office with the extended schema and SQL Express 2008 at each site with a sub-set of data and limited schema.
首先，您需要跟踪更改，如果您可以使用SQL 2008（即使Express版本足够，如果2Gb限制不是问题）这将大大减轻痛苦，只需打开数据库和每个表上的更改跟踪。我们在总部使用SQL Server 2008，扩展架构和SQL Express 2008在每个站点都有一个数据子集和有限的架构。
Secondly you need to track your changes, Sync Services does the trick nicely and supports using a WCF gateway into the main database. In this example you will need to use the Sync using SQL Express Client sample as a starting point, note that it's based on SQL 2005 so you'll need to update it to take advantage of the Change Tracking features in 2008. By default the Sync Services uses SQL CE on the clients, which I'm sure isn't enough in your case. You'll need a service that runs on your Web Server that periodically (could be as often as every 10 seconds if you want) runs the Synchronize() method. This will tell your main database about changes made locally and then ask the server for all changes made there. You can set up the get and apply SQL code to call stored procedures and you can add event handlers to handle conflicts (e.g. Client Update vs Server Update) and resolve them accordingly at each end.
其次，您需要跟踪您的更改，Sync Services可以很好地完成这项工作，并支持将WCF网关用于主数据库。在此示例中，您将需要使用Sync Express SQL Express Client示例作为起点，请注意它基于SQL 2005，因此您需要更新它以利用2008中的更改跟踪功能。默认情况下，Sync服务在客户端上使用SQL CE，我相信在您的情况下是不够的。您需要定期运行在Web服务器上运行的服务（如果需要，可以每隔10秒运行一次）运行Synchronize（）方法。这将告诉您的主数据库有关在本地进行的更改，然后向服务器询问所有更改。您可以设置get和apply SQL代码来调用存储过程，并且可以添加事件处理程序来处理冲突（例如，客户端更新与服务器更新）并在每一端相应地解决它们。
#3
1
We have a shop as a client, with three stores connected to the same VPN
Two of the shops have a computer running as a "server" for that shop and the the third one has the "master database"
To synchronize all to the master we don't have the best solution, but it works: there is a dedicated PC running an application that checks the timestamp of every record in every table of the two stores and if it is different that the last time you synchronize, it copies the results
Note that this works both ways. I.e. if you update a product in the master database, this change will propagate to the other two shops. If you have a new order in one of the shops, it will be transmitted to the "master".
With some optimizations you can have all the shops synchronize in around 20minutes
我们有一个商店作为客户，有三个商店连接到同一个VPN两个商店有一台计算机作为该商店的“服务器”运行，第三个商店有“主数据库”要同步所有主人我们没有最好的解决方案，但它确实有效：有一台运行应用程序的专用PC，它检查两个商店的每个表中每条记录的时间戳，如果它与您上次同步的时间不同，则复制结果请注意，这两种方式都有效。即如果更新master数据库中的产品，此更改将传播到其他两个商店。如果您在其中一家商店有新订单，它将被传送到“主”。通过一些优化，您可以让所有商店在大约20分钟内同步
#4
1
Recently I have had a lot of success with SQL Server Service Broker which offers reliable, persisted asynchronous messaging out of the box with very little implementation pain.
最近我在SQL Server Service Broker上取得了很大的成功，它提供了可靠，持久的异步消息，开箱即用，几乎没有实现的痛苦。
- It is quick to set up and as you learn more you can use some of the more advanced features.
- 设置快速，随着您了解更多，您可以使用一些更高级的功能。
- Unknown to most, it is also part of the desktop editions so it can be used as a workstation messaging system
- 大多数人都不知道，它也是桌面版的一部分，因此它可以用作工作站消息系统
- If you have existing T-SQL skills they can be leveraged as all the code to read and write messages is done in SQL
- 如果您具有现有的T-SQL技能，则可以利用它们，因为所有用于读取和写入消息的代码都是在SQL中完成的
- It is blindingly fast
- 它的速度非常快
It is a vastly under-hyped part of SQL Server and well worth a look.
它是SQL Server中一个大肆宣传的部分，非常值得一看。
#5
0
I'd say just have a job that copies the data in the pub database input table into a private database pending table. Then once you update the data on the private side have it replicated to the public side. If you don't have any of the replicated data on the public side updated it should be a fairly easy transactional replication solution.
我想说只需要一个将pub数据库输入表中的数据复制到私有数据库挂起表中的作业。然后，一旦您更新私有端的数据，它就会复制到公共端。如果您没有更新公共端的任何复制数据，那么它应该是一个相当简单的事务复制解决方案。
#1
19
This is a pretty common integration scenario, I believe. Personally, I think an asynchronous messaging solution using a queue is ideal.
我相信这是一个非常常见的集成场景。就个人而言，我认为使用队列的异步消息传递解决方案是理想的。
You should be able to achieve near real time synchronization without the overhead or complexity of something like replication.
您应该能够实现近乎实时的同步，而无需复制等开销或复杂性。
Synchronous web services are not ideal because your code will have to be very sophisticated to handle failure scenarios. What happens when one system is restarted while the other continues to publish changes? Does the sending system get timeouts? What does it do with those? Unless you are prepared to lose data, you'll want some sort of transactional queue (like MSMQ) to receive the change notices and take care of making sure they get to the other system. If either system is down, the changes (passed as messages) will just accumulate and as soon as a connection can be established the re-starting server will process all the queued messages and catch up, making system integrity much, much easier to achieve.
同步Web服务并不理想，因为您的代码必须非常复杂才能处理故障情况。当一个系统重新启动而另一个系统继续发布更改时会发生什么？发送系统是否会超时？它与那些有什么关系？除非您准备丢失数据，否则您需要某种事务队列（如MSMQ）来接收更改通知，并确保它们到达其他系统。如果任一系统关闭，更改（作为消息传递）将只会累积，一旦建立连接，重新启动服务器将处理所有排队的消息并赶上，使系统完整性更容易实现。
There are some open source tools that can really make this easy for you if you are using .NET (especially if you want to use MSMQ).
如果您使用.NET，有一些开源工具可以让您轻松实现这一点（特别是如果您想使用MSMQ）。
- nServiceBus by Udi Dahan
- Udi Dahan的nServiceBus
- Mass Transit by Dru Sellers and Chris Patterson
- Dru Sellers和Chris Patterson的大众运输
There are commercial products also, and if you are considering a commercial option see here for a list of of options on .NET. Of course, WCF can do async messaging using MSMQ bindings, but a tool like nServiceBus or MassTransit will give you a very simple Send/Receive or Pub/Sub API that will make your requirement a very straightforward job.
还有商业产品，如果您正在考虑商业选项，请参阅此处获取.NET选项列表。当然，WCF可以使用MSMQ绑定进行异步消息传递，但是像nServiceBus或MassTransit这样的工具将为您提供一个非常简单的发送/接收或发布/订阅API，这将使您的需求变得非常简单。
If you're using Java, there are any number of open source service bus implementations that will make this kind of bi-directional, asynchronous messaging a snap, like Mule or maybe just ActiveMQ.
如果您正在使用Java，那么有许多开源服务总线实现可以使这种双向异步消息快速发布，如Mule或者只是ActiveMQ。
You may also want to consider reading Udi Dahan's blog, listening to some of his podcasts. Here are some more good resources to get you started.
您可能还想考虑阅读Udi Dahan的博客，听他的一些播客。这里有一些更好的资源可以帮助您入门。
#2
3
I'm mid-way through a similar project except I have multiple sites that need to keep in sync over slow connections (dial-up in some cases).
我在一个类似的项目中途，除了我有多个站点需要通过慢速连接保持同步（在某些情况下拨号）。
Firstly you need to track changes, if you can use SQL 2008 (even the Express version is enough if the 2Gb limit isn't a problem) this will ease the pain greatly, just turn on Change Tracking on the database and each table. We're using SQL Server 2008 at the head office with the extended schema and SQL Express 2008 at each site with a sub-set of data and limited schema.
首先，您需要跟踪更改，如果您可以使用SQL 2008（即使Express版本足够，如果2Gb限制不是问题）这将大大减轻痛苦，只需打开数据库和每个表上的更改跟踪。我们在总部使用SQL Server 2008，扩展架构和SQL Express 2008在每个站点都有一个数据子集和有限的架构。
Secondly you need to track your changes, Sync Services does the trick nicely and supports using a WCF gateway into the main database. In this example you will need to use the Sync using SQL Express Client sample as a starting point, note that it's based on SQL 2005 so you'll need to update it to take advantage of the Change Tracking features in 2008. By default the Sync Services uses SQL CE on the clients, which I'm sure isn't enough in your case. You'll need a service that runs on your Web Server that periodically (could be as often as every 10 seconds if you want) runs the Synchronize() method. This will tell your main database about changes made locally and then ask the server for all changes made there. You can set up the get and apply SQL code to call stored procedures and you can add event handlers to handle conflicts (e.g. Client Update vs Server Update) and resolve them accordingly at each end.
其次，您需要跟踪您的更改，Sync Services可以很好地完成这项工作，并支持将WCF网关用于主数据库。在此示例中，您将需要使用Sync Express SQL Express Client示例作为起点，请注意它基于SQL 2005，因此您需要更新它以利用2008中的更改跟踪功能。默认情况下，Sync服务在客户端上使用SQL CE，我相信在您的情况下是不够的。您需要定期运行在Web服务器上运行的服务（如果需要，可以每隔10秒运行一次）运行Synchronize（）方法。这将告诉您的主数据库有关在本地进行的更改，然后向服务器询问所有更改。您可以设置get和apply SQL代码来调用存储过程，并且可以添加事件处理程序来处理冲突（例如，客户端更新与服务器更新）并在每一端相应地解决它们。
#3
1
We have a shop as a client, with three stores connected to the same VPN
Two of the shops have a computer running as a "server" for that shop and the the third one has the "master database"
To synchronize all to the master we don't have the best solution, but it works: there is a dedicated PC running an application that checks the timestamp of every record in every table of the two stores and if it is different that the last time you synchronize, it copies the results
Note that this works both ways. I.e. if you update a product in the master database, this change will propagate to the other two shops. If you have a new order in one of the shops, it will be transmitted to the "master".
With some optimizations you can have all the shops synchronize in around 20minutes
我们有一个商店作为客户，有三个商店连接到同一个VPN两个商店有一台计算机作为该商店的“服务器”运行，第三个商店有“主数据库”要同步所有主人我们没有最好的解决方案，但它确实有效：有一台运行应用程序的专用PC，它检查两个商店的每个表中每条记录的时间戳，如果它与您上次同步的时间不同，则复制结果请注意，这两种方式都有效。即如果更新master数据库中的产品，此更改将传播到其他两个商店。如果您在其中一家商店有新订单，它将被传送到“主”。通过一些优化，您可以让所有商店在大约20分钟内同步
#4
1
Recently I have had a lot of success with SQL Server Service Broker which offers reliable, persisted asynchronous messaging out of the box with very little implementation pain.
最近我在SQL Server Service Broker上取得了很大的成功，它提供了可靠，持久的异步消息，开箱即用，几乎没有实现的痛苦。
- It is quick to set up and as you learn more you can use some of the more advanced features.
- 设置快速，随着您了解更多，您可以使用一些更高级的功能。
- Unknown to most, it is also part of the desktop editions so it can be used as a workstation messaging system
- 大多数人都不知道，它也是桌面版的一部分，因此它可以用作工作站消息系统
- If you have existing T-SQL skills they can be leveraged as all the code to read and write messages is done in SQL
- 如果您具有现有的T-SQL技能，则可以利用它们，因为所有用于读取和写入消息的代码都是在SQL中完成的
- It is blindingly fast
- 它的速度非常快
It is a vastly under-hyped part of SQL Server and well worth a look.
它是SQL Server中一个大肆宣传的部分，非常值得一看。
#5
0
I'd say just have a job that copies the data in the pub database input table into a private database pending table. Then once you update the data on the private side have it replicated to the public side. If you don't have any of the replicated data on the public side updated it should be a fairly easy transactional replication solution.
我想说只需要一个将pub数据库输入表中的数据复制到私有数据库挂起表中的作业。然后，一旦您更新私有端的数据，它就会复制到公共端。如果您没有更新公共端的任何复制数据，那么它应该是一个相当简单的事务复制解决方案。