问题描述
限时送ChatGPT账号..我们正在运行 SQL Server 2008 R2.我们的操作软件针对这个数据库运行，我们将运行两个单独的操作软件实例，访问两个单独的数据库.两个数据库都在同一个 SQL Server 上.有一个包含 26 个表的列表，数据库中有 200 多个表，需要在两个单独的数据库之间同步.可以从任一数据库访问表，并且插入/更新/删除必须在两个数据库之间同步.同步需要近乎实时地发生，不少于每分钟一次.
We are running SQL Server 2008 R2. Our operational software runs against this database and we are going to run two separate instances of the operational software accessing two separate databases. Both databases are on the same SQL Server. There are a list of 26 tables, out of 200+ in the database, that will need to be synced across the two separate databases. The tables can be accessed from either database and insert/update/deletes must be synced across both databases. The sync needs to happen near real-time, no less than once per minute.
我知道我可以硬编码触发器，使用 MERGE 来更新表，但这意味着编写 52 (26 x 2) 个单独的触发器，这些触发器具有所有细节硬编码.
I know I can hard code triggers, using MERGE to update the tables, but that means writing 52 (26 x 2) separate triggers that have all of the detail hard coded.
我已经研究了复制，但这似乎更适合一个主表提供一个子表，而不是双向更新.
I've looked into Replication, but that seems to be more geared toward one master table feeding a child table, not two way updates.
我研究了更改跟踪，但并非所有表都定义了 PK.这是供应商的数据库，不是我的.我知道所有的桌子都应该有 PK 的.我不知道我可以定义 PKs在桌子上.
I've looked into Change Tracking, but not all of the tables have PK's defined. This is the vendor's database, not mine. I know all the tables should have PK's. I don't know that I can define PKs on the tables.
我的另一个想法是用同义词替换其中一个表，以便两个系统实际上更新同一个表.我对该解决方案的担忧是破坏"操作系统的可能性.
My other thought was to replace one of the tables with a synonym so that both systems are actually updating the same table. My concern with that solution is the possibility of "breaking" the operational system.
我刚刚开始研究 Microsoft Sync Framework 以及它是否是一个选项.
I've just started researching Microsoft Sync Framework and whether that is an option.
任何想法、建议、方向将不胜感激.
Any thoughts, suggestions, direction would be greatly appreciated.
谢谢，戴夫
推荐答案
这是一个 复制场景.事务复制 可以处理从订阅服务器到发布服务器的更新，但在在这种情况下，您需要合并复制，这在合并冲突时更好订阅者更新回发布者.
This is a Replication scenario. Transactional Replication can handle updates from the Subscriber back to the Publisher, but in this case you want Merge Replication, which is better at merging conflicts when a Subscriber updates back to the Publisher.
你需要PK.如果您有自动增量 IDENTITY 字段，那么这些将是一个问题.有划分 id 范围的策略，例如为每个数据库中的表版本设置不同的 Identity Seed 值.
You'll need PKs. If you have autoincrement IDENTITY fields, those are going to be a problem. There are strategies for partitioning id ranges, such as setting different Identity Seed values for versions of the table in each database.
如果不能对数据库进行任何更改，则此方法可能无法满足要求.复制是做这种事情的正确方法，但它确实需要一些力量来改变结构.
If you can't make any changes to the database, this approach probably can't meet the requirements. Replication is the right way to do this sort of thing, but it does require some power to change the structure.
这篇关于如何在两个数据库之间双向同步表 - SQL Server 2008 R2的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
更多推荐
[db:关键词]
发布评论