Many organizations that host data locally due to security concerns or regulations, might still choose to have their data stored across multiple databases. This use-case is especially popular in the financial industry, where data must be stored redundantly across multiple databases in the case that a primary database fails.
Not only do databases need to be replicated, this sometimes needs to be done repeatedly or in near real-time. This can be achieved through incremental database synchronization. Incremental synchronization mimics the changes made in a primary database onto a secondary or tertiary database. This can be doing using pre-defined triggers. The only pre-requite for this to work is that the database environments must be identical.
Incremental storages work by first fully synchronizing the tables in each of the databases. During the initial synchronization, it also creates service objects that will copy changes accordingly when subsequent changes are made in the primary database. In any future update, insert or delete in the primary database, an event will be triggered in the secondary database that replicates that change. Then synchronization tool will process only those changes stored in the service table.
There are many advantages of using incremental synchronization over classic backups. Firstly, the synchronization is in real time. If something was to go wrong with your primary database, you would not lose any data. In the conventional model, only data backed up would be available. In this model, data is backed up as it comes, so it is like having two primary databases in theory.
Furthermore, this does not require any downtime and is automatic. Conventionally, it would take many hours for a professional to fully backup a large database. However, with incremental synchronization, there is no need for human intervention except in the initial setup and no downtime.
Another advantage is that it is effective with large datasets, which usually take a lot of time to replicate fully. However, since incremental storage only does a full synchronization one time and only copies changes after that, it can work with a large database without causing any performance concerns. The method of incremental synchronization specified above required both source and destination databases have equal structures, primary keys defined and user has sufficient privileges to create tables and triggers.
Incremental synchronization can be evaluated through MSSQL-MySQL Synctool developed by Intelligent Converters. The program can do bi-directional trigger based synchronization between SQL Server and MySQL databases.
