Jon Liperi, a tester on Team Build, has put together the post below that explains a number of the issues around using Visual Studio Team Edition for Database Professionals (DBPro) with TFS Build.  Jon previously worked on the DBPro team, so he knows his way around it quite well.  Here are the issues that he covers.

• Issue #1: Team Build service account does not have the required SQL Server permissions or cannot connect to SQL Server
• Issue #2: Values for TargetDatabase, TargetConnectionString, or DefaultDataPath are missing or incorrect
• Issue #3: The New Build Definition dialog does not provide a “Default” configuration option
• Issue #4: The Deploy target is not invoked when built via Team Build
• Issue #5: Database Unit Tests cannot find database project files, data generation plans, or the database instance(s) to be used for running tests when run via Team Build

This information applies to both the 2005 (8.0) and the 2008 (9.0) versions of VSTS and TFS.

Building Database Projects with Team Build by Jon Liperi

Recently, we have seen more questions about building database projects with Team Build. It is absolutely possible to build these project types with Team Build. However, you’ll need to have VSTE for Database Professionals (aka DBPro) and SQL Server installed on the build agent. There are also several known issues. In this blog post, I’ll describe these issues and their workarounds. Please let me know if there are additional issues you encounter or if the work-around steps need a bit of correction by commenting on this blog entry or posting on the Team Build forum:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=481&SiteID=1

To start, I’ll point you to a few links specific to database projects, including some existing documentation around Team Build integration.

Visual Studio Team System Database Edition (MSDN documentation)
http://msdn2.microsoft.com/en-us/library/aa833253(VS.90).aspx

How to: Deploy Changes using Team Foundation Build (MSDN documentation)
http://msdn2.microsoft.com/en-us/library/aa833289(VS.90).aspx

Visual Studio Team System - Database Professionals Forum
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=725&SiteID=1

Issue #1: Team Build service account does not have the required SQL Server permissions or cannot connect to SQL Server

Database projects create a scratch database on the local SQL Server instance when building. Therefore, the build will fail if the Team Build service account does not have the appropriate permissions on the SQL Server instance running on the build machine. You may see an error message similar to the one below in the build log:

The "SqlBuildTask" task failed unexpectedly.

Microsoft.VisualStudio.TeamSystem.Data.Common.Exceptions.DesignDatabaseFailedException: You have insufficient permissions to create the database project. For more information, see the product documentation.

To resolve this issue, grant the required SQL Server permissions to the Team Build service account. In Orcas, the Team Build service runs by default as NT AUTHORITY\NETWORK SERVICE. A quick way to fix this is to create a SQL login for the service account that has sysadmin privileges. However, if you want to grant only the minimal permissions, detailed SQL Server permissions for DBPro are described here:

http://msdn2.microsoft.com/en-us/library/aa833413(vs.90).aspx

Additionally, you may need to ensure that a necessary registry key, which points to the local SQL Server instance to use for build, exists on the build machine. To configure the build account’s HKCU hive to point to the correct instance, you can either:

1. Start the Visual Studio IDE as that user. Set the instance name by opening Tools | Options and navigating to Database Tools | Design-Time Validation Database.
2. Run the following command from a command prompt.
   (Note: You may need to replace 9.0 with 8.0 depending on the version of DBPro you are using.)
   
   runas /user:<Team Build service account> "REG ADD HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\9.0\DBPro\DialogPage\Microsoft.VisualStudio.TeamSystem.Data.DBProject.Settings.DataConnectionOptionsSettings /v DefaultSqlServerName /d <instance name>"
   
   The value for <instance name> is just the name of the instance. For example, if the instance is “.\SQLEXPRESS”, replace <instance name> with “SQLEXPRESS”. If it is an unnamed instance, enter an empty string.

Issue #2: Values for TargetDatabase, TargetConnectionString, or DefaultDataPath are missing or incorrect

Database projects store any non-default values for the TargetDatabase, TargetConnectionString, and DefaultDataPath properties in a <ProjectName>.dbproj.user file which is not checked into version control (as the values may be different for each user). Therefore, these values are missing when built via Team Build resulting in the following error message:

TSD257: The value for $(DefaultDataPath) is not set, please set it through the build property page.

To build successfully from Team Build, you must either:

1. Copy these properties from the <ProjectName>.dbproj.user file and add them to the <ProjectName>.dbproj file for the configuration that you want to build.
2. Pass these properties as MSBuild command line arguments by entering them in the Queue Build dialog (Orcas only) or by storing them in the TFSBuild.rsp file. For example:
   /p:DefaultDataPath=<path>; TargetDatabase=<databaseName>;AlwaysCreateNewDatabase=true;TargetConnectionString="<connection string>"

[After this was posted, Peter Moresi, a developer for Microsoft's AdECN Exchange, sent us the following alternative that may better fit your needs.]

There is also a third alternative.  Using the first solution of copying the properties in the .dbproj file may cause problems when checking out and checking in the project file, depending on how your team works. The alternative is to create, prior to compilation in the build process, a .user file that includes the required settings. This solution is very similar to the solution to Issue # 5.

1. Create a new file, called $(MSProjectFile).teambuild.user, in the database project that contains the settings for TargetConnectionString, TargetDatabase, and DefaultDataPath. You may also want to include AlwaysCreateNewDatabase so you can explicitly manage this setting for the build.  You'll need to use .user file generated for your project and add the PropertyGroup shown in bold below as the contents for this new file.
   
   <Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
     <PropertyGroup>
       <FileGroupsFileNames>
         <FileGroupFileNameList Version="1" xmlns="">
           <AllFileNames>
             <FileGroupFileName>
               <FileGroupName>PRIMARY</FileGroupName>
               <FileGroupFileName>903b7a26-677c-46d9-85ee-3ada32272e76</FileGroupFileName>
             </FileGroupFileName>
           </AllFileNames>
         </FileGroupFileNameList>
       </FileGroupsFileNames>
       <DesignDBName>MyDatabase_DB_35eb0dc1-5552-427d-9071-c8874464e107</DesignDBName>
     </PropertyGroup>
     <PropertyGroup Condition=" '$(Configuration)' == 'Default' ">
       <AlwaysCreateNewDatabase>True</AlwaysCreateNewDatabase>
       <DefaultDataPath>c:\program files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\</DefaultDataPath>
       <TargetConnectionString>Data Source=localhost;Integrated Security=True;Pooling=False</TargetConnectionString>
       <TargetDatabase>MyDatabase</TargetDatabase>
     </PropertyGroup>
   </Project>
   
2. Create the file Microsoft.VisualStudio.TeamSystem.Data.TeamBuildTasks.targets as described in Issue #5, Step 4 described later in this post.  Then add the following statements to the end of that file (inside the closing </Project> tag).
   
   <PropertyGroup>
     <BeforeBuildTeamBuildTargets>RenameTeamBuildUserFile</BeforeBuildTeamBuildTargets>
   </PropertyGroup>
   <ItemGroup>
     <__TeamBuildUserFile Include="$(MSBuildProjectFile).user"/>
   </ItemGroup>
   <Target Name="RenameTeamBuildUserFile">
     <CreateItem Include="$(MSBuildProjectFile).teambuild.user">
       <Output ItemName="TeamBuildUserFile" TaskParameter="Include" />
     </CreateItem>
     <Copy SourceFiles="@(TeamBuildUserFile)" DestinationFiles="@(__TeamBuildUserFile)" />
   </Target>
   
3. Modify the .dbproj file and add these elements to the end (inside the closing </Project> tag).  You'll need to change v9.0 to v8.0 if you are using the 2005 (v8.0) release rather than the 2008 release (v9.0).
   
   <Import Condition="'$(TeamBuildConstants)' != ''" Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v9.0\TeamData\Microsoft.VisualStudio.TeamSystem.Data.TeamBuildTasks.targets" />
   <Target Name="BeforeBuild" DependsOnTargets="$(BeforeBuildTeamBuildTargets)"></Target>
   

Issue #3: The New Build Definition dialog does not provide a “Default” configuration option

Database projects do not define Debug or Release configurations. They only define “Default”. When creating a new build definition, you may notice that the only listed options are “Release” and “Debug”. To work-around this, you can either:

1. Manually type in “Default” in the dialog.
2. Ensure the “Release” and “Debug” solution-level configurations are set to build the “Default” configuration of the database project.

Issue #4: The Deploy target is not invoked when built via Team Build

The default target executed by MSBuild is Build. Therefore, database projects may not deploy by default when built via Team Build. To invoke the Deploy target from Team Build, you must either:

1. Ensure your solution configuration is set to invoke both the Build and Deploy targets on the database project.
2. Override AfterDropBuild to explicitly invoke the Deploy target. Instructions for overriding Team Build targets can be found at:
   http://msdn2.microsoft.com/en-us/library/aa337604(VS.90).aspx
3. Modify the TFSBuild.proj file to list the individual projects to build and their targets instead of listing the entire solution. For example:
   <SolutionToBuild Include=”foo.dbproj”>
   <Targets>Build;Deploy</Targets>
   </SolutionToBuild>
4. Edit the .dbproj file to make the DefaultTargets in their dbproj file Build;Deploy. For example:
   <Project DefaultTargets=”Build;Deploy”...>
   This would apply outside of Team Build as well, but it would have the desired effect.

Issue #5: Database Unit Tests cannot find database project files, data generation plans, or the database instance(s) to be used for running tests when run via Team Build

Database unit tests provide the ability to deploy a database project and/or run a data generation plan prior to running tests. Database unit test projects store the location of the referenced .dbproj and .dgen files as relative paths in the app.config file.

When building a database project using Team Build, the output and source files are stored in a different directory structure on the build machine. The test files are located in a TestResults folder while the source files are located in a Sources folder. When the unit tests are run from the TestResults folder, the relative path to the referenced .dbproj and/or .dgen files in the <assemblyname>.config file is no longer correct. This causes the tests to fail with one of the following messages:

Database deployment failed. Path ‘<path>\Database1.dbproj' is not a valid path to a database project file.

Data Generation failed. Path '<path>\Data Generation Plans\DataGenerationPlan1.dgen' is not a valid path to a data generation plan file.

Additionally, the app.config file also stores connection string information that points to the SQL Server instance to be used for running tests. If the connection strings are not valid when the tests are run from the build machine during a Team Build, the unit tests will fail with the following message:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

(Note that you may want the connection string to change depending on if they are running locally or through Team Build. For example, you may want tests to use your local SQL Server instance during development but use a remote shared instance when running via Team Build. The solution to this issue can also be used to achieve this result.)

To fix this issue, users need to manually create an app.TeamBuild.config file with the correct path locations and connection strings to be used when builds are created via Team Build. This file will then be renamed to <AssemblyName>.config through a post-build target, overwriting the .config file that contains the incorrect values.

1. Create a file named app.TeamBuild.config by copying the existing app.config in your unit test project. Add it to your unit test project in version control.
   
2. In the app.TeamBuild.config file, change the relative path to the .dbproj and .dgen files by adding a folder level for the Sources folder and a subfolder with the same name as the solution. For example,
   
   Before:
   "..\..\..\Database1\Data Generation Plans\DataGenerationPlan1.dgen"
   
   After:
   "..\..\..\Sources\Database1\Database1\Data Generation Plans\DataGenerationPlan1.dgen"
   
   Before:
   "..\..\..\Database1\Database1.dbproj"
   
   After:
   "..\..\..\Sources\Database1\Database1\Database1.dbproj"
   
   Additionally, you can modify the connection strings in the app.TeamBuild.config to the strings that should be used from the Team Build machine. Check the changes back into version control.
   
3. Check out the unit test project file and modify the end of the file to include these lines right before the </project> tag. Check the changes back into version control.
   (Note: You may need to replace v9.0 with v8.0 depending on the version of DBPro you are using.)
   
   <Import Condition="'$(TeamBuildConstants)' != ''" Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v9.0\TeamData\Microsoft.VisualStudio.TeamSystem.Data.TeamBuildTasks.targets"/>
   
   <Target Name="AfterBuild" DependsOnTargets="$(AfterBuildTeamBuildTargets)">
   </Target>
   
4. On the Team Build machine, create a file with the code below and name it Microsoft.VisualStudio.TeamSystem.Data.TeamBuildTasks.targets. Save the file in folder %Program Files%\MSBuild\Microsoft\Visual Studio\v9.0\TeamData
   (Note: You may need to replace v9.0 with v8.0 depending on the version of DBPro you are using.)
   
   <?xml version="1.0" encoding="utf-8"?>
   
   <Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
   
   <UsingTask TaskName="DataGeneratorTask" AssemblyName="Microsoft.VisualStudio.TeamSystem.Data.Tasks, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
   
   <PropertyGroup>
     <AfterBuildTeamBuildTargets>RenameTeamBuildConfig</AfterBuildTeamBuildTargets>
   </PropertyGroup>
   
   <ItemGroup>
     <__TeamBuildConfig Include="$(OutDir)$(TargetFileName).config"/>
   </ItemGroup>
   
   <Target Name="RenameTeamBuildConfig">
     <CreateItem Include="app.teambuild.config">
       <Output ItemName="TeamBuildAppConfig" TaskParameter="Include" />
     </CreateItem>
     <Copy SourceFiles="@(TeamBuildAppConfig)" DestinationFiles="@(__TeamBuildConfig)" />
   </Target>
   
   <Target Name="DataGen">
     <DataGeneratorTask
      ConnectionString="$(ConnectionString)"
      SourceFile="$(SourceFile)"
      PurgeTablesBeforePopulate="$(PurgeTablesBeforePopulate)"
      Verbose="$(Verbose)" />
   </Target>
   
   </Project>

[UPDATE 8/29/08] I've added another alternative to solving issue #2 courtesy of Peter Moresi.