How do you manage two-way synchronization between a 'main' database server and many 'secondary' servers, in particular conflict resolution, assuming a connection is not always available(offline syncing)?
For example, I have an Android application which is use data from local database(sqlite). And user can deal with data even it is online or offline. In meantime when if it is online, then local database(sqlite) should sync with real database(mysql) in the server and resolve conflicts as a background service.
Is there any solution already implemented for this kind of scenario ?
I heard about Couchdb which is a NoSQL solution can be a solution for this. If it is so Can I use it with SQL ?
Best Answer
Basically you would need to take the commit logs from both databases and compare them, looking for instances of updates to the same table and row (and possibly column) in both databases. Any commit records for rows not in conflict can be sent to the appropriate database (i.e., a commit record from the master can be transformed into an appropriate CRUD statement and issued to the client), all rows in conflict will have to have their commits ordered temporally and the last commit issued would "win". If the commit logs are not retained, you'll have to keep them manually (probably by structuring your updates using the command pattern). This is a non-trivial exercise.