Automating DB Object Migrations from Source Control

Question

I'm looking for some "Best Practices" for automating the deployment of Stored Procedures/Views/Functions/Table changes from source control. I'm using StarTeam & ANT so the labeling is taken care of; what I am looking for is how some of you have approached automating the pull of these objects from source - not necessarily StarTeam.

I'd like to end up with one script that can then be executed, checked in, and labeled.

I'm NOT asking for anyone to write that - just some ideas or approaches that have (or haven't) worked in the past.

I'm trying to clean up a mess and want to make sure I get this as close to "right" as I can.

We are storing the tables/views/functions etc. in individual files in StarTeam and our DB is SQL 2K5.

Answer 1

I prefer to separate views, procedures, and triggers (objects that can be re-created at will) from tables. For views, procedures, and triggers, just write a job that will check them out and re-create the latest.

For tables, I prefer to have a database version table with one row. Use that table to determine what new updates have not been applied. Then each update is applied and the version number is updated. If an update fails, you have only that update to check and you can re-run know that the earlier updates will not happen again.

Answer 2

We use SQL Compare from redgate (http://www.red-gate.com/).

We have a production database, a development database and each developer has their own database.

The development database is synchronised with the changes a developer has made to their database when they check in their changes.

The developer also checks in a synchronisation script and a comparison report generated by SQL Compare.

When we deploy our application we simply synchronise the development database with the production database using SQL Compare.

This works for us because our application is for in-house use only. If this isn't your scenario then I would look at SQL Packager (also from redgate).

Answer 3

Check into Migrations (pointed out by Andrew Peters in another post)

Answer 4

As Carl pointed out you can use a diff utility to create your update scripts. RedGate makes a good product, but SQL Server 2k5 ships with TableDiff which should do the job as well.