Suppose the middle tier servers are replicated 3 way and the backend database (MySQL, PostgreSQL, etc...) is replicated 3 way. A user request ends up creating some user data in the middle tier servers and I would like to commit this to the backend database in a way resilient to failures.

A candidate attempt solution, for example, if I send the data to one SQL database and have it replicate the data to the other databases then if the one SQL database has the harddrive crash before it can replicate the data, the data is lost.

What is the best practice solution for fault tolerance that is used in the actual real world.

  • 1
    you may get more answers on serverfault – Ian Jul 22 '11 at 11:04

Replication would typically be used for site to site data transfer.

Fault tolerance is all about redundancy. The more redundant your system is more tolerant it is to faults. A lot of can be solved through infrastructure, rather than code, especially for a database.

Hopefully the server has redundant hard drives that can be hot swapped on the fly if there is a failure. So the server itself must have redundant feature(s) such as hardrives power supplies, etc.

But what if server crashed, then we are into failover clustering, this is when another server takes over seamlessly should one server fail. In this case, multiple nodes would be available. Usually one is active and the other passive waiting for failure.

Then is a more serious failure, data center crash, for example an earth quake or some other act of god. If this case another data center would take over. In this scenario the most likely the data is replicating from one site to another.

All of these cost more money to implement and maintain. It depends on how serious the data is and what the client demands are. In some cases, just a nightly back up will do.

Hopefully your online banking system is using more than that.

I don't know what your requirements are, but those are the most common scenarios and levels of escalation.


Are you familiar with the CAP theorem ? There's mathematical proof that there's no best solution. You must make a tradeoff that's best for your case.

As a practical matter, use redundant disks so the likely failures are uncommon, and use distributed replication so that a loss of a single site doesn't mean the loss of the entire business. It's generally an acceptable risk to lose some recent data in such extreme cases.


The best practice for generic systems (ie not a custom rolled one for a specific case) is currently DB log shipping to 2 sites with a witness server to ensure the "split-brain" instance never occurs.

(split brain is when you have 2 sites that lose connectivity between them, clients at site A think they're the one that's up and writes to its DB, but site B thinks its the one that's still up and writes to its. when you re-connect, you find you have data in both DBs thats impossible to merge together).

SQLServer, MySql and Oracle all have a form of replication (Oracle Dataguard, SQLServer mirroring, MySql replication IIRC). You write data to a 'single' DB and it copies the data to the mirror. If the primary DB goes down, the system promotes the mirror to be the primary and they write to that instead, when the dead DB is brough back up, all the missing data is shipped to it and it continues from there. The problems with this is that the longer its dead, the more data is required to be shipped over to 'rebuild' the DB. Also, when the promotion occurs, the system will pause or appear to hang for a short while as it makes sure the DB is un-contactable. We've seen this pause for a minute, which is a long time in a mission-critical situation!

Oracle will provide for multiple mirrors, up to 9 I think, but most of the other systems only provide for a single mirror.

  • PostgreSQL has supported hot-standby backups since 9.0, and it allows for multiple standby servers as well. – unpythonic Jul 22 '11 at 11:16

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.