MSBuild: automate db migration script collection?

Question

Consolidated environmental information.

• Asp.net web application (source stored in svn)
• SQL Server Database (Database schema (tables / sprocs) stored in svn)
• the db version is synchronized with the build version of the web application. (stored in the table "CurrentVersion")
• CI hudson server that checks the web application from the repo and runs its own msbuild file for publishing / packaging.

My msbuild script updates the web application build version (Major.Minor.Revision.Build) for each build. "Revision" is installed on the current verified version of svn and "Build" on the hudson build number (incremented by each automatic build).

That way, I can map the application to a specific version of the chest, and also get other build statistics from the hudson build number.

I would like to automate the collection of migration scripts (updated sprocs, etc.) to add to the zip package. I think by comparing the version of svn db that is not yet deployed to the deployed version, I can find which db files have changed in the trunk since the last deployment in this database / environment.

This can be easily achieved by manually invoking the svn diff -r REVNO:REVNO to rename the modified .sql files. These files can be manually added to the package. It would be great if it could be automated.

First, I would suggest that I have to write a custom task to check for a version of db that has not yet been deployed. After that, I am completely unsure. Does anyone have any suggestion on how this will be achieved using the msbuild task, both existing and custom?

Finally, I will have to auto-generate a script to add to a package that updates the database version table in order to synchronize with the application.

Answer 1

Integrate SQL changes into the automated HARD build / deployment process. I know, because I tried to do this a couple of times with limited success. What you are trying to do is roughly on the right track, but I would say that it is actually too complicated. In your proposal, you suggest collecting specific SQL scripts that should be applied to your database at build / package time. Instead, you should pack all the delta scripts (in the entire history of the database) with your project, and calculate the deltas that should actually be applied during deployment - this way, your deployable package can be deployed to an environment with databases of various versions. To do this, you need to complete two parts of the implementation:

1) You need to pack your deltas in your deployable package. Note that you must share packages — not static files that create the circuit in its current state. These delta scripts must be in source control. It is also good to keep the static circuit in the original control, but you will have to synchronize it with the deltas. In fact, you can use a tool like Red Gate SQLCompare or VS Database to generate (most) deltas from a static circuit. To get deltas in your deployable package, and provided that you use SVN - you can look in SVN: external links as a way to "soft link" delta scripts in your web project. Then your build script can simply copy them into your deployable package.

2) You need a system that can read the list of delta files, compare them with an existing database, determine which deltas should be applied to this database, and then apply the deltas (and update the accounting information, like the version of the database) . It called the open source project (with ThoughtWorks support) dbdeploy , which solves this problem. I have had some success with this tool personally.

Good luck is a strong nut (right).

Answer 2

Look at SQL database projects. In VS 2010, they are significantly improved and have built-in deployment capabilities that can synchronize your DEV database with other environments.

Here are some good links about database projects in 2010: http://msmvps.com/blogs/deborahk/archive/2010/05/02/vs-2010-database-project-building-and-deployment.aspx

http://weblogs.asp.net/gunnarpeipman/archive/2009/07/29/visual-studio-2010-database-projects.aspx

Answer 3

Try SQL Examiner: http://www.sqlaccessories.com/Howto/Version_Control.aspx

You can automate the collection of scripts using the SQL Examiner command-line tool.

Answer 4

Available solutions for the .NET / SQL Server stack today:

• DBUp (open source)
• ReadyRoll (deeper Visual Studio integration, script auto-generation)

The latest product is the one we are actively developing here at Redgate.