CODEWITHSUNDEEP
databasedatabase-designsynchronizationreplicationdatabase-replication
Fernando Leal
Fernando Leal

Reputation: 10145

How to keep tables synchronized in different database?

Problem

I have a problem where I need to synchronize some tables from one legacy database to another simpler database that will be used by an external application.

Attempts

So I started to analyze manual techniques to perform the management of the changes in both bases and replicate them in the others (without foreseeing conflicts and merge).

But I realized that in some ways that I could model, they end up being prone to failures in some scenarios, not counting the amount of extra columns just to control the versions and audit the records.

Question

So I believe there are some approaches, database techniques and modeling that will facilitate this work, and assist in maintaining the updated data in both databases, perhaps even predicting conflicts and merge.

Something you can tell me? Something that works similarly to be based?

Note: Integration can not be performed through the database, it must be performed through webservices. Because they may be different databases.

My question is not about tools, but rather about a modeling that gives me a structure where I can get the records that I have to send to the other database, with as little redundancy as possible.

I accept suggestions for using the external database in non-relational structure (NoSQL).

Upvotes: 1

Views: 531

Answers (1)

Boris Schegolev
Boris Schegolev

Reputation: 3701

In master-master configuration the easiest way is to create triggers on DB level. AFTER INSERT OR UPDATE OR DELETE they would log basic information (table name, PK value, change operation, time, etc.) into a new table (change log).

The change log will then be collected by some job (on application layer). This job will have access to both DBs directly or through an intermediate service layer - depends on your security requirements. It will pull change history from the log table, collect individual rows (by table name and PK value) and will try to send the change to the other DB.

And here we get to the most complicated part of the master-master replication: conflicts resolution.

  • For INSERT operations if the row already exists in the other DB you can try to compare row content and update it instead of inserting a new row. Also, this works better for UUID-based PKs than for sequential keys.
  • For UPDATE operations you may want to check if target DB does not have a change on the same row, too. Otherwise you may end up overwriting changes made by someone else.
  • The amount of conflicts depends on how many changes per time period you have and how fast the job reacts to the new change. In some cases you may be required to implement some notify-listen mechanism so that the job is triggered immediately after the change is committed.

A completely different concept is providing a writer API (service layer) that will write changes into both databases synchronously (will issue COMMIT on both DBs after both DBs accepted the change). Though, this requires changing all applications working with given databases, so I can't tell if it's a viable option for you.

Upvotes: 2

Related Questions