0

I have a central DB and three remote client DB, say RDB-A, RDB-B and RDB-C. Main DB is implemented on a SQL Server Standard instance and all the RDB on different SQL Express instances. The RDB-X are not located in the same place, rather are mobile and replication is implemented using the Merge-Web Sync method.

Each RDB-X uploads and downloads specific table rows not shared with other RDB. Basically, main DB holds the union of data in all RDB. Synchronization process between DB and each RDB takes place once every four or five days.

Let's say a user on a remote DB-A process an entry and this entry is replicated to the main DB. A user on main DB process the same entry in response, but at the same time the user on remote DB-A re-edits the same entry, making some corrections. What would be the result on the next replication?

How is it possible to make the data correspondence consistent in such a time-lapse replicated DB scheme?

I was thinking to impose something like a check boolean on the row to "interlock" further editing from the same user (remote or main) until the entry has been "viewed" or "edited-back" by the other user. The main problem is the time lapsing between replications.

2
  • What version of SQL Server are your instances? To be clear, are you actually using the Merge Replication feature and if so, how is it time lapsed?...it should be near real-time.
    – J.D.
    Apr 23, 2023 at 13:19
  • Version is 2016 and the replication is not real time because the RDBs are connecting only once every three or four days or more due to comms problems.
    – db-hopper
    Apr 23, 2023 at 15:21

1 Answer 1

0

There is a big article about it:

https://learn.microsoft.com/en-us/sql/relational-databases/replication/merge/advanced-merge-replication-conflict-detection-and-resolution?view=sql-server-ver16

So, assuming your central DB is a "publisher", and RDB-A is a "subscriber", then in case of the conflict - editions made on RDB-A would be forgotten.

2
  • The article is well-known and the design was based on this, but we want to overcome the authority of "publisher". The application is like a "messenger" but with distributed clients that are not online all the time, but they post their comment whenever they wish.
    – db-hopper
    Apr 24, 2023 at 16:17
  • 1. Forbid publisher to make changes. If only subscriber does them - no conflicts. 2. Use a "resolver", start reading here: learn.microsoft.com/en-us/sql/relational-databases/replication/… 3. Switch to a different ETL (up to a homebrew system) with your own conflict resolution approach
    – White Owl
    Apr 24, 2023 at 20:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.