Database distribution Database distribution database database

Database distribution


Look for database replication. SQL Server can do this for sure, others (Oracle, MySQL, ...) should have it, too.

The idea is that the other location maintains a (subset) copy. Updates are exchanged incrementally. The way to treat conflicts depends on your application.


Most major database software such as MySql and SQL server can do the job, but itis not a good model. With the growth of the application (traffic and users),not only will you create a load on the central database server (which might be servingother applications),but you will also be abusing your network bandwidth to transfer databetween the far away database and the application server.

A better model is to keep your data close to the application server, and use the far awaydatabase for backup and recovery purposes only. You can use an FC\IP SAN (or any other storage network architecture) as your storage network model, based on your applications' needs.


One big question that you didn't address is if Application A needs read-only access to the data or if it needs to be read-write.

The immediate concept that comes to mind when reading your requirements is sharding. In MySQL, this can be accomplished with partitioning. That being said, before you jump into partitions, make sure you read up on their pros and cons. There are instances where partitioning can slow things down if your indexes are not well chosen, or your partitioning scheme is not well thought out.

If your needs are read-only, then this should be a fairly simple solution. You can use MySQL in a Master-Slave context, and use App A off a slave. If you need read-write, then this becomes much more complex.

Depending on your write needs, you can split your reads to your slave, and your writes to the master, but that significantly adds complexity to your code structure (need to deal with multiple connections to multiple dbs). The advantage of this kind of layout is that you don't need to have complex DB infrastructure.

On the flip side, you can keep your code as is, and use a Master-Master replication in MySQL. Although not officially supported by Oracle, a lot of people have had success in this. A quick Google search will find you a huge list of blogs, howtos, etc. Just keep in mind that your code has to be properly written to support this (ex: you cannot use auto-increment fields for PKs, etc).

If you have cash to spend, then you can look at some of the more commercial offerings. Oracle DB and SQL Server both support this.

You can also use Block Based data replication, such as DRDB (and Mysql DRDB) to handle the replication between your nodes, but the problem you always will encounter is what happens if your link between the two nodes fails.

The biggest issue you will encounter is how to handle conflicting updates in 2 separate DB nodes. If your data is geographically dependent, then this may not be an issue for you.

Long story short, this is not an easy (or inexpensive) problem to resolve.