Deploy a database project with TFS Build

When you develop web application you often have this scenario. Some people are testers, they are testing the application in dev all day, and they want always to test latest version. Moreover testers usually fills data into web application, so they want their data to be preserved between various deploy. If you simply clear all data in test database you will end with a lot of furious tester :) so do not even think to do this.

If a developer needs to change database schema, we need to automate not only the deploy of the web application, but we need also to sync test database with the latest changes. This is necessary so the test site can work with the new version, but old data is preserved for the happiness of the testers.

This is quite simple using TFS and Database project. The situation is this one


When a developer does a check-in, the build server grabs the tip from TFS, then it runs the builds script and deploy the new version of the site in the test web server. At the same time test database gets updated with the latest database schema.

To accomplish database deployment is necessary to modify build script by hand. If you create a build script with the wizard and ask to the build machine to build a database project, the only stuff you will get is a build of the database project, not a deploy, and all artifacts are moved in the drop location.


What you really need is the ability to automatically deploy changes to a specific database, this task is quite simple and you can find some details here in msdn, but we need to insert this command into the msbuild script. Here is the code

<Target Name="AfterDropBuild">
    <Message Text="Deploy of project database" />

        Properties="OutDir=$(DropLocation)\$(BuildNumber)\debug\;TargetDatabase=NorthwindCiTest;DefaultDataPath=C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data;TargetConnectionString=Data Source=VM2003R2_1\SQLTEST%3Buser=sa%3Bpwd=Pa$$w0rd;DeployToDatabase=true;" 
        Targets="Deploy"  />

Wow, it is really simple, all you need is a simple call to a <MSBuild> task inside the AfterDropBuild target. The MSbuild task needs only three properties, the first is the Targets one, that specifies witch action we want to do on the project, in this situation is “Deploy”. Then in the Peojects property we must specify the full path of the database project file, and it is quite simple using the SolutionRoot property that points to the root of the source. Finally we need to set Properties property, the most important one, used to override project properties.

In my example I need to deploy in a server with a different instance name and a different Data Path from the original one included in the project. Properties property of msbuild permits you to specify different pair property=value separated with semicolon (;). The properties I need to override are:

OutDir=The directory where we can find all the files resulting from the compilation of the database project, the DropLocation is the easiest way to specify them

TargetDatabase= the name of the database used to deploy the project, is NorthwindCiTest, where CI is Continuos Integrated :)

DefaultDataPath=the phisical path where to locate files. In the original project I use D:\ disk that is the one used to store databases in dev machines.

DeployToDatabase= a boolean that must be set to true to ask to update target database

TargetConnectionString=the connection string used to access the sql server, in this example I used sa because I used some test VM, do not use sa in real environment.

A little note must be done for the TargetconnectionSTring, since a connection string usually contains semicolon, and since semicolon is used by msbuild to separate properties, to use semicolon in connection string or in other property value, you need to escape it with the code  %3B the usual Url Escaping.

Now you can fire a build, and verify what is happened in the log.

Task "Message"

  Deploy of project database

Done executing task "Message".

Task "MSBuild"

  Global Properties:



    DefaultDataPath=C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data

    TargetConnectionString=Data Source=VM2003R2_1\SQLTEST%3buser=sa%3bpwd=Pa%24%24w0rd


In this first part you can verify that all variables are correctly overridden. If you scroll down the result file you can find details on the update operation

Task "SqlDeployTask"

  Deployment script generated to:


  Creating NorthwindCiTest…

  Creating dbo.Categories…

  Creating dbo.Contacts…

  Creating dbo.CustomerCustomerDemo…

  Creating dbo.CustomerDemographics…

  Creating dbo.Customers…

  Creating dbo.Employees…

  Creating dbo.EmployeeTerritories…

  Creating dbo.Order Details…

  Creating dbo.Orders…

  Creating dbo.Products…

  Creating dbo.Region…

  Creating dbo.Shippers…

  Creating dbo.Suppliers…

  Creating dbo.Territories…

  Creating dbo.Categories.CategoryName…

This is the first time I run the script against the server, so all tables gets created, I can verify that the database was correctly created going to the test server


Et voila, the database was automatically deployed to the test instance of sql server thanks to TFS Build and with no effort :).



Correct use of a Source Control System, branching for long time task

Source control system is probably developer’s best friend, but quite often people use only a small percentage of its functionalities. One of the most missed feature is a correct use of a branch. Let’s make a concrete example. Suppose that developer A needs to implement a big feature, it estimates a week to complete it, and while he is working at this feature he needs also to modify some basic part of the system. The problem is: until the developer has finished the new feature the code is in not in stable condition, and it can even not compile correctly.

A standard way to proceed is the following one.

  1. dev A issue a get latest
  2. dev A begins to modify code in local copy
  3. dev A periodically executes a Get Latest and merge changes made by others with the code in the working copy.
  4. dev A finished the new feature, executes a final Get Latest, verify that everything is ok and finally commits all modifications.

What is wrong with this process?

  • until A commits everything in the trunk, new code is only in his hard disk, if it fails dev A losts everything.
  • if the new feature needs to be developed by more than a single developer, say A and B and C it is impossible to work this way.
  • if A wants another developer to review his code, he needs to send all modification to other dev for review, maybe zipping local folder.

This situation can be solved with a branch in a more efficient way. A branch is nothing than a special copy of a file or folder located in source control. I said special because Source Control System keeps track of the original source of the copy, and files are not really copied until someone makes a modification to the copied file. This permits to minimize the impact on the size of source control system but at the same time the SCM knows that a file is related to its original counterpart. To create a branch in TFS simply go to the source control view, right click a folder and choose Branch. You got this window


In this example I’m branching the entire src folder to a folder called Branches/TestBranch, and I’m branching from the latest version. After you commit the branch, you will find that a complete copy of the src folder is now present in the branch folder


Ok, you can now open the solution in the branch and begin working to the new feature, even if the code is not compiling, you can check-in with no problem. In my example I’ve modified a file to make solution not compile, then I look at the pending changes.


I can commit with no problem because modified file will be checked-in only to the branch. Other developers can continue to work in the trunk without being disturbed by my modification. Now suppose that another developer modify the same file I’ve modified in the branch. Periodically you need to verify if someone has modified the trunk to keep your branch in sync. Remember that you need to merge quite often, because in this way you always merge a little bit of code, if you wait for your work to be finished you will have to do a Huge merge, and it can become a pain. Now I simply click on the src folder and choose compare


And I decided to compare it with the server version that I’ve commited to the branch version of the project.


This shows me all differences between the two folders.


Ok two files are different, now you need to understand what to merge, since I know that I’ve modified AssertBaseTest it is normal that is different, but the ConstraintBaseTest is surely being modified by someone else. If you need information you can right click the file and view history, you have the possibility to look for history in the source (left side) or the target (right side). In this situation the source is the trunk, while target is my branch. Let’s see the history of the AssertBaseTest.


Red one is the history for the source, you can see that in changeset 142 guardian modified a test. In blue one you see modification made in the branch, in changeset 140 there is the branch, while in 141 I changed a file to make it not compile. Since other people modified the file I need to do a merge. So I return to the code view, find the file in the source I want to merge and right-click and choose merge.


Since Tfs knows that I branched this file it presents me this windows


You can verify that it permits me to choose only right target branch, I choose to branch all changeset, or you can even choose to merge to a specific changeset. Now when I merge the file a conflict is raised, because modifications are incompatible (me and other dev modified the same file), so you can simply edit conflicts in the standard merge tool. After merge is complete you can verify that file was really merged looking at his history.


If you merge now ConstraintBaseTest.cs that was modified in the trunk no conflict happens and modification in the trunk are ported in the branch. Clearly in real situation you will do a merge of the entire src folder, and not file by file :) this is only to show you the details.

When you finished your new feature you can simply do a final merge of all the trunk, verify that everything is ok and now you are ready to reintegrate the branch into the trunk.

Merge operation is a very specific operation that assures to SCM that all modification of the trunk are moved and merged in the branch. After the last merge, you verify that everything in the branch is OK, and now you are ready to reintegrate the branch to the trunk. To reintegrate you simply right click the branch folder in source control view, and choose merge, and merge again with the original src directory. Now you will get conflicts for each file that was modified in the branch and in the trunk, but since you already merge changes from the trunk, and verified that everything is ok, you can now overwrite trunk version with merge version.


Now the trunk contains your new feature, but without being disturbed during its developement. This way to proceed has the following advantages.

  • More than one developer can work at new feature working in the branch
  • The branch is under SCM, so you get history, conflict handling, and it is backup with other source.
  • You have full history of the branch, so you can track back every modification made by source code in the branch to create new feature.
  • You can associate check-in of branches with team foundation work item.
  • If you need someone review your code you can simply tell him to download the branch and do a review.



Team foundation server and excel

One of the greatest features of Tfs is that it is a single product that is able to manage the whole ALM of an application, but another strength point is high level of integration with other tools. One of the coolest feature is the ability to use Excel to manage workitems, without the need to even open visual studio.


It is enough for you to install the team exploer, and you will get office integration. Excel shows a new tab in the ribbon named “Team” that permits you to work with team foundation server. You can choose item specifying a standard workitem query, or you can directly create new workitem or load by id. As an example you can view all opened bugs.


Thanks to high level of integration, you can edit data with combo, in this screenshot you can verify that I can assign a bug to only valid user of tfs. If you want to change the state of a bug you can see that the state column is missing, all you need to do is to select what column to show.


This means that you can edit all information of every workitem, and when you are finished you can press the “Publish” button to send updated data to TFS. This is useful for massive operations on works items, since working in excel is simplier and quickier respect to manage work items in visual studio. This is especially useful for project manager, or similar project figures that are not programmers and does not want to install visual studio only to manage workitems.

Creating a new task is as simple as creating a new line on the worksheet, omitting the workitem id and then press publish, immediately you will see that the id field will be populated, thus confirming that new workitems are really added to tfs.



Tfs Web access and some tinyurl magic to signal test failure

In the last post of Tfs series, I showed how  to build a msbuild custom action to tweet when build test run fails. Using twitter can seem strange at first, and probably not so professional to associate to a project build, but this is not true. Twitter is a free service that can be consumed from many devices, you can use tool such as twitterDeck to manage all of your tweets in your desktop environment and to categorize them, but you can also use any mobile phone, so it is probably the most serious way to signal build problems to everyone is interested in the build process of a certain project.

The only great limitation of twitter is that it cannot convey more than 140 characters, and it is difficult to give enough information to the user in such little space. Here is a typical result from a failure build


When anyone interested in the project looks at this message, he immediately know that something is gone wrong, but how can he know what exactly is gone wrong? To solve this problem we must do a couple of things.

The first step is installing the Visual Studio Team System Web Access 2008 SP1 Power Tool, an extension to tfs that permits Tfs management from a web interface like this one.


Since this site can use windows authentication to manage user permissions, you can even expose this site to the entire internet, so everyone that has permission, can manage Tfs server simply from a pc with a browser and internet access. I do not want to show you all the possibilities that the web access power tool can give to you, but I immediately noticed an interesting stuff: I can browse into the team build, then I can look at details of every build:


This pages shows me full details about the build, now if you look closely at the query string, you can see that it specify the build to show with the parameter builduri=vstfs///build/build/41 ;) now is the time to do some other custom action. To tweet such a long url is impossible, but you can use tinyurl to shorten it to be tweeted without problem.

This means that I need another custom msbuild action, this one is slightly different from the one used to tweet the message, because it need to returns the string with the new url (the one returned by the tiny process). Here is the full task definition.

public class TinyUrlTask : Task
    public String Url { get; set; }

    public String TinedUrl { get; set; }

    public override bool Execute()
        TinedUrl = TinyUrl.MakeTinyUrl(Url);
        return true;

The only difference with the tweeter task is the presence of a property marked with [Output] parameter, and in the Execute() method I simply call an external class that shorten the url with TinyUrl and then I stored the result of the task into TinedUrl output property. With such a definition I can modify the build script with this custom action to execute after tests are run

<Target Name="AfterTest">

    <!-- Refresh the build properties. -->
    <GetBuildProperties TeamFoundationServerUrl="$(TeamFoundationServerUrl)"
                                 Condition=" '$(IsDesktopBuild)' != 'true' ">
        <Output TaskParameter="TestSuccess" PropertyName="TestSuccess" />

    <TinyUrlTask Url="$(BuildURI)">
        <Output TaskParameter="TinedUrl" ItemName="TinedBuildUrl" />
        Condition="'$(IsDesktopBuild)' != 'true' and '$(TestSuccess)' != 'true'"
        Tweet="Build TestFailed:@(TinedBuildUrl)" />

The tiny url task is called passing the url that points to the build result, fortunately the only parameter that this page needs is the BuildUri, that is passed with querystring and is contained in the msbuild variable $(BuildURI). The result of this task is retrieved with the Output node, where you can specify the output property you want to know with the TaskParameter attribute and the propertygroup where to store this value in the ItemName attribute. The final effect is that the @(TinedBuildUrl) propertyGroup contains the encoded url returned by tinyurl server after the TinyUrlTask is invoked. Now you can simply call the same TweetTask of the previous post to tweet the link to the web page that shows the details of the build. If you commit some code that will make a single test fail you got a tweet like this.


The great difference from the previous version, is that the tweet now contains a link that can be immediately clicked to browse build results.


Thanks to tinyurl, that long url was shortened into a twittable one and you can look at build detail with a single click, how cool :) . Now you can simply create a twitter account for each build, and everyone interested in that project can follow the corresponding twitter account to be notified of each build failure, and for each tweet he got clickable link that immediately takes him to the page with the results of the failing build.

Managing projects in such a way is real fun!!!!