如何同步 SQL Server 数据库的两个(或 n 个)复制进程? [英] How to synchronize two (or n) replication processes for SQL Server databases?

问题描述

通过标准事务复制更新了两个主数据库和两个只读副本.需要从两个只读数据库映射一些实体，假设 A 数据库包含订单，B 数据库包含行.

There are two master databases and two read-only copies updated by standard transactional replication. It is needed to map some entity from both read-only databases, lets say that A databases contains orders and B databases contains lines.

问题是复制到一个数据库会滞后于复制第二个数据库，并且在映射R-databases的时候会有不一致的数据.例如.

The problem is that replication to one database can lag behind replication of second database, and at the moment of mapping R-databases will have inconsistent data. For example.

我们在 19:00 和 19:03 存储了 2 个订单.映射过程从 19:05 开始，但到映射的那一刻，A 数据库复制处理了 19:03 之前的所有更改，而 B 数据库复制仅处理了 19:00 之前的更改.映射后，我们将有订单实体为 19:03 的订单和 19:00 的订单.麻烦有保障:)

We stored 2 orders with lines at 19:00 and 19:03. Mapping process started at 19:05, but to the moment of mapping A database replication processed all changes up to 19:03, but B database replication processed only changes up to 19:00. After mapping we will have order entity with order as of 19:03 and lines as of 19:00. The troubles are guaranteed:)

在我的特殊情况下，两个数据库都有时间模型，因此可以为每个时间片获取数据，但问题是确定最近复制的时间.

In my particular case both databases have temporal model, so it is possible to fetch data for every time slice, but the problem is to identify time of latest replication.

问题:如何同步多个数据库的复制进程以避免上述情况?或者，换句话说，如何比较每个数据库中上次复制的时间?

Question: How to synchronize replication processes for several databases to avoid situation described above? Or, in other words, how to compare last time of replication in each database?

更新:

我看到的唯一同步方法是不断地将时间戳写入每个数据库的服务表中，并在复制的服务器上检查这些时间戳.这是可接受的解决方案吗?

The only way I see to synchronize is to continuously write timestamps into service tables in each database and to check these timestamps on replicated servers. Is that acceptable solution?

推荐答案

看来，给定的任务无法在给定的约束下解决.如果我理解正确，数据库的数量和行的架构是常量.

It seems, that given task can't be solved in given constraints. If I understood correctly, number of databases and row's schema are constants.

所以，剩下的变量:

• 对数据库的额外注入"
• 时间技巧
• 触发技巧
• 未及时复制的更改的后期绑定"

目前，我只发现了一个似乎可行的想法:

Currently, I have found only one idea, that seems to work:

1. 在Lines"表上添加触发器，以修改Order"记录时间戳(last_line_time)
2. 在replica中，等到一个Line with time，等于last_line_time出现.
   • 如果 max(lines.line_time) > order.last_line_time 则 order 已过时
   • 如果 max(lines.line_time)
   • 如果 max(lines.line_time) == order.last_line_time 一切都好，现在:)

但是，这种情况可能会陷入死循环，如果 Lines 不断修改，Lines 表副本总是滞后.

But, this case can fail into infinite loop, if Lines are constantly modified, and Lines table replica always lag behind.

这篇关于如何同步 SQL Server 数据库的两个(或 n 个)复制进程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！