Database synchronisation - how do you integrate different systems?
The clients: BAA and Artesis Ltd wanted to connect two systems running separate databases for a monitoring system for Heathrow Airport.
One system was built on an Access database (not a modern and scalable solution like SQL), so the client was facing a major problem. To redesign the system to work with SQL instead of Access would be very complicated and expensive, and beyond the budget and time for the project.
To solve the problem, we created a way to integrate the two systems with a tool synchronising the two types of database in real time (so they would be running a mirror of the data in parallel).
This “crude” solution took only a fraction of the time and cost to develop and saved the project for BAA. Another benefit was that the systems were left unmodified, providing a stable working system without additional modifications and stability tests
DB-Synchroniser allows for continues synchronising of data between Databases in SQL or MySQL and MS Access DB. The software's functions include automated fast synchronisation from SQL to Access (running in the background and synchronising the SQL DB and Access DB), Full Synchronisation SQL to Access (this function clears all the Access tables and copies the entire content of the SQL tables selected for that process) and Access to SQL synchronisation.
The program allows the synchronising of data between MySQL DB and MS Access DB in both directions. The following synchronisation types are possible:
- Full database synchronisation - means that whole table from source DB is copied to selected destination DB without any restrictions. This operation takes a long time and should be used sparingly.
- Continues database synchronisation - possible only between MySQL and the MS Access database: the algorithm is described below.
Copy files to destination directory and run exe file - the program is using the configuration file from the "config" directory. Any errors and detailed information about program working can found in ".\log" directory.
Db-Synchronizer.exe without a parameters program will be run in standard mode. Db-Synchronizer.exe -debug can be used for detecting configuration problems (when the database tables have not been synchronised or with any other unexpected behaviour). The time of the synchronisation process will depend on the speed of the network connection.
Full synchronisation tables are copied from beginning to end of the table without any limitations. Quick synchronisations from the source table are copied but not empty 100 rows starting from end of table. If row does not exist in destination table the synchronisation will continue, if the row exists after 100 rows the synchronisation is finished.
XML Configuration File
All configuration used by the process is stored in special XML files.