Bidirectional Database synchronization

DBSync data synchronization tools support Bidirectional sync and allow having up-to-date data among different databases. This article shows how does the Bi-directional Synchronization Pattern work in screenshots.

Our synchronizers are able to sync data in a heterogeneous environment that spans across databases and networks either in one way from source to target or perform two way synchronization without extra effort from your side.

In case of the most simple one-way sync process programs transfer distinctive data in one direction from Master (Source) to Target (Slave) database. This mode is used, for example, for offloading part of data or a whole database for further analysis and reporting. Another case is using it for backup purposes in the event of failure or maintenance of an active database. Sometimes source and target databases have a different structures and subset of data need to be transferred in one direction. For instance, customers put orders on a e-commerce web site and then the data passed to call center to process these orders.

More info about one-way synchronization process you can find here.

Bidirectional synchronization is used when it’s necessary to keep all synced databases identical with the up-to-date information collected from all nodes. Data is replicated across all nodes, where all of them actively participate in data exchange. This configuration can be used to replicate the same data between several nodes to reduce server loads and for making faster access to multiple web sites from different locations.

Notes:

• Please back up your data before running bidirectional syncing for the first time.
• Check “Bidirectional sync” option at the Customization step and software will perform all the rest automatically.
• Bidirectional synchronization uses triggers to track data changes. Therefore, 'Trigger-based sync’ option has to be checked as well (it is checked by default).
• Requirement: Don’t change time on the Source and Target servers manually as databases' tables will have the most recent data according to the latest changes sorted by time. Anyway, if the server time has been changed, just clear history tables and assign triggers once again.

Bi-directional synchronization is available for the following directions:

• Oracle and MySQL
• Oracle and SQL Server
• MySQL and SQL Server
• SQL Server and PostgreSQL
• MySQL and PostgreSQL
• Oracle and PostgreSQL

Bidirectional synchronization. How it works?

The following example illustrates bi-directional sync process. For the simplicity let's assume that table structures are the same for both tables.

Initially two tables have the following data:

          Table#1                                                         Table#2

Then we change the row #6 firstly in Table#1 then in Table#2

          Table#1                                                         Table#2

In the next step, we modify row #7 in both tables. But firstly changes are made in Table#2, then in Table#1.

         Table#2                                                         Table#1

Now let's start Bidirectional sync. Make sure you have checked options 'Trigger-based sync’ and 'Bidirectional sync'

As a result, we've got identical data in both tables.

           Table#1                                                         Table#2

Both tables have the most recent data according to the latest changes sorted by time. The row # 6 data came from Table#2 and the row # 7 has the data from Table#1.

One-way synchronization. How is it different from Bidirectional sync?

In one-way synchronization, all changes from primary table are transferred to secondary one, but no changes are ever copied back to the primary table.

Let's assume that originally two tables have the following data:

          Table#1                                                         Table#2

Then we change data in rows #6 and #7 in primary table:

         Table#1                                                        

Now let’s carry out one-way synchronization. Note, that 'Bidirectional sync' option is checked off:

As a result, we have the identical data in both tables. In this case data come in one direction only from Table#1 to Table#2.

          Table#1                                                         Table#2