Keeping distributed databases synchronized in a unstable network

Question

I'm facing the following challenge:

I have a bunch of databases in different geographical locations where the network may fail a lot (I'm using cellular network). I need to keep all the databases synchronized but there is no need to be in real time. I'm using Java but I have the freedom to choose any free database.

How can I achieve this?

Answer 1

It's a problem with a quite established corpus of research (of which people is apparently unaware). I suggest to not reinvent a poor, defective wheel if not absolutely necessary (such as, for example, so unusual requirements to allow a trivial solution).

Some keywords: replication, mobile DBMSs, distributed disconnected DBMSs.

Also these research papers are relevant (as an example of this research field):

1. Distributed disconnected databases,
   • The dangers of replication and a solution,
   • Improving Data Consistency in Mobile Computing Using Isolation-Only Transactions,
   • Dealing with Server Corruption in Weakly Consistent, Replicated Data Systems,
   • Rumor: Mobile Data Access Through Optimistic Peer-to-Peer Replication,
   • The Case for Non-transparent Replication: Examples from Bayou,
   • Bayou: replicated database services for world-wide applications,
   • Managing update conflicts in Bayou, a weakly connected replicated storage system,
   • Two-level client caching and disconnected operation of notebook computers in distributed systems,
   • Replicated document management in a group communication system,

... and so on.

Answer 2

I am not aware of any databases that will give you this functionality out of the box; there is a lot of complexity here due to the need for eventual consistency and conflict resolution (eg, what happens if the network gets split into 2 halves, and you update something to the value 123 while I update it on the other half to 321, and then the networks reconnect?)

You may have to roll your own.

For some ideas on how to do this, check out the design of Yahoo's PNUTS system: http://research.yahoo.com/node/2304 and Amazon's Dynamo: http://www.allthingsdistributed.com/2007/10/amazons_dynamo.html

Answer 3

Check out SymmetricDS. SymmetricDS is web-enabled, database independent, data synchronization/replication software. It uses web and database technologies to replicate tables between relational databases in near real time. The software was designed to scale for a large number of databases, work across low-bandwidth connections, and withstand periods of network outage.

Answer 4

I don't know your requirements or your apps, but this isn't a quick answer type of question. I'm very interested to see what others have to say. However, I have a suggestion that may or may not work for you, depending on your requirements and situation. particularly, this will not help if your users need to use the app even when the network is unavailable (offline access).

Keeping a bunch of small databases synchronized is a fairly complex task to do correctly. Is there any possibility of just having one centralized database, and either having the client applications connect directly to it or (my preferred solution) write some web services to handle accessing/updating data rather than having a bunch of client databases?

I realize this limits offline access, but there are various caching strategies you can use. (Which of course, leads you back to your original question.)