This is a very pragmatic approach to database-backed software upgrades. It was described by Martin Fowler and Pramod Sadalage in 2003 and subsequently written up in Refactoring Databases : Evolutionary Database Design.

I can see what you mean when you say that it seems sloppy, but when done intentionally and with forethought, and taking the time to refactor unused structures out of the codebase and database when they’re demonstrably no longer used, it’s much more robust than simpler solutions based on upgrade and rollback scripts.

“Zero downtime” is just one of many possible reasons for this kind of approach. Keeping a datamodel backwards compatible this way helps you to deal with a lot of different problems:

• if you have a lot of software packages accessing your database, you won’t have to check them all if a schema change affects them (in bigger organizations with multiple teams writing programs all accesssing the same database, schema changes may become very hard)

• if you must, you can check out an older version of one of your programs and it will most probably run agains a newer database (as long as you don’t expect the old program to handle any newer columns correctly)

• import / export of archived data into the current database version is much easier

Here is an additional rule for your list

• each new column should be either NULLable or provide a meaningful default value

(this makes sure even older programs which don’t know the new columns won’t break anything when they create new records in your database).

Of course, this approach has one real drawback: your datamodel quality may degrade over time. And if you have complete control over all applications accessing your database, and you can refactor all those applications easily when you, for example, are going to rename a column, then you might consider to refactor things in a cleaner way.

It sorts of varies from one deployment to another.

Sure, you could never delete a table or a column. You could never change anything that broke interface compatibility. You can always add a layer of abstraction. But then you have to version that abstraction and the version the versioning.

The question you need to ask yourself is, does every single release change the schema in such a way that it is not backwards compatible?

If very few releases change the schema in that way, then the database issue is mute. Just do a rolling deploy of the application servers.

The two things that I have seen help the most with minimal-downtime deployment are:

1. Strive for backward compatibility – at least within a single release. You won’t always achieve it, but I can bet you can achieve it on 90% or more of your releases, especially if each release is small.
2. Have a pre-release and post-release database script. This allows you to handle renames or interface changes by creating your new object before your app code is deployed, then dropping the old one after the app code is deployed. If you add a new non-nullable column, you could add it as nullable in your pre-release script with a trigger that fills a default value. Then in your post-release, you can drop the trigger.

Hopefully the rest of your deploys can be saved for maintenance windows.

Other ideas that may help deal with the few deploys which require downtime:

• Can you build backward compatibility into your code? For example, is there any way your code can support multiple types of result sets? If you need to change a column from an int to a double, your app code could read it as a string and parse it. Kind of hacky, but if it’s temporary code to get yourself through the release process, it may not be the end of the world.
• Stored procedures can help insulate your app code from schema changes. This can only go so far, but does help a bit.

You could potentially do it like this for a bit of extra effort.

1. Back up database by taking an export
2. Import the back up but rename it with a release version e.g. myDb_2_1
3. Execute database release on myDB_2_1
4. “Stop” the app pool on Web Server A or take it out of the load balancer
5. Update Web Server A, run post implementation tests and rollback if necessary
6. Session bleed Web Server B and put Web Server A back in loop
7. Upgrade Web server B and then put back in load balancer

Naturally the web updates would need new config entries to point to the new Db schema. Thing is if you’re doing releases once a month and it’s a small team how many DB changes are you really making that aren’t backwards compatible? If you can control that by testing you could go for getting an automated deployment with no down time or maybe at worst only 5 mins down time.