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

Question

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 1

You can't. Your only solution is to have only one master. You should never split an application into different databases, as you already have the problem even without replication. You cannot restore both A and B to a consistent state. The database is your unit of recovery, and it should never be split into separate entities.

Your best option is two go back to the drawing board and redesign your application that it only keeps state in only one database, like all applications should. If you can't accomplish this, then you're going to have to give up consistency on the replicas.

Answer 2

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:

1. Add a trigger on "Lines" table, to modify "Order" record time-stamp (last_line_time)
2. 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.

Answer 3

Why dont you create views joining tables with appropriate states from Database A & Database B in say Database C which will have synchronised data and then replicate them ? I think this way you would have consistent data.