Database Merging

Recently, I have been absorbed by the topics of database replication and scalability.

It all started with this very complicated relational database, implemented in SQL Server 5.0, express edition. The reason “why” we are using a relational database, in SQL Server is beyond the scope of this topic. However, the system turn out to have a few more requirements that I now think are only remotely compatible with these choices of technology:

– it is a distributed system, so we want it to be scalable and replicated;

– the nodes of the system are a “worst case scenario”;

Being a “worst-case scenario” in this context means:

– there is no money to buy technologies/frameworks, so we should rely as much as possible in free technologies;

– there is likely no Internet connection;

This means, we must forget about expensive technologies such as Microsoft Sync Framework and get our “hands on the code”. For the record, MS Sync framework offers two types of replication (transactional and merging) but none of them works without Internet.

To actually have a database-independent system that works through text files, that can be posted through email, a USB pen or sheets of paper, it is necessary to implement it from scratch, or at least I thought so (if you have any suggestions you are very welcome to post a comment!)

Having text files means transforming the DB in a document oriented DB, and for that matter I have chosen JSON as the language to implement it. The main challenge here (which is still ongoing) was to convert the relational model into a document, and back to the relational model again.

Also, since it is an asynchronous system, there is no locking to prevent conflicts and therefore there is the need of conflict management, which is always a “sensitive” topic. Being asynchronous, we also need to accept there is an eventual consistency;

Conclusions:

Slowly, I get the picture that a NOSQL database could ease much more the task of replication (and scalability for that matter). For instance in MongoDB, “replication” is a feature of the database:

http://labs.conekia.com/mongodb-nosql-data-bases-replication/

The relational thinking is a structured way of seeing the world; however, do we really want to “shoot ourselves in the feet”, every time we want to do a routine task such as a merge? Maybe the price for “having a schema” is, after all, too high…