0

I have two databases on different servers. Each one of them contains 350+ tables and users have read and write access on both databases.

I want to synchronize them, but in special cases, like if a specific record is updated on both databases, I have to be able to prioritize one of updates over the other.

One of the databases is always online and the other one is never online and only has access to local network of organization, synchronization happens everyday at a specific time by downloading all files from internet and uploading them on local net and reverse.

I have considered merge replication and trigger-based replication, but I'm open to any other solutions.

I can't choose the best solution for this task, I really appreciate your advice.

4
  • 2
    What solutions did you consider so far, and what prevents you from choosing one of them? What is the criterion that determines if a solution is "best"?
    – mustaccio
    Commented Mar 9 at 13:06
  • How often or close to real-time does the data need to be synchronized between the two?
    – J.D.
    Commented Mar 9 at 15:38
  • @J.D. Everyday and probably at least 1 million records should be updated.
    – hanie
    Commented Mar 10 at 4:42
  • @mustaccio I updated the question, but I was wandering, is there a possible way to use backups for this situation?
    – hanie
    Commented Mar 10 at 4:45

1 Answer 1

0

I want to synchronize them

If they aren't currently synchronised, and if the application hasn't been specially-designed with this two-way synchronisation in mind, then it may be a mistake to try.

You've already recognised that there may be changes on each side that turn out to be conflicting.

You say you intend to "prioritise" one side over the other, but it is often a downright logic error to assume the data on one side is more correct than the other (when in principle the system accepts inputs from both sides).

Often, correct conflict resolution requires human intervention to understand the situation as a whole, to understand which change should take priority, and to understand what steps might need to be taken (including who needs to be notified) as a result of there having been a conflict.

The difficulty of engineering both the application and the business processes to resolve these conflicts properly, and the prospect of requiring additional human labour to do so, almost invariably leads to an approach being considered which instead resolved conflicts automatically but leads to a built-in process of data loss.

This loss occurs as the conflicting data from the non-preferenced side is simply clobbered and overwritten by the "synchronisation" process. This is likely to be outside all the normal processes which were coherently designed into the application by a competent development team and proven by tests.

No competent software developer would accept an application being designed to randomly lose data on an ongoing basis or silently undo changes made by users, yet when it comes to synchronisation proposals, you can often be dealing with proponents who think it is reasonable.

It also creates difficulties for those who actually maintain the data, because for those with such responsibilities, you cannot just make a change to the records anymore and rely upon it, but there is instead a long period during which there is a latent open transaction that may be effectively rolled back if it is found to be in conflict. This kind of application behaviour would be thought ludicrous, in any context except perhaps by those proposing a synchronisation.

How you maintain the integrity of audit trails and record versions can be another question. Often, audit trail data might be turned into completely unreliable junk by a badly-designed synchronisation process.

Then you have the problem of development and testing. You'd need an enormous test environment, so what might instead occur is that somebody just hacks together some scripts that connect two production databases. The necessary algorithms for synchronisation are often non-trivial, once they are designed to cope with various kinds of delay or failure in execution.

Then you have to think about disaster recovery, and how consistency is restored across the system when potentially one side is recovered from a backup and reversed to a previous state in time (which could very well disrupt synchronisation).

And there is a question of whether there is a quiescent period overnight, and how long it takes to make comparisons for conflicts and apply changes. If both sides are online and accepting input at the same time as synchronisation occurs, its very much more complicated to reason about than if both sides go offline, because there are many more concurrency concerns when the system is online rather than in an overnight batch mode.

Another term for this two-way synchronisation is "eventual consistency", and it's recognised as a difficult area.

I'd think very carefully about whether the synchronisation is necessary and whether alternatives have been considered (such as better telecoms that allow the database to be fully-centralised, rather than maintaining two synchronised copies).

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.