Home > Continuous Database Integration, Uncategorized > Continuous Integration with Visual Studio 2010 Database Projects, Hudson & MSBuild (Part 3 of 3)

Continuous Integration with Visual Studio 2010 Database Projects, Hudson & MSBuild (Part 3 of 3)

This post is a continuation to the series on implementing a continuous integration environment for Visual Studio 2010 database projects (.dbproj) using Hudson, MSBuild and Subversion. Links to the previous two posts:

Part 1: Agile Data warehouse Planning & Implementation with Hudson, MSBuild, Subversion and Visual Studio Database Projects.
Part 2:
Managing Database Code for Continuous Integration

Part 1 was an overview of agile development practices for database\data warehouse development. Part 2 explained in detail on setting up the database project, adding code to change control (Subversion) and preparing artifacts for CI. Part 3 will make use of the deployable database script (sql) created as part of the Visual Studio deployment process (from part two) for setting up the CI environment for the database project.

Hudson can be used with NAnt or MSBuild to build and deploy database projects. I will perform a walkthrough of setting up the AdventureWorks database project (created in Part 2) on Hudson using NAnt\MSBuild and Subversion.

Installing Hudson
Hudson is a free continuous integration server mostly used by the dark side (a.k.a. the java community). I have worked with CruiseControl.Net and Bamboo but my personal favorite is Hudson for its management features, ability to extend plugins and a cool UI. Hudson is available for download
here. (Java is a prerequisite to install Hudson).

Install Hudson by running the command line statement (from the directory where Hudson.war was downloaded to):

java -jar hudson.war

By default Hudson is installed on Port 8080. If this port is taken by some other service, use the ‘-httpPort’ switch to change to port on which Hudson runs.

Ex: java -jar hudson.war –httpPort 7777

Hudson can also be configured to run as a windows service. The documentation already does a good job of explaining the steps for configuring hudson to run as a windows service so I will just redirect you to the right place.

Next, find and add MSBuild and Nant plugins from Manage Hudson –> Manage Plugins. The ‘Install’ button is at the very end of the page.


Create a project for the build process

Navigate to http://localhost:8080 (or to the port on which you have hudson running on). Start by creating a freestyle software project for our AdventureWorks sample. In this demo we will be creating a development hourly build of the AdventureWorks DB project. Click OK once done (ignore all the settings on next page, just hit save for now).


Adding the build step & Polling source control for auto updates
Hudson works with build files to execute against different targets, more like a service broker that works with different services to run different kinds of jobs. Targets can be a set of instructions grouped together under a common task umbrella. For example a ‘debug’ target for building the project in debug mode with configurations that are specific to development environments and having a ‘Deploy’ target for running the distributable product.

You could use either MSBuild or Nant to build and deploy the project.

Difference between using MSBuild & NAnt with Database Projects: With MSBuild the database project file AdventureWorks.dbproj works as the build file and one can make changes or customize it in the VS IDE itself which is really helpful. With NAnt and DBProjects,  one has to create a build file (.build) manually or use a free NAnt build file editor; whichever the case you would have to include the MSBuild task in the Nant build filee to build and deploy the database project. (Tasks are specific XML tags that are passed as instructions for a particular unit of work in a build file). The reason MSBuild is preferred for a database project .dbproj vs. a dbp project type lies in the way the deployable database file is created (see previous blog post) .

For example, if a new table script is added to the database project and you intend to include it in the deployment script (Adventureworks.sql) you need to rebuild the database project (Right click –> Rebuild). Only this action will recreate the deployable AdventureWorks.sql with all the database objects and include the new table that was recently added. And to do this action from the command line you need the MSBuild command. So… If you already have a provision for running the MSBuild task from Hudson directly, why would you use Nant. The answer – in this case since we are ‘only’ deploying the database project we are fine. But, if we had a different range of project types with different technologies, like a Java project for instance, then using Nant would make sense. It all depends on the composition of your build steps\events.

Before you can access the MSBuild tasks in NAnt you need to install the NAntContrib task libraries and include them in your NAnt build file. Since all of this sounds a bit complex, lets just use MSBuild for now and follow the steps below to add the build steps:

1. Click on the AdventureWorks-Dev-CI-Build project and click on Configure.

2. Assuming SVN is setup correctly and the database project is in source control (if not setup the repository from here and then access it from here), update the Subversion Repository URL as follows.


3. Save and go back to the dashboard to test the changes. Hit the ‘Schedule a build’ button to start the project (Play button)


The second column ‘W’ (Weather Report) should go to sunny if everything was set up correctly.


Note: Click on the Build History –> Console Output. It should display all the files from AdventureWorks project being checked-out to a workspace location. In the event of a build failure, this would be the place to check for the failure error.

4. Make sure that the database project builds and deploys without errors from VS IDE.

5. Add build step: Go back to the project configuration and add an MSBuild step.


6. To add a trigger to poll for changes on source control, follow this final configuration or skip it.


7. Finally, save and go to dashboard. Start the build and verify the output.

This completes the setup of a database project with continuous integration using Hudson, Subversion and MSBuild (I know I promised using NAnt, but trust me – in our case it just would have been a bit more painful to go over. Helpful NAnt tutorial).

This was a very basic, barebones CI setup. Once you start delving into configurations, parameters, different environments it gets more complex and interesting. The database project is the core component of all of this CI setup process. The rest of the pieces like the CI server, source control, build tools and issue tracking systems are customizable. Depending on the environment and other influential factors you may decide to go with one product set versus another. This was just one way of getting started on CI on a database project with Visual Studio. There are numerous CI server products available as open source. A comparison is provided
here. Hope this helped. Thank you and keep reading.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: