从sql-server到另一个数据仓库的实时单向同步 [英] Real-time unidirectional synchronization from sql-server to another data repository
问题描述
在我在此门户上的上一个问题中，我询问了一些有关在 SQL Server 和基于键值的数据存储库之间同步数据的见解.
In my previous question on this portal, I had asked about some insight about syncing data between SQL Server and key-value based data repositories.
代替同样的问题(从 SQL 到 HBase 或任何其他数据库的一种实时同步)，我需要考虑一些性能和延迟方面的考虑，但没有找到一种非常万无一失的方法.
In lieu of the same problem (one way real-time synchronization from SQL to HBase or any other database), I need to take care of some performance and latency considerations and did not find a very foolproof way of doing it.
我们有多个 SQL 2008 数据分片，其中数据从各种来源更新，并由多个进程同时处理(UI 从相同分片读取).
We have multiple SQL 2008 data shards where data is updated from various sources and processed by many processes at the same time (and UI reads from the same shards).
目标是在任何时间点获取选定表中的所有更新，并以几乎实时的方式将它们传输到其他数据源.
The goal is to get all updates in selected tables at any point of time and transport them to other data source in almost real-time fashion.
SQL 分片的变化量将保持在 100-500 MB 范围内(如果我们保持 1 分钟的频率).我们不想对 SQL 服务器进行重大更改，因为我们会在迁移完整系统后将其丢弃.
Amount of change in SQL shards will stays in 100-500 MB range (if we keep the frequency of 1 min). We do not want to make major changes in SQL servers as we would discard it after migrating complete system.
不幸的是，我们的数据访问层在各个层中都一团糟.否则，这将是在两个方向上分叉更新的最佳方式.
Unfortunately, our data access layer is messed up in various layers. Otherwise, it would have been the best way to fork the updates in two directions.
触发器会减慢分片的速度并使它们处于无响应状态.
Triggers will slow down the shards and leave them in an unresponsive state.
不确定 SQL Server 2008 是否具有类似于 SQL Server 2005 通知服务的功能以及效果如何.
Not sure if SQL Server 2008 has something similar to SQL Server 2005 Notification services and how effective that would be.
任何其他创新解决方案都会大有帮助.
Any other innovative solution would greatly helpful.
这里我的问题不是将数据从关系转换为键值形式(这相当容易)，而是如何实时获取 SQL Server 更新(可以承受1-2分钟)不影响用户体验.
Here my problem is not about transforming the data from relational to Key-value form (it's fairly easy), but how to get the SQL Server updates in real-time (can afford the latency of 1-2 minutes) without affecting the user experience.
推荐答案
您是否看过 SQL Service Broker?这是一个包含一些信息的链接:http://blogs.msdn.com/b/sql_service_broker/archive/2008/07/09/real-time-data-integration-with-service-broker-and-other-sql-techniques.aspx
have you looked at SQL Service Broker? here's a link with some info on it: http://blogs.msdn.com/b/sql_service_broker/archive/2008/07/09/real-time-data-integration-with-service-broker-and-other-sql-techniques.aspx
这篇关于从sql-server到另一个数据仓库的实时单向同步的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
