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

sql-server deployment database-versioning
Brett Allen picture Brett Allen · Dec 22, 2009 · Viewed 20.2k times · Source

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

marc_s picture marc_s · Dec 22, 2009

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!

Related questions

How do I create the deployment scripts from VS 2012 database projects?

With VS2010 database projects, I could define a target (or not) database, set an action (create script only or create script and deploy to target), execute a "deploy" (through VS or MSBuild) and get a result script. I never used …

Read More
Keyword not supported: 'dsn'

I am trying to publish my site on remote server. My hosting provider has provided my with dsn connectivity through dotnetpanel. When i publish my site i get the error as shown in screenshot. I have also included my web.…

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More