Database distribution

Question

What are the possibilities to distribute data selectively?

I explain my question with an example. Consider a central database that holds all the data. This database is located in a certain geographical location.

Application A needs a subset of the information present in the central database. Also, application A may be located in a geographical location different (and maybe far) from the one where the central database is located.

So, I thought about creating a new database at the same location of application A that would contain a subset of information of the central database.

Which technology/product allow me to deploy such a configuration?

Thanks

Answer 1

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.

Answer 2

Most major database software such as MySql and SQL server can do the job, but it is 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 serving other applications),but you will also be abusing your network bandwidth to transfer data between 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 away database 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.