Use trigger-based sync algorithm to replicate data in almost real time. DBSync realizes this by capturing and delivering updates of modified information as the changes occur and providing continuous data synchronization across heterogeneous environments.
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.
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 sync feature implemented in our DBSync 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 syncing and in order to clean your synced databases, extra stage is required.
Adding Triggers and "history_store" table to both 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" table. Notice, that the process of triggers creation can take extra time. Triggers are created in each synchronized table for bi-directional sync.
Note: to fulfill triggers embedding you need to have privileges allowing you to create triggers and tables.
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:03You can see there were some auxiliary information has been added to synched tables after process finish.
Use Triggers for 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.
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.
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:02Remove 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 'Trigger-based sync' option and commit the session. All auxiliary information will be erased from your synchronized databases.
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
0 comments
Please sign in to leave a comment.