What is the best way to deploy SQL Server scripts in source control?

Question

I am trying to get our company's database objects stored in source control. We are going to be stuck using Aldon LM for our source control program. What is the best way to deploy scripts that we create to our SQL Server environments automatically from source control?

For our .Net web applications, we have configured Aldon to push our code to the web server and run MSBuild.

Answer 1

We have programs (ant hill pro for one) which go through the source control system, and retrieve all the necessary sql scripts and compile them into one file. We have a branch for each release, so only the ones for that branch get get pulled. I think cruise control can do the same thing, but I don't know how that is done.

We then can execute all the updates and once and don't have to worry about missing one.

Answer 2

Open a Database project in your solution to manage all SQL code such as stored procedures. This has a few advantages:

• Your SQL can be under version control just as the rest of your conde
• You can do easy find/replace file operations on multiple procedures
• You can keep your SQL in a meaningful and systematic folder hierarchy

Answer 3

Install VS Team System Database Edition, now included (available to add to) Developer Edition.

This has tools to work out which scripts to apply to a database to update it to match the reference database and will (providing your SCM integrates with VS) also handle tracking all those scripts and there status within the project.