Which databases can meet these replication requirements

Architecturedatabase-recommendationreplication

I am doing research on database architectures, and I am looking for which software can meet my requirements.

To try to explain the requirements simply:

  1. You have a master database server. This may make a one or a few identical replicates for distance purposes – such as one for US and another for Europe. These masters would have identical clones locally for failover.

  2. This master then does one-way slave propagation to many read-only servers via VPN. These servers serve-up all data reads to a LAN at that server's location, serving a few hundred clients at each location. Ideally, the read-only slave DB could mirror locally for failover.

  3. All writes are made to the master server(s) and then make their way back to the slaves. The application this architecture serves is far more read intensive than write.

  4. The slaves require intelligent granularity. The replication must not only be table-specific per site, but also must be row-specific in some tables. For example, the master server would have a monolithic client table with all clients across all sites. The client table should replicate in part to the slaves, containing the same columns however only having those clients that have purchased from that local branch. We do not want the local branch to have access to all clients.

__

Though relatively novice in database administration, I have done a bit of research thus far. It seems Postgresql is quite a positive choice for a database, and the closest replication middleware I can find to fit these requirements is PgPool-II. I do not, however, know if it will meet all of these requirements easily and in a way that will scale.

What, if any, major database such as PostgreSQL or MSSQL, is capable of satisfying the above requirements? Does it do this natively? And if not natively, what middleware for that database is capable of handling it?

Best Answer

Having done this type of project before, here's some of the practical gotchas to look out for:

"2. This master then does one-way slave propagation to up to 200 read-only servers via VPN."

Think carefully about the network traffic required here. In the simplest terms, if the insert/update/delete load on the database takes up 1/200th of a network cable's throughput, and if your master server only has one network connection, it's going to be saturated. (In practical terms, it'd be saturated long before that since you also need to accommodate query loads and backups.)

Next, think about the logging requirements. Every transaction has to be logged, and the logged changes get sent to the read-only servers as they happen. If any one read-only server drops offline, then the master has to retain all of the logs until the read-only server comes back online and consumes the changes. In a high-volume change environment like you're describing, this can easily be a higher size than the original data set itself (since some records can get updated over and over, or get deleted.) You need the ability to recognize when a replica has gotten farther behind than it can easily catch up, and then the ability to reinitialize that replica from backup/restore rather than replication synchronization.

Since you want to be able to fail over from one master to another, then every master would need to be aware of the replication synchronization state of every read-only replica.

With these limitations, you can see why you usually want to separate the work of distribution out onto other servers. You want masters handling writes, but then just batching off the changes to a set of distributor servers that are responsible for tracking replication synchronization states across many read-only servers. (This is how Microsoft SQL Server's transactional replication works, and I would imagine most other DBMS's have similar distributor architectures available as well.)

Related Question