How to Deploy Database Changes to a Live Server?

Question

I have a script that is part of my deployment process to push DB changes to the production server. If the script corrupts my data for some reason (a bad update), it is tough to recover.

One way to solve this is to shut down the application to users while updating, so if a problem occurs, just go back to the backup I made before deploying.

But I have heard of others who deploy and keep their site live... how would you go about doing this, and if you failed, how could you recover the data that came in since you took your backup before deploying?

Answer 1

This is a tricky problem in general, like with many things in database administration. There are basically three ways to approach this:

1. Avoid failure at all costs.
2. Lock everything down (and make the upgrade really fast).
3. It's OK to lose data.

If you have a complex system, isolate your components according to these or similar categories.

Have a staging system to test upgrades. The staging system is more or less a copy of the production system; it's separate from the test system. Another thing is to have an audit or logging system that you can refer to if you need to replay data.

The real problem is if you notice much later that your upgrade was faulty. Then you're quite screwed.