Synchronizing Two Databases

[aryan Sharma]

i have an application which maintains contacts. now contact management can be done through web which has a different database or can be done through a handheld device which again has a different database. now though semantically both DBs are same but they are different DB servers (and we cannot have one DB for both the interfaces for some reason). now i have to design an application which would sync both the DBs. How should i go about it. it obviously needs to merge, add, and even delete contacts. i mean in case from web you edit a contact but from the handheld you delete that same contact it has to handle such scenarious. can someone give any indicators... i thought that when the sync application is run i could create a snapshow of both the DBs and compare them and update both synchronously. but in this case what happens if the DB is changed while this sync job is running... also i am confused as to how i would 'compare' the DB snapshots....

[Jeanne Boyarsky]

Do you know what the reason is to use two databases? Because it is creating a ton of extra work.

[aryan Sharma]

yes there's some reason which i would not like to go into but any ideas as to how to do this sync job ?

[Jeanne Boyarsky]

Valid approaches are going to deal with constraints you have. For example, can the program read from both databases? How accurate does it need to be? How do you plan to deal with conflicts?

[Ravi Kiran Va]

when you are going for two databases synchronization issues ,i would suggest to go for JTA Suppourt to achive this .

[aryan Sharma]

JTP and all is fine actually the question is how exactly to achieve it. how do design this sync process. as for the other question handhedl device will only read its own DB and web its own... and yes, they need to be consistent right... i mean otherwise it would not serve any purpose

[Ravi Kiran Va]

From JTA , within the same transaction achive this (Either deleteing , editing or adding )
, so that it maintains a synchronization of two databases .

[aryan Sharma]

thats precisely my question. how would you 'sync' the two DBs ...how would you check what all attributes are changed/added/deleted etc..

[Vikram Saxena]

There are cases where we are using multiple DB's

There could be multiple approaches to handle this, but what we use is
1. JTA as already mentioned
2. XA datasources.

The theory behind this approach is that when JTA is managing the transactions, it will first check the databases if they are in a state of commit, for every transaction (good case) , this will ensure that if a particular resource (database) will not be able to commit the JTA will rollback the transaction, and hence the databases will be in sync even if one of the databases as in your case would have committed successfully.

I hope it makes sense

[Paul Sturrock]

Ravi Kiran Va wrote:when you are going for two databases synchronization issues ,i would suggest to go for JTA Suppourt to achive this .

This is fine if your application is the only one that updates the databases. If there are other mechanisms whereby the databases can be updated it is not enough.

Some databases provide tools to maintain a schema that spans two databases. Assuming both databases are the same type (e.g. both Oracle) you could do this, though if they are you have to wonder why they are two separate databases at all. What database are you using?

[Ravi Kiran Va]

If there are other mechanisms whereby the databases can be updated it is not enough.

seems to be interesting , could you please tell me a scenario of such a requirement .
Thanks

[Paul Sturrock]

If any other client updates your database.

[Ravi Kiran Va]

If any other client updates your database.

I did not get the above ,any link which explains this scenario ,

[Paul Sturrock]

Your application can maintain the integrity of the databases using XA transactions, however all clients must behave the same for this to always be guaranteed. Its a mistake to trust the client to maintain the integrity of the database. If someone opens a database client and updates some data in one database, they are now out of sync. To properly do this you need something like a linking mechanism between the two, which is why it is so very hard to do (and why no one would choose to use two databases without very good reason).

[aryan Sharma]

i think we are deviating from the point here.. my problem is that how do i identify what all records and attributes have changed...

[Paul Sturrock]

Well, that's a different question from the one you asked. Some database (Oracle for example) provide auditing capabilities. If your database doesn't have such capabilities, you'll need to write your own using triggers, or some similar mechanism.

[aryan Sharma]

ok and once i get the changes that were done in the two DBs... how do i compare them.. how would i come to know which should be the latest changes and thus should overwrite the other changes done in the other DB..

[Paul Sturrock]

If you have to do this manually you'll need to:

track what is changed

track when it has changed

have a strategy for how your application handles the latency you are building in to the application

have some sort of strategy to resolve collisions

Doing the first is fairly easy, you can use triggers to do it. Some databases come with this sort of auditing, if yous does not you'll need to write triggers for create, update and delete operations for every paired table that logs what was changed and how to a history table. Doing the second is easy too, just have these triggers log a time stamp of when the change was made. Your application, if it is not driven by these triggers, will have to will have to watch these audit tables to create/update/delete data in the other database (and vice versa) as appropriate.

Doing the last two points is really hard and depends on your application's business logic. You are going to have to work out what to do when you have an update on one entity that has been deleted from another database, or what to do when you have two concurrent updates on the same entity in both databases.

[aryan Sharma]

but then would we create a timestamp for every field.. would that be too much of data.... cant we optimize this step ?

[Paul Sturrock]

Every field? No, you can timestamp the row and describe the operation (e.g. 'u' for update, 'd' for delete etc. ) then your code can compare the current audit row with the previous to track changes for that entity in one database. You'll need extra logic to then compare against the value in the other database. Auditing database tables will cause a significant increase in the size of your databases. There's no avoiding that.

[aryan Sharma]

i do not think its going to work. think about it... lets say from the web interface for a particular contact the user changes the first name and after that from the handheld device the user changes the first name of this contact...now as per the timestamp i would be overriding the Data of the Web DB with that of my handheld DB since according to the timestamp handheld DB has the latest changes, but actually its incorrect since in this case i would loose the first name change that i did from the web interface.. right? thats why i was saying that we might need to have timestamp for every field and this dosent sound like a good logic... what do you think ?

[Paul Sturrock]

A timestamp for every field doesn't solve that problem - think about it, what is different from that and a row for each change? What you have to come up with are the business rules to define what happens when conflicts occur - conflicts are going to occur and you are going to have to throw away data (or otherwise quarantine it till someone can manually make the decision of which update wins).

Like I (and others) said earlier this is very hard. Which is why it is rare to hear of people choosing to try to do it.

[Jeanne Boyarsky]

You are correct that you have to track changes at a field data and it is a lot of data. And a lot of work.

Which is why I asked above why the constraint on having two databases. If you can't talk about it here, talk about it with the person imposing the decision and make sure they are aware of the consequences.

[PJ Crump]

You never said what kind of database you are dealing with? Oracle, SqlServer, MySql, Db2???

[Paul Sturrock]

Does that matter for this problem?

[PJ Crump]

I ask because there could be to solutions to the problem.. one via the usage of java technology and another if you implement database replication..

[Joe Panico]

There is an open source Java project that is able to describe the data or ddl differences between two tables in a structured text format. It also has a component that is able to generate SQL statements (inserts and updates) that will modify one of the tables so that it will have the same data elements as the other.

Why don't you see if that project is able to provide you with what you need.

www.diffkit.org

Joe