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.
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 Forumhttp://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=725&SiteID=1
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 Forumhttp://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.
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:
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:
[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.
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:
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:
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.
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.
[UPDATE 8/29/08] I've added another alternative to solving issue #2 courtesy of Peter Moresi.