How a website should handle a database crash

Question

I am currently creating a website in php that has a database backend (can be either MySQL or SQL Server) and I realized recently that if my database crashes at any time, my website will not run properly and probably cause some headaches.

So what is the proper thing to display on the website if my database (or any crucial outside component) goes down? My particular website relies heavily on its database and will be almost useless without it.

One option I have been told is to email the website admin and display a Error 500 page that says something is wrong with the server and basically make the website unusable till the issue is fixed. Is there anything else I could do to work around this problem? Are there any ways to design a website so that the database (any crucial component) crashing isn't an issue?

I am looking for general rules of thumb as well as specific examples of how people have worked around this in the past. Also, these examples don't just have to be for my website example.

Answer 1

If you only have one database server, and the website cannot work without it's database, there is no magic : you'll have to display some sort of nice error page, informing the users there is a technical problem and that the website will come back shortly.

Generally speaking :

• Chances of such a problem are pretty low
• If your website is a normal one, people will tend to accept a problem once in a while, especially if you communicate about it.

If you can afford it (and have the technical knowledge to set this up), you could use two database servers, with replication (MySQL supports this) between them : one master, which you use, and a slave, that's considered as a backup.

Then, if the master falls, your application will use the slave.

Of course, this will greatly reduce the risks of a database-related problem (having two servers crash at the same time is quite unlikely), but you'll still have problems with all other components -- like your webserver : if you only have one, you might want to consider using two, with the second one as a fallback.

After that, if you still have money (and think you need an even better uptime for your website), you'll want to think about the case when your datacenter has a problem -- setting up server in two separate locations...