我们可以通过哪些方式将数据级联到多个目录？
我有 10 个 SQL Server。每台服务器上都有一个目录 MASTER_DATA。该目录有一个名为“Employees”的表。每当员工信息发生变化时，它就会在 CENTRAL 服务器上的 MASTER_DATA 目录中更新。
现在我要做的是将Employees 表中的更改级联到所有服务器上的所有MASTER_DATA 目录。此后，相同的更改需要级联到所有服务器上的所有其他目录（MASTER_DATA 目录除外）。
我有以下选项来执行此操作
- SSIS 包
- 复制
- 普通旧 TSQL 查询
执行此操作的最佳方法是什么？另外，还有其他方法可以做到同样的事情吗？
I have 10 SQL Servers. On every server there is a catalog MASTER_DATA. This catalog has a table called Employees. Whenever there are changes in the employee info it gets updated on the CENTRAL server in the MASTER_DATA catalog.
Now what I have to do is to cascade the changes in the Employees table to all the MASTER_DATA catalogs on all servers. After this, the same changes needs to be cascaded to all the other catalogs (other then the MASTER_DATA catalog) on all the servers.
I have the following options to do this
- SSIS Packages
- Replication
- Plain Old TSQL Queries
What would be the best way to do this? Also, are there any other ways to do the same?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
根据您提供的信息，并假设“目录”指的是“数据库”，这似乎是 事务复制。
您的 CENTRAL.MASTER_DATA 数据库将是发布者；所有其他数据库都将是订阅者。
从您的描述中不清楚为什么需要第二层复制 - 即为什么每个非 MASTER_DATA 数据库需要自己的
Employees表副本 - 是否有理由不让查询引用本地MASTER_DATA 数据的副本？您可以在非 MASTER_DATA 数据库以避免更改查询。Based on the information you have provided, and assuming that by "catalog" you mean "database", this seems like an ideal use-case for transactional replication.
Your CENTRAL.MASTER_DATA database would be the publisher; all the other databases would be subscribers.
It's not clear from your description why the second tier of duplication is required - i.e. why each non-MASTER_DATA database requires its own copy of the
Employeestable - is there a reason not to have queries refer to the local MASTER_DATA copy of the data? You could use a synonym in the non-MASTER_DATA databases to avoid having to change your queries.