How to synchronize N processes?

How to synchronize two (or n) replication processes for SQL Server databases?

• 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. 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. 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? UPD: 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?

• Answer:

  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. So, variables that left: Additional "injections" to database Temporal Tricks Triggers Tricks "Late binding" of changes, that replicated not in time Currently, I have found only one idea, that seems to work: Add a trigger on "Lines" table, to modify "Order" record time-stamp (last_line_time) In replica, wait until a Line with time, equals to last_line_time to appear. If max(lines.line_time) > order.last_line_time than order is obsolete If max(lines.line_time) < order.last_line_time than lines are obsolete If max(lines.line_time) == order.last_line_time than everything is OK, for now :) But, this case can fail into infinite loop, if Lines are constantly modified, and Lines table replica always lag behind.