Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?
share|improve this question
    
Where is your backout plan? How do you test that everything works and there are no regressions? –  Deer Hunter Jun 24 '13 at 16:37
1  
You don't need "never": you "only" need to ensure that every two adjacent versions can run concurrently. This restricts your upgrade paths, but not as severly as never beeing able to alter the DB schema significantly. –  Joachim Sauer Jun 25 '13 at 6:57
    
Thanks Joachim ... I like to talk in absolutes so that the basic idea is clear - but you are correct, we could have a policy of being backward compatible by N releases, at which point we can remove unnecessary DB objects. –  MattW Jun 25 '13 at 12:37
add comment

4 Answers

up vote 5 down vote accepted

This is very pragmatic approach to database-backed software upgrades, and was described by Martin Fowler and Pramod Sadalage in 2003, and was 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.

share|improve this answer
add comment

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.