Using Team Build to Build Database Projects

    This article is taken from the book Team Foundation Server 2008 in Action by Jamil Azher. As part of the introduction to VSTS 2008 Database Edition, this segment shows how you can customize the Team Build project file to automatically deploy the database build script to a target database.

Team Build enables you to create public builds in dedicated build machine. Team Build fetches the latest source files from TFVC and generates the SQL build script in a designated build machine. The build machine needs to have a local instance of SQL Server installed so that DB Pro can create a local design database. The build machine also needs to have DB Pro installed.

As is the case when building C# or VB.Net projects, Team Build uses MSBuild to build database projects. However, as discussed, for database projects, the output is a .sql script, not an executable file. By default, Team Build does not deploy the schema changes to the target database (although you can customize the Team Build script to do so).

Choosing a Configuration

When creating a build type for a database project, you need to be aware of configuration issues. When creating a database project, you get a configuration named “Default” (see Figure 3.26). However, when creating a new Team Build project, your configuration options are “Debug”
and “Release” (see Figure 3.27). As a result of this mismatch, when you try to build the Team
Build project, it fails.

Figure 3.26 Use the Configuration Manager to create configurations containing different set of options.


Figure 3.27 Team Build allows you to specify two configurations-Debug and Release.

You can solve the configuration mismatch problem in several ways:

  • Option 1: Using the Configuration Manager in Visual Studio, create a new “Debug” or “Release” configuration in the database project. Select Configuration Manager from the Solution Configurations drop-down in the Visual Studio toolbar. In the Configuration Manager dialog box, select New from the Active Solution Configuration drop-down to launch the New Solution Configuration dialog box (see Figure 3.28). You can copy the options from an existing configuration.
  • Option 2: Modify the configuration section of the build script to use the “Default” configuration, as shown in Listing 3.3. You can also simply type “Default” in the configuration dialog box in Team Build.


Figure 3.28 When creating a new configuration, you can inherit the settings from an existing configuration.

Listing 3.3 The Build Script Modified to Use the Default Configuration.

<ConfigurationToBuild Include="Default|Any CPU">
   <FlavorToBuild>Default</FlavorToBuild>
   <PlatformToBuild>Any CPU</PlatformToBuild>
</ConfigurationToBuild>

Specifying a Target Database

When you specify a target database name in the Build tab of the project properties dialog box (refer to Figure 3.9 displayed previously), your entry is stored in the database project user file (the .dbproj.user file). However, the .dbproj.user file is not used by Team Build. Consequently, the generated build script may contain invalid entries.

To solve this problem, modify the database project file (the .dbproj file) and specify valid values for TargetDatabase, TargetConnectionString, and DefaultDataPath properties (see Listing 3.4).

If you are using TFS 2005, move the property definition block after the line containing the Import statement for the Microsoft.VisualStudio.TeamSystem.Data.Tasks.targets file. As
discussed, the TargetDatabase property is unconditionally overwritten in the Microsoft.VisualStudio.TeamSystem.Data.Tasks.targets file. Check in the database project file to TFVC and make sure that developers do not accidentally overwrite it.

If you are using TFS 2008, you do not need to move the property definition block. Moreover, you can specify the values for TargetDatabase, TargetConnectionString, and DefaultDataPath properties in the Queue Build dialog box or in the TFSBuild.rsp file in Team Build.

Listing 3.4 Specify Valid Values for TargetDatabase, TargetConnectionString
and DefaultDataPath Properties in the Database Poject (.dbproj) File.

<PropertyGroup Condition=" '$(Configuration)' == 'Default' ">
   <DefaultDataPath>
      <Valid_Path_in_Target_Machine>
   </DefaultDataPath>
   <TargetDatabase>
      <Your_Target_Database_Name>
   </TargetDatabase>
   <TargetConnectionString>Data
      Source=<Your_DBServer_Name>;Integrated
      Security=True;Pooling=False
   </TargetConnectionString>
</PropertyGroup>

Deploying Schema Changes

As discussed, by default, Team Build does not deploy the schema changes to one or more target databases. However, you can easily achieve this goal by overriding the AfterDropBuildtarget, and launching the MSBuild task
with the Deploy

Listing 3.5 To Deploy the Schema Changes, Override the AfterDropBuild Target and Invoke
the MSBuild Task with the Deploy Target.

<Target Name="AfterDropBuild">
    <MSBuild
        Projects="$(SolutionRoot)\SolutionName\
        DatabaseProjectName\DatabaseProjectName.dbproj"
        Properties="Configuration=Default;OutDir=
        $(SolutionRoot)\..\binaries\Default\"
        Targets="Deploy" />
</Target>

More by Author

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Must Read