Bidirectional Database synchronization
DBSync database synchronization tools support bi-directional synchronization and keep data up-to-date between different databases. This article demonstrates how the bi-directional sync pattern works.
In the most straightforward one-way synchronization process, programs send data in one direction from the source to the target (destination) database. However, sometimes the source and target databases have different structures, or just a subset of the data needs to be transferred.
In one-way synchronization, all changes from the primary table are carried over to the secondary, but no changes are ever copied back to the primary table.
Find more information about one-way database sync.
Bidirectional synchronization
Database Sync tools synchronize data across heterogeneous environments spanning different databases and networks, either in one direction from source to target or perform two-way synchronization with no extra effort on your part.
Bidirectional synchronization maintains the identity of all synchronized databases with up-to-date information collected from both the source and target databases. The data is replicated to all nodes, where they all actively participate in the exchange of data.
How does it work?
- Triggers (Insert, Update, Delete) are initially created to track changes in each table.
- When the "insert, update or delete a record" event occurs, the corresponded trigger is fired. This record goes to the previously generated service tables "history_store."
- Once the DBSync process starts the next time, it reads the records from this "history_store" in the source and destination and synchronizes these records in both the origin and destination DB.
The following example shows a bi-directional synchronization. For simplicity, let's assume that the table structures are the same for both tables.
Initially, two tables contain the following data:
Let's change line # 6 first in Table # 1 and then in Table # 2.
In the next step, we change line # 7 in both tables. But first, changes are made to Table#2, then to Table#1.
Now let's start with bi-directional synchronization. Make sure you have checked the Trigger-Based Synchronization and Bidirectional Synchronization options.
As a result of synchronization, we got identical data in both tables.
Both tables have the most recent data according to the latest changes sorted by time.
The row #6 came is taken from Table #2 and
the row #7 has the data from Table #1.
Notes:
- Back up your data before starting bi-directional synchronization for the first time.
- Make sure you've checked the Bidirectional Sync box in the configuration.
- Bidirectional synchronization uses triggers to track data changes. Therefore, it is also necessary to have checked the 'Trigger-based sync' option.
- Requirement: Do not manually change the time on the source and destination DB servers, as the database tables will have the most recent data according to the most recent changes, sorted by time. Anyway, if the server time has changed, clear the history tables and reassign the triggers.