Library Technology - Reviews, Tips, Giveaways, Freeware

Library Technology – Reviews, Tips, Giveaways, Freeware

Incremental Database Synchronization

Posted In Online services - By Techtiplib on Friday, November 3rd, 2017 With No Comments »

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 timeupdate of modified data as both the source and the target databases could be in use constantly.

Incremental Database Synchronization

In software engineering, synchronization alludes to one of two unmistakable however related ideas: synchronization of procedures, and synchronization of information. Process synchronization alludes to the possibility that various procedures are to sign up or handshake at one point, so as to achieve an understanding or focus on a specific arrangement of activity. Information synchronization alludes to keeping various duplicates of a dataset in intelligibility with each other, or to keep up information honesty. Process synchronization primitives are ordinarily used to actualize information synchronization.

String synchronization is characterized as a component which guarantees that at least two simultaneous procedures or strings don’t at the same time execute some specific program portion known as basic segment. Procedures’ entrance to basic area is controlled by utilizing synchronization methods. When one string begins executing the basic segment (serialized portion of the program) the other string should hold up until the point that the main string wraps up. In the event that legitimate synchronization methods are not connected, it might cause a race condition where the estimations of factors might be unusual and fluctuate contingent upon the timings of setting switches of the procedures or strings.

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

  • Insertevery rows from the data source which are missing the destination
  • Delete from the target database every rowsthat’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 improveit 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 approachingthis problem is by iterating only those rows which are already modified since the earlier run of synchronization. It is possible to implementbased on these algorithm: –

  • During the first run,the program performs classical database synchronization as it is mentioned above
  • In addition, the program generatestriggers on insert, delete and even for updates of every table that’s being synchronized. Every triggers write infoabout the modified row into a special service table
  • From the second run, the program retrieves infoabout the modified rows straight from the service table and alsoupdates only those rows

This method is called incremental or trigger-based database synchronization. Now, let’s examine how incremental method can reduce the timefor database synchronizationbased on the example given above. Even with record-by-record processing, incremental method can be executed in near to real time mode if scheduled in proper manner. However, it is also possible to implement bulk rows processing methods because every modified data that is accumulated in one place is grouped by type of operation – insert, delete and update. The bulk rows method of processing gives over 10 times higher the synchronization process.

Incremental or a trigger-based database synchronizationmethod applies these requirements: –

  • Accesstosynchronizeddatabaseshaving sufficient privilegesto createtriggersandtables
  • Every synchronizedtablesshould havea defined primarykey

Author Bio:

Simon Hopes is a renowned author and social media enthusiast. To assess the general advantages of incremental synchronization you can utilize Microsoft SQL – MySQL Sync instrument.

More contents in:

About - Hey, this blog belongs to me! I am the founder of TechTipLib and managing editor right now. And I love to hear what do you think about this article, leave comment below! Thank you so much...