CODEWITHSUNDEEP
visual-studio-2013sql-server-data-tools
David Wilson
David Wilson

Reputation: 177

Deploy multiple SSDT database projects from Visual Studio 2013

I have a solution with multiple (6) database projects that were converted from Visual Studio Database (.dbproj) projects to SQL Server Data Tools (.sqlproj) projects. In Visual Studio 2013, I can successfully build and publish each of the databases to my local SQL Server instance one at a time.

In Visual Studio 2010, I was able to deploy the solution, which in turn built and deployed all the databases. In Visual Studio 2013, deploying the solution does nothing, even though all of the projects are set to both build and deploy for the active configuration.

How can I build and deploy all of the databases from Visual Studio 2013?

Upvotes: 2

Views: 2627

Answers (2)

JGooLaaR
JGooLaaR

Reputation: 94

I understand it is may be late, but...

We using this way:

  1. Tune you database references in existed sqlproj as you need.

  2. Just make another one project(kind of "lalala_Deploy") and add all existed as references with Database location == "Same database".

  3. Deploy "Deploy" project.

As a result your projects will be builded corresponding their database references and then deployed to single DB.

upd 28.03.2017 17:37: In 2013 we was use cdm with msbuild command line and publish profiles. Here is these codes:

CMD example:

%WINDIR%\Microsoft.NET\Framework64\v4.0.30319\MSBuild.exe .\ProjectName\ProjectName.sqlproj /t:Build;Publish /p:Configuration=Release /p:SqlPublishProfilePath=ProjectName_local.publish.xml /consoleloggerparameters:Summary /verbosity:normal /l:FileLogger,Microsoft.Build.Engine;logfile=OmniUS_Build.log;append=false

Here is publish profile example:

"<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="12.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <IncludeCompositeObjects>False</IncludeCompositeObjects>
    <TargetDatabaseName>ProjectName</TargetDatabaseName>
    <DeployScriptFileName>ProjectName.publish.sql</DeployScriptFileName>
    <TargetConnectionString>Data Source=.\SQL2014;Integrated Security=True;Pooling=False</TargetConnectionString>
    <ScriptDatabaseOptions>True</ScriptDatabaseOptions>
    <BlockOnPossibleDataLoss>False</BlockOnPossibleDataLoss>
    <IncludeTransactionalScripts>False</IncludeTransactionalScripts>
    <CreateNewDatabase>True</CreateNewDatabase>
    <DropObjectsNotInSource>False</DropObjectsNotInSource>
    <AllowDropBlockingAssemblies>True</AllowDropBlockingAssemblies>
    <GenerateSmartDefaults>True</GenerateSmartDefaults>
    <IgnoreFillFactor>False</IgnoreFillFactor>
    <IgnoreFilegroupPlacement>False</IgnoreFilegroupPlacement>
    <IgnorePermissions>True</IgnorePermissions>
    <IgnoreObjectPlacementOnPartitionScheme>False</IgnoreObjectPlacementOnPartitionScheme>
    <IgnoreRoleMembership>True</IgnoreRoleMembership>
    <BlockWhenDriftDetected>False</BlockWhenDriftDetected>
    <RegisterDataTierApplication>False</RegisterDataTierApplication>
    <DisableAndReenableDdlTriggers>False</DisableAndReenableDdlTriggers>
    <IgnoreIndexPadding>False</IgnoreIndexPadding>
    <IgnoreKeywordCasing>True</IgnoreKeywordCasing>
  </PropertyGroup>
  <ItemGroup>
    <SqlCmdVariable Include="N_Period">
      <Value>201410</Value>
    </SqlCmdVariable>
  </ItemGroup>
</Project>"

In cdm example i left 1 string, but for you case you have to add for each you project. The publish profiles mast be separated for all of projects because they are containg DB name.

Upvotes: 0

Ebeid ElSayed
Ebeid ElSayed

Reputation: 1156

If these databases are related somehow and referenced as database references, they will be built and deployed together as one connected solution. Consider the following two scenarios:

Scenario 1: You have a solution that contain four databases [A, B, C, D]. [A] references [B, D]. [D] references [C]. If you added them as connected databases using Database references and tried to deploy A, all of them will be built and deployed.

Scenario 2: You have a solution that contain four databases [A, B, C, D]. [A] references [B, D]. If you added them as connected databases using Database references and tried to deploy A, only [A, B, D] will be built and deployed. [C] is an orphan project in the solution ( and actually it shouldn't be in that solution) and it will not be built or deployed by the solution.

Upvotes: 0

Related Questions