If you want to deploy a database project into a target sql server instance during a tfs 2010 build, you can use with success the basic MsBuildTask, similar to tfs2008.

I decided to deploy the database, only if the tests are ok and the build is ok, so I place a condition activity under the test phase and I set the condition to pass only if the test and build status are different from Failed

image

Now I need to know where the dbproj will be located in the disk during the build, and since I know the repository path, I can use a specific TFS build action to convert the source control path into physical path. The source control path is “$/Experiments/NorthwindTest/NorthwindTest.Database/NorthwindTest.Database.dbproj” so I can drop a “ConvertWorkspaceItem” activity to convert this path to the physical one. First of all I need to declare a variable where to put the result.

image

I called this variable “dbProject” (String type). Now I can configure my ConvertWorkspaceItem activity I dropped in the “then” part of my condition activity.

image

This activity needs three variables, the first is the source control path you want to translate, the second is the name of the variable that will contain the result and the third is the Workspace to use (is stored into the global variable Workspace).

Following this activity I dropped a WriteBuildMessage activity, used to write something into the build log (I wrote the exact path of the database project that is to be deployed)

image

This is only informational, but it is useful to check during the build process.

The most important activity is the MsBuildActivity that will do the real work. This activity invokes Msbuild.exe that in turns will target the database project to deploy. The most important property is the CommandLineArgument where you must specify all details of the deploy

"/p:TargetDatabase=NorthwindTest" +
" /p:""DefaultDataPath=C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATA""" +
" /p:""TargetConnectionString=Data Source=10.0.0.99SQL2008,49400%3Buser=sa%3Bpwd=Pa$$w0rd""" +
" /p:DeployToDatabase=true"

These properties are used to specify the name of the database, the datapath, the connectionstring where to deploy the database, and they override the standard one defined on the project. As you can see in connection string I used the sa password, that is strongly discouraged for security reasons in production environment. You can use integrated security if the user used to run the team agent has the right to create a database in the target instance.

Finally these are all the properties of the msbuild activity.

image

LogFile is really important, because you want a msbuild log file of what is happened during the deploy. You can check this file in the logs subfolder of the drop location

image

If you open deploydatabase.log, you can find all the information you need on what is happened, here is a little extract.

Build started 1/4/2010 7:08:38 AM.
Project "C:Builds1ExperimentsDeployDatabaseSourcesNorthwindTestNorthwindTest.DatabaseNorthwindTest.Database.dbproj" on node 1 (Deploy target(s)).
DspDeploy:
  Deployment script generated to:
  C:Builds1ExperimentsDeployDatabaseBinariesNorthwindTest.Database.sql
  Creating NorthwindTest…
  Creating [dbo].[Categories]…
  Creating PK_Categories…
  Creating [dbo].[Categories].[CategoryName]…
  Creating [dbo].[CustomerCustomerDemo]…
  Creating PK_CustomerCustomerDemo…
  Creating [dbo].[CustomerDemographics]…
  Creating PK_CustomerDemographics…
  Creating [dbo].[Customers]…
  Creating PK_Customers…

A really important property is the Target, that is set to New String() {“Deploy”}. A database project supports some different targets, as an example the build, deploy, dataGeneration and StaticCodeAnalysis, each of them will perform a different task.

The project to deploy is specified in the Project property, and as you can see I use the variable dbProject that was populated with the ConvertWorkspaceItem.

If you check build details you can find all the information you need about the build process

image

From here you can verify project directory, and all the options that are passed to the msbuild.exe. You can now schedule the build and verify that the database is deployed on target server.

alk.

Tags:

Tags: , ,

7 Responses to “Deploy a database project with tfs build 2010”

  1. Hey,

    Awesome. I would like to know the command line argument syntax if I need to use integrated security. Thanks.

  2. I am also having an issue firing my “If can deploy” activity. I am not running any DB Tests, so I placed the activity immediately before the “If Not Disable Tests”. Is there a better place you recommend I place this?

  3. You can place wherever you want, I did the check with the “If can deploy” because usually I want my database to be deployed in test server only if everything is ok, but you can place wherever you want, clearly after the compile action.

    alk.

  4. Thanks. I was able to get this working finally. I placed it in the “Finally” section of the compile action.

  5. Thnx!

    I changed the commandline to this:
    “/p:TargetDatabase=” + TargetDatabase +
    ” /p:”"TargetConnectionString=Data Source=” + TargetServer + “;Integrated Security=true;”"” +
    ” /p:DeployToDatabase=true”

    (and made arguments for TargetDatabase and TargetServer so they can editted outside the xaml)

    Next to that I changed the convert workspace item activity to create the dbProject variable with:
    BuildDirectory + “\Sources\SQL2005DB\SQL2005DB.dbproj”.

    This way the same xaml file will work for a branched solution.

  6. Hi.

    I had another question. I am facing an issue where the workspace created by my DB build isn’t getting deleted. I’m using the deleted workspace activity and setting the DeleteLocalItems to True and the Name to WorkspaceName. I placed this activity immediately after my MSBuild activity to deploy. Is this somehting you have faced or am I just missing somehting? Thanks for your help

  7. Have you look at detailed logs? If the Delete workspace cannot delete the workspace, maybe some logs will clarify why it is not working.

    Anyway, why you want to delete the workspace after each build? It would be better for performance to leave the workspace after the build, so it could be used for the next one.

    alk.