Quite a few projects and solutions call for handling of several databases. You will need to have the data up-to-date which is the goal of the synchronization. In most cases, synchronization is insert of missing records, update of existing records and delete of data that doesn’t exist anymore.
Incremental synchronization enables us to reflect changes created in a database to another one within using dedicated previously created triggers. This approach leads to near real-time data replication by collecting the modified information and implementing continuous data synchronization.
Understanding how it work
Incremental synchronization works with respect to this particular scheme. When running synchronization for just two particular databases in the beginning:
- the program does full synchronization for every of the selected tables from source and destination databases (inserts into destination table missing rows within the source, updates existing rows and removes all rows that do not exist in source table)
- the program creates all necessary service objects for incremental synchronization in next run (table to save changes and triggers on insert, update and delete for every table being synchronized)
Next time when running synchronization, the tool processes only data being changed using incremental synchronization method specified earlier.
Benefits of the incremental synchronization over traditional approach:
- Effective for big dataset volumes. If your primary database is more than 2 Gb, you don’t need to perform complete sync each and every time. The only recent modifications will likely be indicated in synchronized databases.
- Faster speed synchronization. Synchronizers replicate databases in times faster since they process altered records only as a result of pre-created triggers.
Let’s illustrate advantage of incremental synchronization on the following example. Imagine, the database includes table with A Million rows and just 1000 rows appears to have been updated as early as the last synchronization. Classic method of synchronization (Insert/Update/Drop operations) takes a couple of,5 hours for the table. Incremental synchronization allows to complete exactly the same procedure within 1-2 minutes. Based on the presented data, it is simple to feel the significant advantage of incremental sync feature implemented in our Intelligent Converters software.
- Near real-time synchronization. The trigger-based sync system allows running sync sessions as frequently as you want. In case the record was altered, it is possible to run sync session immediately (or by Scheduler) as well as the imported data will forever “win” over the data within the existing record that’s being imported into. So that your replicated databases would be up-to-date continuously.
- Simple configuration. It is extremely easy to configure trigger-based sync and doesn’t require any developer-level technical skills.
The trigger-based mechanism of incremental synchronization is perfect for both one-way and bi-directional synchronizations.
Requirements:
- You need to have immediate access to synchronized databases with privileges for creation triggers and tables.
- Incremental synchronization can work just for tables with Primary Key
Check out MS SQL and MySQL database synchronization tool to find out how incremental synchronization operates on real data.