simonhopes
A few projects and systems need supervision with multiple databases. It is recommended to have all these databases up-to-date and this is the purpose of synchronization procedure. Generally, synchronization includes the insertion of missing records, update of existing records and deletion of data that doesn’t exist anymore.
Incremental sync allows reflect modifications made in one database to another database in a distributed DBMS according to pre-created triggers. It gives replicating data the opportunity to capture in real-time and deliver updates of modified data as the changes take place and offering consistent data synchronization across heterogeneous environments.
Understanding how it work
Incremental synchronization functions in accordance to the following scheme. Whenever you are maintaining a synchronization for a couple of specific databases in the beginning:
- the software does full synchronization for all selected tables from source and destination databases (inserts into destination table missing rows from the source, updates existing rows and deletes from destination table all rows missing in source table)
- the software creates all essential service objects for incremental synchronization in next run (table to store changes and triggers on insert, update and delete for each table being synchronized)
Whenever you are trying to run a synchronization in the future, have it at the back of your mind that the program processes only data that gets modified in each table using incremental synchronization technique mentioned above.
Benefits of the trigger-based synchronization system over conventional sync process:
It is effective for big dataset volumes. If you are working with a database that is more than 2 Gb, you don’t need to execute complete sync all the time. The main current changes is going to be reflected in synchronized databases.
Faster speed synchronization. Synchronizers reproduce databases in periods faster because they process altered records only as a result of pre-created triggers.
Let’s prove the advantage of incremental synchronization with the following illustration. Suppose, the database includes table with 1 Million rows and only 1000 rows has been updated since the last synchronization. Classic approach to synchronization (Insert/Update/Drop operations) takes about 2,5 hours for that table. Incremental synchronization allows to do the same procedure within 1-2 minutes.
In accordance with the presented data, it is possible to take note of the important advantage of trigger-based sync function applied inside our Intelligent Converters software.
On-the-fly synchronization. The trigger-based sync program makes it possible for running sync sessions as frequent as you require. In case the record was modified, it is possible to run sync session immediately (or by Scheduler) and the imported data will certainly “win” over the data in the existing record that’s being imported into. Which means that your replicated databases will be up-to-date continuously.
Simple configuration. It is extremely easy to manage trigger-based sync because it doesn’t call for any developer-level technical skills.
The trigger-based mechanism of incremental synchronization is appropriate for both one-way and bi-directional synchronizations.
Requirements:
- You need to have direct access to synchronized databases with privileges for creation triggers and tables.
- Trigger-based synchronization can function only for tables with Primary Key
Experiment with MS SQL – MySQL synchronization tool to find out how incremental synchronization works on real data.
