Achieving Zero Downtime Deployment

Question

I am trying to achieve zero downtime deployments so I can deploy less during off hours and more during "slower" hours - or anytime, in theory.

My current setup, somewhat simplified:

• Web Server A (.NET App)
• Web Server B (.NET App)
• Database Server (SQL Server)

My current deployment process:

1. "Stop" the sites on both Web Server A and B
2. Upgrade the database schema for the version of the app being deployed
3. Update Web Server A
4. Update Web Server B
5. Bring everything back online

Current Problem

This leads to a small amount of downtime each month - about 30 mins. I do this during off hours, so it isn't a huge problem - but it is something I'd like to get away from.

Also - there is no way to really go 'back'. I don't generally make rollback DB scripts - only upgrade scripts.

Leveraging The Load Balancer

I'd love to be able to upgrade one Web Server at a time. Take Web Server A out of the load balancer, upgrade it, put it back online, then repeat for Web Server B.

The problem is the database. Each version of my software will need to execute against a different version of the database - so I am sort of "stuck".

Possible Solution

A current solution I am considering is adopting the following rules:

• Never delete a database table.
• Never delete a database column.
• Never rename a database column.
• Never reorder a column.
• Every stored procedure must be versioned.
  • Meaning - 'spFindAllThings' will become 'spFindAllThings_2' when it is edited.
  • Then it becomes 'spFindAllThings_3' when edited again.
  • Same rule applies to views.

While, this seems a bit extreme - I think it solves the problem. Each version of the application will be hitting the DB in a non breaking way. The code expects certain results from the views/stored procedures - and this keeps that 'contract' valid. The problem is - it just seeps sloppy. I know I can clean up old stored procedures after the app is deployed for awhile, but it just feels dirty. Also - it depends on all of the developers following these rule, which will mostly happen, but I imagine someone will make a mistake.

Finally - My Question

• Is this sloppy or hacky?
• Is anybody else doing it this way?
• How are other people solving this problem?

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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.