CODEWITHSUNDEEP
javasqldatabaseoptimizationjdbc
guru
guru

Reputation: 61

efficient method to sync up two tables in different servers with java

we have a source table which is updated from various external systems. i require the destination table (in different server) to be in sync with this source table. the destination table is not an exact replica of the source table, some data processing has to be done before the data is inserted/updated into destination table.

i have thought of the following logic

every 15min we run this java consumer code which fetches the records where the timestamp is created than that of previous update and stored in a CachedRowSet. and call a stored procedure with CachedRowSet as parameter, where the data processing is done and the data is inserted/updated into the destination table.

do you believe above mentioned is an efficient way as we are dealing over a million records every update ?

also when a record is deleted in the source table in would not be replicated in the above method ! can you suggest what to do in such scenario

Upvotes: 4

Views: 4721

Answers (3)

chubbsondubs
chubbsondubs

Reputation: 38706

Here are some existing solutions:

https://www.symmetricds.org/

http://opensource.replicator.daffodilsw.com/

Upvotes: 0

Joshua Martell
Joshua Martell

Reputation: 7212

You might want to check out mk-table-sync from Maatkit tools:

http://www.maatkit.org/doc/mk-table-sync.html

You'd need to be careful around your table differences.

Upvotes: 0

sudmong
sudmong

Reputation: 2036

Something similar to technique used by database for save point and rollback. Whenever there is some change in the source table e.g. CRUD. keep the scripts of change as per format required to the target table. periodically you can push those changes to the target server. As your source table is updated by various external system, you'll need to have trigger on your source table for keeping script logs.

Upvotes: 1

Related Questions