What is an easy way to deploy database changes using SQL Server?

Question

The software system I work on is a medical billing system, large amounts of data and data tables, and stored procedures.

I was reading the article "12 Steps to Better Code" and in The Joel Test #2 states: Can you make a build in one step?

Now I was wondering, does this mean deployment build (so that a customer can update their deployment).

Now the main issue I'm running across, is how do you do a one step database update?

At the current time, when we make changes to a database, all changes are recorded and added to a database update script, which gets a version number attached to it when a deploy to customer build is created.

Is there a simpler way to do this? Some script or application out there that takes a "before and after" look at a database schema and creates an update script like I mentioned?

Or is this just the way everyone does it, which I would find hard to believe, but plausible.

An automated system would decrease errors, and speed up deployment build times considerably, and I would be interested in knowing how to do so.

Answer 1

There's various levels of complexity that you can go through:

• if you have update scripts that you create manually, and are just looking for a way to easily apply those to various servers, check out the SSW SQL Deploy by SSW Consulting. It can handle that scenario very nicely

• if you tend to do more of a database diff approach, then Red Gate's SQL Compare (already mentioned) and SQL Packager make a great combo. You can diff the database between old and new and then apply the changes in a nice package - as an EXE or a C# project

• if you want a real, end-to-end, well thought out approach (with a bit of a learning curve), check out Innovartis' DBGhost approach. It's a entire methodology / technique how to handle database development and incremental updates. It's very powerful and look very promising - but it's a bit of an all-or-nothing approach: either you buy into it and use it end-to-end, or you don't

Hope this helps a bit!

Answer 2

redgate has a tool SQL Compare to compare databases and generate a script to synchronize. We used to use it but more recently switched to manual scripts using the same process you describe. Using manual, fine grained, scripts with a unique version number has worked out well.

We have our upgrade scripts integrated into unit tests so they get tested along with code as part of continuous integration. I think this is an important part to "making a build in one step."