Trigger-based database real-time synchronization

Triggers and "History_store" table creation in synched databases

Triggers in use for real-time database sync

Triggers removal and cleaning of databases

Trigger-based Database synchronization makes it possible to reflect changes made in one database to another databases in a distributed heterogeneous database system based on pre-created triggers.

A new approach of real-time trigger based sync algorithm has been implemented in synchronizer tools. It allows replicating data in near real-time by capturing and delivering updates of modified information as the changes occur and providing continuous data synchronization across heterogeneous environments.

Advantages of the trigger-based synchronization mechanism over classic sync process:

Effective for large dataset volumes. If your database is over than 2 Gb you don’t have to perform complete sync every time. The only recent changes will be reflected in synchronized databases.
Faster speed synchronization. Synchronizers replicate databases in times faster as they process altered records only due to pre-created triggers.
Classic Sync without triggers processes normalized 1 Million records database (Insert/ Update/ Drop synchronization options are ON) for 01 hour 32 minutes.
It takes about 12-13 minutes to transfer the same 1 Million of records with active triggers.

According to the presented data you can easily notice the significant benefit of trigger-based instant synchronization feature implemented in our Sync software.

Near real-time synchronization. The trigger-based sync system allows running sync sessions as often as you need. If the record was altered you can run sync session immediately (or by Scheduler) and the imported data will always "win" over the data in the existing record that is being imported into. So your replicated databases will be up-to-date continuously.
Simple configuration.It is quite simple to configure trigger-based sync and does not require any developer-level technical skills.

The trigger-based sync mechanism is implemented both for one-way and for bi-directional synchronization.

Requirements:

  • You should have direct access to synchronized databases with privileges for creation triggers and tables.
  • Trigger-based synchronization can work only for tables with Primary Key

Below we will enlighten the most important details in the illustrated manual to show the exact options need to be activated. The process of trigger sync configuration is divided into 2 general stages. If you decide to cancel real-time synching and in order to clean your synched databases, extra stage is required.

Adding Triggers and "History_store" to databases

Auxiliary information will be collected for each table participated in sync process. All coming changes will be handles by Insert, Update and Delete triggers and the information is stored in "History_store". Notice, that the process of triggers creation can take extra time. Triggers are created in each synchronized table for bi-directional sync.

To fulfill triggers embedding you need to have privileges allowing you to create triggers and tables.

triggers session configuration 1

triggers session configuration 2

Note: For the first time you should have checked "Copy data" option only, but Options "Insert sync", "Update sync" and "Drop sync" should be unchecked. Otherwise the process will take longer.

Here is the log of triggers creation:

Process started.
            Source database:-- MySQL database --
            Host: 192.168.0.78
            Port: 3306
            User: dmsoft
            Password: Yes
            Database: testSuperBase
            CharSet: utf8
            Use SSH: No
            Use SSL: No

            Destination database:-- MSSQL database --
            Host: 192.168.0.78
            Port: 1433
            User: sa
            Password: Yes
            Database: Test_trigger_1
            CharSet: utf8

            Connecting to the source database.
            Connecting to the destination database.
            Creating history store for testSuperBase
            Creating history store for Test_trigger_1
            Table 'Children' exists. Skipping creation.
            Table 'Cities' exists. Skipping creation.
            Table 'DMSoft' exists. Skipping creation.
            Table 'Jobs' exists. Skipping creation.
            Table 'Students' exists. Skipping creation.
            Synchronizing data in table 'Children'.
            Synchronizing data in table 'Cities'.
            Synchronizing data in table 'DMSoft'.
            Synchronizing data in table 'Jobs'.
            Synchronizing data in table 'Students'.
            Creating trigger 'after_update_Children' for 'Test_trigger_1.Children'
            Creating trigger 'after_insert_Children' for 'Test_trigger_1.Children'
            Creating trigger 'after_delete_Children' for 'Test_trigger_1.Children'
            Creating trigger 'after_update_Cities' for 'Test_trigger_1.Cities'
            Creating trigger 'after_insert_Cities' for 'Test_trigger_1.Cities'
            Creating trigger 'after_delete_Cities' for 'Test_trigger_1.Cities'
            Creating trigger 'after_update_DMSoft' for 'Test_trigger_1.DMSoft'
            Creating trigger 'after_insert_DMSoft' for 'Test_trigger_1.DMSoft'
            Creating trigger 'after_delete_DMSoft' for 'Test_trigger_1.DMSoft'
            Creating trigger 'after_update_Jobs' for 'Test_trigger_1.Jobs'
            Creating trigger 'after_insert_Jobs' for 'Test_trigger_1.Jobs'
            Creating trigger 'after_delete_Jobs' for 'Test_trigger_1.Jobs'
            Creating trigger 'after_update_Students' for 'Test_trigger_1.Students'
            Creating trigger 'after_insert_Students' for 'Test_trigger_1.Students'
            Creating trigger 'after_delete_Students' for 'Test_trigger_1.Students'
            Closing connections.
            Process completed successfully.
            Total time elapsed - 00:00:03

You can see there were some auxiliary information has been added to synched tables after finishing of process

triggers embedding result 1

triggers embedding result 2

Use Triggers for real-time database sync

Created triggers start tracking changes made in databases. You can launch sync process manually anytime you want to replicate your data or configure scheduler to perform synchronization automatically.

"History_store" table keeps changes in your databases.

triggers store

When the session is finished, "History_store" table will be cleaned automatically to be prepared for the next database changes tracking.

Now you can check 3 sync options: Insert, Update and Drop allowing triggers to track all changes.

triggers configuration 2

Sync log should looks like:

Process started.
		Source database:-- MySQL database --
		Host: 192.168.0.78
		Port: 3306
		User: dmsoft
		Password: Yes
		Database: testSuperBase
		CharSet: utf8
		Use SSH: No
		Use SSL: No

		Destination database:-- MSSQL database --
		Host: 192.168.0.78
		Port: 1433
		User: sa
		Password: Yes
		Database: Test_trigger_1
		CharSet: utf8

		Connecting to the source database.
		Connecting to the destination database.
		Table 'Children' exists. Skipping creation.
		Table 'Cities' exists. Skipping creation.
		Table 'DMSoft' exists. Skipping creation.
		Table 'Jobs' exists. Skipping creation.
		Table 'Students' exists. Skipping creation.
		Synchronizing data in table 'Children'.
		Synchronizing data in table 'Cities'.
		Synchronizing data in table 'DMSoft'.
		Synchronizing data in table 'Jobs'.
		Synchronizing data in table 'Students'.
		Closing connections.
		Process completed successfully.
		Total time elapsed - 00:00:02

Remove Triggers and clean databases

If you don’t need real-time synchronization anymore you can remove Triggers and "History_store table" from databases. Just uncheck 'Use history table for synchronization' option and commit the session. All auxiliary information will be erased from your synchronized databases.

triggers remove

The log will be similar to:

Process started.
		Source database:-- MySQL database --
		Host: 192.168.0.78
		Port: 3306
		User: dmsoft
		Password: Yes
		Database: testSuperBase
		CharSet: utf8
		Use SSH: No
		Use SSL: No

		Destination database:-- MSSQL database --
		Host: 192.168.0.78
		Port: 1433
		User: sa
		Password: Yes
		Database: Test_trigger_1
		CharSet: utf8

		Connecting to the source database.
		Connecting to the destination database.
		Removing history store for testSuperBase
		Removing history store for Test_trigger_1
		Table 'Children' exists. Skipping creation.
		Table 'Cities' exists. Skipping creation.
		Table 'DMSoft' exists. Skipping creation.
		Table 'Jobs' exists. Skipping creation.
		Table 'Students' exists. Skipping creation.
		Synchronizing data in table 'Children'.
		Synchronizing data in table 'Cities'.
		Synchronizing data in table 'DMSoft'.
		Synchronizing data in table 'Jobs'.
		Synchronizing data in table 'Students'.
		Synchronizing data in table 'Children'.
		Synchronizing data in table 'Cities'.
		Synchronizing data in table 'DMSoft'.
		Synchronizing data in table 'Jobs'.
		Synchronizing data in table 'Students'.
		Dropping triger 'after_update_Children' for 'testSuperBase'.'Children'
		Dropping triger 'after_insert_Children' for 'testSuperBase'.'Children'
		Dropping triger 'after_delete_Children' for 'testSuperBase'.'Children'
		Dropping triger 'after_update_Children' for 'Test_trigger_1'.'Children'
		Dropping triger 'after_insert_Children' for 'Test_trigger_1'.'Children'
		Dropping triger 'after_delete_Children' for 'Test_trigger_1'.'Children'
		Dropping triger 'after_update_Cities' for 'testSuperBase'.'Cities'
		Dropping triger 'after_insert_Cities' for 'testSuperBase'.'Cities'
		Dropping triger 'after_delete_Cities' for 'testSuperBase'.'Cities'
		Dropping triger 'after_update_Cities' for 'Test_trigger_1'.'Cities'
		Dropping triger 'after_insert_Cities' for 'Test_trigger_1'.'Cities'
		Dropping triger 'after_delete_Cities' for 'Test_trigger_1'.'Cities'
		Dropping triger 'after_update_DMSoft' for 'testSuperBase'.'DMSoft'
		Dropping triger 'after_insert_DMSoft' for 'testSuperBase'.'DMSoft'
		Dropping triger 'after_delete_DMSoft' for 'testSuperBase'.'DMSoft'
		Dropping triger 'after_update_DMSoft' for 'Test_trigger_1'.'DMSoft'
		Dropping triger 'after_insert_DMSoft' for 'Test_trigger_1'.'DMSoft'
		Dropping triger 'after_delete_DMSoft' for 'Test_trigger_1'.'DMSoft'
		Dropping triger 'after_update_Jobs' for 'testSuperBase'.'Jobs'
		Dropping triger 'after_insert_Jobs' for 'testSuperBase'.'Jobs'
		Dropping triger 'after_delete_Jobs' for 'testSuperBase'.'Jobs'
		Dropping triger 'after_update_Jobs' for 'Test_trigger_1'.'Jobs'
		Dropping triger 'after_insert_Jobs' for 'Test_trigger_1'.'Jobs'
		Dropping triger 'after_delete_Jobs' for 'Test_trigger_1'.'Jobs'
		Dropping triger 'after_update_Students' for 'testSuperBase'.'Students'
		Dropping triger 'after_insert_Students' for 'testSuperBase'.'Students'
		Dropping triger 'after_delete_Students' for 'testSuperBase'.'Students'
		Dropping triger 'after_update_Students' for 'Test_trigger_1'.'Students'
		Dropping triger 'after_insert_Students' for 'Test_trigger_1'.'Students'
		Dropping triger 'after_delete_Students' for 'Test_trigger_1'.'Students'
		Closing connections.
		Process completed successfully.
		Total time elapsed - 00:00:09
comments powered by Disqus