Database and Continuous Integration

Ben Scheirman wrote a post on the flaws and irritation of dealing with database scripts for a project and looked to Ruby on Rails for inspiration. After I commented on his blog about what our team does, he sent me a email with some questions:

Thanks for the comment on my blog...  I'm wondering:  what tool do you use to create the scripts?

Do you do a drop, re-add?  or a if not exists, then create?

Also, how do you order these?

I was thinking of doing a rails-style migration scheme like this:

0001.Items.Table.sql
0002.Categories.Table.sql
0003.Items.TestData.sql
0004.Items.Categories.FK.sql

but I'm concerned about multiple developers adding migrations and having them collide.  Ideas?

My Response:
Order is an interesting problem. For table changes, we use the "if not exists, then create" strategy. For Views, Functions, Sproc we drop and re-create (including permissions).

What we've discovered is that order only applies to table mods. Our sprocs and views run after the table updates, but we don't have sprocs calling other sprocs (generally) so they don't have order dependency.

As for a tool, we used a SQL project in VS 2003. It would create a .cmd file with all files in the project using an algorithm based on file extension. .tbl are scripted first, then .vw, then .prc, and then finally .sql (or were .sql first, I don't remember). This worked well until VS 2005 dropped the feature that created a command file.

So…we wrote a Nant script that traverses the \sql directory and performs the same logic as the command file. It works, but it is not ideal.

I did see a demo of the new "Data Dude" (VS 2005 Edition for DB Professionals). It was impressive how they solved this problem. They introduced a "build" step that creates a script file of all the changes taking into account dependencies and refactorings (yes, refactorings in the database). Very cool stuff, but I haven't worked with it personally yet. L

I like the ideas behind Rails but do you think your developers are going to do it? They probably have access to the database and will just run the script themselves. I'm asking because we ran into that problem on my current project and the team decided to remove our rights to the testing database. The only way to get scripts on the test server were to have the CI server execute them during the automated build. This forced us to make sure our scripts were in the code repository (I hate typing SourceSafe) and ran without error.

All that being said, I’m still looking for a better solution.