How to handle database upgrades

What is the best way to handle database upgrades?

Currently we run an upgrade script that does stuff like: If column does not exist then add column.

Is there a better way? This script is a giant?

This questio开发者_如何学Gon is in regards to Microsoft and SQL server 2005/2008

If this is deployed to many different locations, then what you have is exactly how to do it. Or you can use something like Red Gate's SQL Packager

If this is a question on how to deploy to just a few locations, for example moving from staging to production, then use Red Gate's SQL Compare and/or SQL Data Compare products.

FYI - I do not work for Red Gate; I just really like their stuff.

I've tried using Microsoft's tools for schema and data comparisons. The data tool works just fine; the schema tool is over engineered and difficult thing to get to work right. Quite frankly I've seen MS's schema tool screw up enough updates that I flat don't trust it anymore.

One of the many drawbacks to MS's tool is that by default it wants to update things like the physical location of the files on the server. This presents problems when you have multiple copies of the database on the same server. I don't think they thought the development of this tool out very well. The UI is difficult to work with as well.

You may also want to try out RedGate's SQL Compare. This tool will compare two schemas and produce the script to update and make them the same.

Using an upgrade/downgrade script is a good way. The feature NYSystemsAnalyst is talking about sounds interesting. However, you may need some specific actions to convert your existing data.

For example, if you refactor your table structure split columns to several tables, you need some code to generate the data for the new table from within the existing one.

Ruby on Rails has such a mechanism built in by using "migrations". You have to provide a change script for both directions, upgrading and downgrading. A tool then automatically checks your current database schema version and executes the necessary change sripts. I think similar systems should be available for .NET or which environment you are using.

As mentioned already, Red Gate's SQL Compare will most likely meet your needs. However, if you're looking for a "migrations" feature analogous to Ruby's Active Migrations, you'll have to wait a little longer for this to be included. If you could fill in the following survey it will help us design the feature: http://www.surveymk.com/s/migrations

Thanks for your help!

In the past, I have used a version table to keep track of what version a database was currently at. Then, based on that version table, the necessary scripts would be run to bring the database to the desired version. This is somewhat risky because it relied on the version table to be the source of truth regarding the current state of the database schema, but we have a very controlled database environment, so that helps mitigate some of the risk.

I have recently been making use of the Database Projects that are part of Visual Studio 2010. If you have VS, take a look. You can maintain the "current" version of the database in VS, then it will analyze the target database and generate the necessary change script. It's not perfect and there are some steps you need to take to preserve the current data, but it may be worth a look.

I have written a series of blogs on database versioning techniques that I use - that might be useful to you eg auto updating a version table on each deployment, using checksums to test for 'unauthorised' changes, auto logging changes. See - http://michaelbaylon.wordpress.com/category/database-versioning/ - for more detail.