GDR - deploying multiple database targets with one project

Question

I'm using Visual Studio Team System 2008 with GDR Version 9.1.40413.00 and am trying to resolve the issue of deploying a single DB project to multiple databases. For instance if I have a Performance.DB project I would like to deploy databases called Performance, Performance2, Performance3 etc.

However it seems if you go to the properties of the database project you can only have 1 deploy target. Is there a way round this? I would also like each Performance db to have its own sqlcmdvars file so it'll have its own unique mdf and ldf files.

If this is not possible to do from the properties within the project would there then be a way of creating a batch script that will execute for example the following command multiple times for each database I want to deploy - when I deploy the project?

"D:\Program Files\Microsoft Visual Studio 9.0\VSTSDB\Deploy\vsdbcmd.exe" /ManifestFile:"D:\Performance\Test\Binaries\Release\Performance.DB.deploymanifest" /a:Deploy /cs:"Data Source=.\SQLEXPRESS;Integrated Security=True;Pooling=False" /p:GenerateDropsIfNotInProject=True /p:BlockIncrementalDeploymentIfDataLoss=False /p:TargetDatabase=Performance /p:SqlCommandVariablesFile="D:\Performance\Test\Binaries\Release\Performance.sqlcmdvars" /p:IgnorePermissions=True /p:GenerateDeployStateChecks=False /dd:+

score 0 · Answer 1 · answered Apr 17 '12 at 18:58

I recently ran into a similar situation, wanting one database project with a set of "base" objects that get deployed into multiple "child" databases. To solve this issue, our team went with composite projects (http://msdn.microsoft.com/en-us/library/dd193415.aspx) to define our base and child databases, and then set up multiple build configurations. Each build configuration allows you to specify a different target, sqlcmdvars, etc. and basically tailor the build to deploy to only one database at a time.

This means that to deploy all of your Performance databases, you'll need to change build configurations for each one, or if deploying/unit testing in TFS, create multiple test settings files with different .config files for each build configuration you have.

score 0 · Answer 2 · answered Apr 19 '12 at 12:48

With some MSBUILD hacking in the *.dbproj file I came up with a project that just calls itself a second/n times in the AfterDeploy event, so you can use a single 'Deploy' command to deploy to multiple databases:

 <PropertyGroup>
    <TargetDatabase>Performance</TargetDatabase>
    <TargetDatabase Condition="$(SecondDatabase)!=''">$(SecondDatabase)</TargetDatabase>
  </PropertyGroup>


 <Target Name="AfterDeploy">
    <MSBuild Condition="$(SecondDatabase)==''" Targets="Deploy" Properties="SecondDatabase=Performance2" Projects="$(ProjectPath)" />
    <MSBuild Condition="$(SecondDatabase)==''" Targets="Deploy" Properties="SecondDatabase=Performance3" Projects="$(ProjectPath)" />
  </Target>

score 0 · Answer 3 · answered Oct 11 '09 at 01:04

You could try and look at partial database projects.

Here is an example http://blogs.msdn.com/gertd/archive/2008/10/05/partial-projects.aspx.

They are useful for sharing code from one database project to another, although it may not be so great on a full project scale.

Using this your core Perfomance database project would be the "base" project and all others would derive from this.

GDR - deploying multiple database targets with one project

3 Answers3