#database synchronization

In the age of heterogeneous information systems, it is must for multiple applications and business processes to be in sync with databases. And under such condition, the most challenging task is to achieve a real time update of modified data as both the source and the target databases could be in use constantly.

A classic approach to synchronization of database is based on comprehensive scanning of the original and the target databases in order to locate the data that is being changed or modified. It’s only after this that necessary actions are implemented over this data:  

·         Insert every rows from the data source which are missing the destination

·         Delete from the target database every rows that’s missing in the source 

·         Update/upgrade every available rows

One problem of this method is the extremely long synchronization process. For instance, if there are two databases –SQL Server and MySQL, both of which have at least more than one million rows. And let’s assume that the average performance of a classical synchronization process is 100 rows/ second. It is impossible to further improve it by deploying high speed database access components or even the techniques as this method is incapable of bulk manipulations over data. Subsequently, every pass of classical synchronization requires over 2 and a half hours for considered databases which makes it impossible to run on an hourly basis. 

Another way of approaching this problem is by iterating only those rows which are already modified since the earlier run of synchronization. It is possible to implement based on these algorithm: -

·         During the first run, the program performs classical database synchronization as it is mentioned above

·         In addition, the program generates triggers on insert, delete and even for updates of every table that’s being synchronized. Every triggers write info about the modified row into a special service table

·         From the second run, the program retrieves info about the modified rows straight from the service table and also updates only those rows

The approach described above is known as incremental or trigger-based database synchronization. Now, let’s examine how incremental method can reduce the time for database synchronization based on the example given above. Even without using bulk rows processing incremental synchronization can be executed in near to real time scheduling. However, it is also possible to implement bulk copy technique because all modified data that is accumulated in one place is grouped by type of operation - insert, delete and update. This method of data replication gives over 10 times higher performance of the synchronization process. 

Incremental or a trigger-based database synchronization method applies these requirements: -

·         Access to synchronized databases having sufficient privileges to create triggers and tables

·         Every synchronized tables should have a defined primary key