What is the State of the Art for deploying database updates to production databases?

Question

Every shop at which I've worked has had their own cobbled-together, haphazard, poorly understood and poorly maintained method for updating production databases.

I've never seen a consistent method for doing this.

So, in the most recent versions of SQL Server, what is the best practice for updating schema changes and migrating data from a development or test server to a production server?

Is there a 3rd party tool which handles this painlessly?

I'd imagine the ultimate tool would be able to

• detect schema changes between two DBs and generate DDL to update one to the other.
• include the ability to have custom code which performs custom data migration steps
• allow versioning so a v1 db could be updated all the way to a v99 database, running all scripts and migration steps in order.

Answer 1

The three things I've used are:

For schemas

Visual Studio Database Projects. Meh. They are okay but you still have to do alot of the work yourself.

Red Gate's SQL Compare and the entire SQL Toolbelt. They've worked pretty hard to make this something you can version control. In practice I've found with databases you are usually trying to get from point A in the version timeline to point B. With binaries, you often just clobber whatever is there with point B (an oversimplification I know, but often true).

http://www.red-gate.com/

xSQL is a good place to start if your system is small and perhaps will remain small:

http://www.xsqlsoftware.com/LiteEdition.aspx

I don't work for or know anyone who works for or get any money from these people. Just telling you what I've done in the past.

For data

Red Gate has SQL Data Compare.

However, if you want something "free" (or included with SQL Server) I've actually had a lot of success just using BCP and writing a small system that injects and extracts data. Generally when I find myself doing this I ask myself, "Why? If I am changing data, does that mean I am really changing something that is configuration? Can I use a different method here?" But sometimes you can't (maybe it's a legacy system where the original devs thought databases are for everything).

The problem with BCP extracts is they don't version control very well. There are tricks I've used like extracting in character mode and stuffing an order by in the extract query to try and pull rows out in an order that makes them somewhat more palatable for version control.