2

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?

4
+100

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.)

  • Thanks and very informative. Distributor servers are referred to as "Publishers", right (at least in MSSQL lingo)? And do you know if MSSQL can support all of these things? Thanks. – dthree Mar 15 '15 at 20:29
  • No, publishers are the original source of the truth, and they pass the list of replicated data off to distributors. Can MSSQL support all these things? Sure, but think about licensing: Standard Edition is ~$2k USD per CPU core. The project you describe would be millions of dollars just for licensing alone, even before hardware. – Brent Ozar Mar 15 '15 at 20:30
  • @dc2 Re: point 4: SQL Server Merge Replication can do that. Be aware that complex join filters + many subscribers (200 can be "many", in case you're wondering) might turn into a performance nightmare, especially with precomputed partitions. – spaghettidba Mar 15 '15 at 22:59
  • @dc2 I'm not saying it's the wrong way, I'm just saying it won't be easy. – spaghettidba Mar 16 '15 at 9:12
  • @dc2 exactly, speghettidba is right on. There's no one "right" way to do a design like this in MSSQL. It's like asking, "Can I build an app that does ___?" Modern RDBMS's have all kinds of features built in, including the replication features we're describing, but it's still up to you to combine those features, architect them, and load test them with your loads and hardware in order to get to the right answers. In theory, this design works great. In practice, it's a multi-million-dollar project. (I've been on these.) – Brent Ozar Mar 18 '15 at 18:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.