approaches to Build servers and stored procedures

Question

Hi all,

I have been put in charge of looking at setting up a build server for our office. We currently put all queries into stored procedures in SQL 2000 server. This is done manually and no SQL files are produced or put into SVN.

What I am after is a good way of dealing with having a build server that can get all the stored procs from a DB.

I am guessing this might not be possible / practice and am pretty sure not the best practice. I realize one solution could be to start creating SQL script files and putting them into SVN so they can be picked up and dealt with.

Answer 1

You have answered your own question. Get these things into source control before you start digging yourself further into a hole you really don't want to be in.

Once done, an approach we have used successfully is to have an initial snapshot set of scripts, then version numbered script folders for changes, with the overall database version number stored in a database table specifically for that purpose. We then wrote a utility to assemble all the update scripts since the stored version number, run them, and update the version number. This integrated with our build script that was run against the dev DB by an automated build. Schedules and so on are of course up to you.

Would strongly advise you make all DB scripts safely repeatable as well.