Best way to synchronize data between two different databases

Question

I have to implement data synchronization between two big databases which have completely different structures. Basically, I need to grap some data about products in different tables in the first database and re-arrange them for other tables in the second database.

Creating my products on the first time is not very complicated. But I'm looking for a way to update some specific data - not all data - about each product.

Obviously, there are a few issues that make this tricky.

• I'm not allowed to do anything on the source database apart select queries.
• On the target database, I can do usual queries (select, update, insert, create) but I can't modify the existing structure/tables.
• Target and source db have completely different structures, tables are not the same at all, therefore data really have to be rearranged - comparing tables won't work.
• Target database uses a MySQL server - source may be DB2.
• There are no "updated time" fields anywhere.

So the whole process needs to be done in a single Python (ideally) script.

I think about creating a hash for each product, based on the fields to update in the target database: md5( code + description + supplier + around 10 other fields). A new hash based on the same data will be created on a daily basis from the source database. I will store all hashes in a single table ( item code, current_hash, old_hash ) for performances purpose. Then compare and update the product if the new hash is different from the old one.

There are around 500 000 products so I'm a bit worried about performances.

Is it the good way to go?

Answer 1

This is pretty much what I have been doing or a living the past few years, and my gut instinct is that the time to read 500,000 items from the source database and sync in the destination will not take as much time as one might think and the time taken to read the "key" fields, compute the MD5 hash, and cross check with your table to avoid syncing items that haven't changed won't end up saving too much time and may even run longer. I'd simply read all and update all. If that results in a runtime that is too long, then I'd compress the runtime by making the ETL muti-threaded, with each thread only operating on a segment of the table but working in parallel.

It would be important to ensure that your destination database has a primary key index or unique index. Otherwise, each of your updates/inserts could lock the entire table. This would be bad if you are taking the multithreaded approach, but important even if you are remaining single-threaded because your job could lock the destination DB table and interfere with the application that rides on top of that DB.

You say the source DB "may be DB2". When you say "may" it implies that DB is still being designed/planned? DB2 9 or above does have built-in tracking of last update time, and the ability to query and get back only the items that have changed since a point in time. Perhaps this is why the DB was designed to not have a column indicating the last updated time, eg:

SELECT * FROM T1 WHERE ROW CHANGE TIMESTAMP FOR TAB t1 > current timestamp - 1 hours;

The timestamp cutoff for the above query would be the last timestamp your sync ran.

If this is the case, that should solve your problem. But, your solution would end up being tied very tightly to DB2 and in the future they may like to move to another DB platform and expect your sync job to not need to be re-visited. So it would be important to make sure all the right people know that your product will be dependant on remaining on DB2, or if they plan to migrate that migration would include restructuring the DB to have a "last changed timestamp" column, and make whatever changes necessary at the app level to populate that field.

Answer 2

The data sync would be much better and faster, if it can be done on the basis of some kind of delta identifier or flag. Basically, you should update the target db data rows only when it is out of sync with the source db.

In SQL server db, you can take the help of the Checksum fn also to build the delta based identifier.

You should develop a SQL based job to get invoked at a certain time of the day or night to get this sql logic fired. It is better to run it as a nightly SQL job, when the db usage is very low. If the delta of the source and the target db records does not match, then pull those records only. But the downside would be to calculate the checksum of the source data rows every time and then compare it with the target data.

If you have a column like "LastModifiedDate" in the source db tables, then you can skip the checksum approach. This way, your evaluation will be executed on the date based column and takes less time in comparison to the checksum approach.

Answer 3

Using a hash is a good idea. Since security is not the goal in this case, choose a hash function that is fast (md5 is fine).

Unless you plan to split the hash calculation across multiple threads/processes, you don't really need to store the current hash value in the database. If your process is a single script, you will just have the current hash in memory, and will write it to the database as the old hash after you have updated the data in the new database.

Answer 4

you should have create a windows service which will run at some speficif times whenever you want and it will find the changes in your source database and insert that changes in your destination database.