如何为来自不同公司的断开连接的系统同步两个数据库
是否有标准消息传递协议/API 可用于保持数据库同步。或者用于创建和解析消息的 API。
我们公司正在与另一家公司合作，为两种不同类型的用户提供两种不同的软件包。数据位于两个独立的数据库中，但其中部分数据必须保持同步。
他们的系统对我们来说几乎是一个黑匣子。反之亦然。
因此，需要跟踪更新，将它们转换为消息并将它们发送到 Web 服务，将它们映射回目标数据库字段，然后提交它们。
数据库架构不匹配。
我知道我们将不得不自己完成大部分工作，但关于消息传递或技术的一些想法会很好。
Is there a standard messaging protocol(s) / API(s) available to keep databases in sync. Or alternatively API(s) for creating and parsing messages.
Our company is working with another company to provide two different software packages to two different kinds of users. The data sits in two separate databases but parts of it have to remain in sync.
Their system is pretty much a black box to us. And vice versa.
So what would be required would be to track updates, and turn these into messages and send them to a web service, map these back to the destination database fields, and commit them.
The database schemas do not match.
I am aware that we are going to have to roll most of this ourself, but some ideas around messaging or techniques would be good.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您有两个问题：
有一个解决方案结合了这两个问题的解决方案，我相信您已经知道它：复制。 合并复制将允许两个站点更新数据，并且还提供合并冲突解决。但复制仅在表架构相似时才起作用，并且对开发造成很大限制，因为架构更改必须在站点之间仔细协调。实际上，当网站由独立公司运营时，长期维护是相当困难的。
如果您想自行部署，更改跟踪部分已在 SQL Server 中内置支持：
两者都可用于同步解决方案作为检测更改内容的手段。
应用更改可以通过 Web 服务来解决，但 SQL Server 中也有内置解决方案，可以实现更高的可伸缩性和吞吐量：服务代理。只要通信 API（消息协议）保持不变，依靠消息定义的 API 进行同步允许两个站点按照自己的节奏发展并几乎随意更改架构。
You have two problems:
There is a solution that combines a solution to both issues and I'm sure you are aware of it: replication. Merge Replication would allow both sites to update the data and would also provide merge conflict resolution. But replication only works when the table schema is similar and puts a big constraint on development as schema changes have to be carefully coordinated between the sites. In practice, when the sites are operated by independent companies, is quite difficult to maintain for a long term.
If you want to roll your own the change tracking part has built in support in SQL Server:
Both can be used for a sync solution as a mean to detect what changed.
Applying the changes can be resolved by a web service, but there are also built-in solutions in SQL Server that allow for far higher scalability and throughput: Service Broker. Relying on a message defined API for sync allows the two sites to evolve at their own pace and change the schema almost at will, as long as the communication API (the message protocol)remains unchanged.
提供的答案给了我一些好主意，但我认为我们最终会做一些不同的事情。
我们正在使用 MSMQ，并定义一个我们将自行推出的标准消息传递系统。
至于我们如何知道事情发生了什么变化，我目前还不确定。
The answers provided give me some good ideas, but I think we are going to end up doing something a bit different.
We are using MSMQ, and defining a standard messaging system which we will roll ourselves.
As to how we will know what things have changed I am not sure at the moment.
一种解决方案：SQL Server 集成服务。它出现在 SQL Server 2005 中。这正是您所需要的。在SQL Server 2000中它被称为DTS，用于数据转换服务。创建它是为了将数据从一点导入/导出/转换到另一点。这在 SQL Server 2005 中确实很容易使用（DTS 非常糟糕）。
所以基本上，您必须编写包来从数据库导入数据、转换、过滤等，这正是您需要的将其插入数据库的方式。反之亦然。
关于黑盒事实，您应该生成数据库关系设计以使其更容易。
编辑
为了以防万一您需要安装它，我记得 SQL Server 2005 安装程序根本没有安装 SSIS 的错误。我必须满足安装程序系统要求步骤中的所有警告才能获得它。
One solution : SQL Server Integration Service. It appears from SQL Server 2005. This is exactly what you need. It was called DTS in SQL Server 2000 for Data Transformation Service. This was created to import/export/transform data from one point to an other. This is really easy to use from SQL Server 2005 (DTS is quite horrible).
So basically, you will have to write packages to import data from their database, transform, filter, etc. it exactly how you need it to insert it into your database. And vice versa.
Regarding the black box fact, you should generate the database relational design to make it easier.
EDIT
Just in case of you need to install it, I remember bugs from the SQL Server 2005 installer not installing SSIS at all. I had to satisfy all warnings in the installer system requirements step to obtain it.