以最小的延迟将来自许多不同数据库的数据整合到一个数据库中
我有 12 个数据库，总计大约 1.0TB，每个数据库都位于运行 SQL 2005 Enterprise 的不同物理服务器上 - 所有数据库都具有完全相同的架构。我需要将此数据卸载到一个单独的数据库中，以便我们可以用于其他目的（报告、Web 服务等），延迟最多为 1 小时。
还应该注意的是，这些服务器都位于同一机架中，通过千兆位连接进行连接，并且数据库的插入量很少（平均 2500 条记录/小时）。 当前的方法非常不稳定：数据当前正在从 12 台服务器中的每台服务器复制（SQL Server 事务复制）到另一台服务器上的数据库（是的，来自 12 台不同服务器的 12 个不同员工表复制到不同服务器上的单个员工表）服务器）。
每个表都有一个主键，并且所有表中的行都是唯一的（每个表中有一个 FacilityID）。
我有什么选择？ 必须有一种简单的方法来做到这一点。
I have 12 databases totaling roughly 1.0TB, each on a different physical server running SQL 2005 Enterprise - all with the same exact schema. I need to offload this data into a separate single database so that we can use for other purposes (reporting, web services, ect) with a maximum of 1 hour latency.
It should also be noted that these servers are all in the same rack, connected by gigabit connections and that the inserts to the databases are minimal (Avg. 2500 records/hour).
The current method is very flakey: The data is currently being replicated (SQL Server Transactional Replication) from each of the 12 servers to a database on another server (yes, 12 different employee tables from 12 different servers into a single employee table on a different server).
Every table has a primary key and the rows are unique across all tables (there is a FacilityID in each table).
What are my options? There has to be a simple way to do this.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
SQL Server事务复制有什么问题，很多地方都使用它？看起来设置得很好，每个数据库中都有一个 FacilityID？
What is the problem with SQL Server Transactional Replication, many places use it? it looks like it is set up nicely, with a FacilityID in each database?
根据您要处理的表数量，设置一些从每个表移动数据的 SSIS ETL 包可能会更容易。如果您设置了包配置并且架构相同，您应该能够对所有数据库使用同一组包。如果您有超过 10-20 个表，我可能要么不执行 ETL 包，要么确保它们的创建以某种方式自动化。
Depending on how many tables you're dealing with it might be easier to set up some SSIS ETL packages that move the data from each of the tables. If you set up package configurations and the schema is the same you should be able to use the same set of packages for all the databases. If you have over 10-20 tables I would probably either not do the ETL packages or I would make sure that the creation of them was automated somehow.
由于您拥有企业版并且具有您提到的数据大小和相同的架构，一种方法可能是使用分区表，1 对于目标服务器上的每个不同源，例如您可以将 [FacilityID] 列用于分区键您提到假设这对于每个源来说都是独一无二的，那么只需执行用于在分区之间移动数据的正常数据移动操作，这将为您提供一个听起来像您所追求的统一表（分区）。
As you have Enterprise Edition and with the data sizes, same schemas you mentioned, one approach could be to use Partitoned tables, 1 for each of the different sources on the Destination Server, Example you could use for the Partition Key the [FacilityID] column you mentioned assuming this is unqiue for each source, then just following normal data move operations that areused to move data between partition, this would then give you a consolidated table (partitioned) you sound like you are after.