Sql Project Publish replaces Deploy - how to supress versioning and hook into tfs build

Question

We're using the Sql Server 2012 SSDT which removed the deploy option in Visual Studio for the database projects (now sql projects). We'd like to automate the Publish step as we had for deploy, but it's not clear how to do this. so thA couple of questions:

1. I've added the .publish.xml to the project (after the first manual publish, checking add to project). Even after that, and setting it to the default, when I double click it, it builds, but always pops up settings window, where I need to click the "Publish" button to continue. Is there a setting that would skip this prompt and use the current values?

2. It seems that each publish generates a version of the sql output. How can I suppress this- i.e. overwrite the base file each time?

3. And lastly, any pointers for updating the build to use the new project type and publish command for the automated builds would be appreciated.

Answer 1

The best way I have found to automate the deployment of SSDT database projects is to use msbuild. Originally we were using VSTSDB and used msbuild against the *.dbproj file. As it turned out the arguments for deploying sqlproj files is exactly the same.

Because the old argument list works for us, I didnt swap to using the public.xml file style. There quite a bit of documentation for the vsdbcmd.exe and msbuild against dbproj. I would use that as reference.

Here's argument list, and execution output as we define it for FinalBuilder execution

[ MSBuild Project [ C:\xx\xxx\xx\xx\MyProject.sqlproj ] ]
  Configuration : Release
  OutDir : C:\Builds\1\xxxxx\builddefname\Binaries\Release\
  DeployToDatabase : True
  TargetDatabase : ExistingDatabaseName
  TargetConnectionString : Data source=.;Integrated Security=SSPI;**
  Build started 3/23/2012 2:17:08 PM.
  Deployment script generated to:
  C:\Builds\1\xxxx\builddefname_FB\Binaries\Release\MyProject.sql
  Dropping FK_at_lusys_assetCategory_at_lusys_image...
  Creating FK_dcb28374eeabe8e715038984419...
  Creating FK_d82897e4acd966d4b136c242cef...
  Checking existing data against newly created constraints
  Update complete.
  Done Building Project "C:\xxx\xxxxxxx\xxxxxxxxx\MyProject.sqlproj" (Deploy target(s)).
  Build succeeded.
  0 Warning(s)
  0 Error(s)

and putting together the msbuild command line looks like this:

msbuild XXX.sqlproj /target:Deploy /p:Configuration=xxx;OutDir=xxx;DeployToDatabase=True;TargetDatabase=xxxx;TargetConnectionString="xxxxx";AlwaysCreateNewDatabase=True

Answer 2

How to restore the Deploy option: (Visual Studio 2010/2012 only -- this is no longer supported in Visual Studio 2013)

The Deploy option is still present but for some reason it's not available in the menus. (Cursed Visual Studio team!) I've worked around this by adding the Deploy option to one of the toolbars as follows:

1. Click the arrow on the right-hand side of a toolbar.
2. Click "Add or Remove Buttons", then Customize.
3. In the Customize dialog click Add Command.
4. Select the "Build" category, then select the "Deploy Selection" command.
5. After saving your selection the "Deploy [project name]" option will appear on the toolbar. You'll need to select your project in Solution Explorer for the button to become enabled.

Note that the deployment settings are different than the publish settings. The deployment settings are configured in the project's properties on the Debug tab.

---

To answer your questions about the Publish option:

1) How to use a specific publish file by default and avoid the annoying prompt

I don't think there's a way around this.

2) How to publish the entire database, not just the changes

Open your .publish.xml file in a text editor and add <AlwaysCreateNewDatabase>true</AlwaysCreateNewDatabase>.

For example:

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="4.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <TargetDatabaseName>MyDatabase</TargetDatabaseName>
    <DeployScriptFileName>MyDatabaseProject.sql</DeployScriptFileName>
    <TargetConnectionString>Data Source=localhost\SQL2012;Integrated Security=True;Pooling=False</TargetConnectionString>
    <PublishDependentProjects>False</PublishDependentProjects>
    <ProfileVersionNumber>1</ProfileVersionNumber>
    <AlwaysCreateNewDatabase>true</AlwaysCreateNewDatabase>
  </PropertyGroup>
</Project>

3) Command-line syntax for automated builds

First build your project with msbuild as you normally would so that the .dacpac file is created in the bin.

Then use sqlpackage.exe to publish using your .publish.xml file:

C:\Program Files\Microsoft Visual Studio 10.0\Microsoft SQL Server Data Tools\sqlpackage.exe /Action:Publish /SourceFile:C:\[path to my project]\bin\Debug\MyDatabaseProject.dacpac /Profile:C:\[path to my project]\MyDatabaseProject.publish.xml

Note that the path to sqlpackage.exe may be different.

Answer 3

A bit late to the party, I admit, but maybe this will help others who stumble across this discussion. My company is presently moving to VS2012 and we have all three of the same issues as Keith. I have found workarounds for #1 and #2.

For #1, I use AutoHotKey to monitor for the existence of the publish window, and automatically click the "Create Script" button. You could of course have the script automatically click the "Publish" button instead. In this example if the publish profile is not "XYZ" (I always prefer manual intervention for production server deployments) then go ahead and send an Alt+G to generate the script.

#Persistent
SetTimer, ClosePopups, 5000

return

ClosePopups:
if WinExist("Publish Database ")
{
    WinActivate, Publish Database
    WinGetTitle, TitleText, A
    If not TitleText = "Publish Database XYZ.publish.xml" {
        Send, !G
    }
}
return 

For #2, every time we publish it increments the filename with a number suffix and we end up with lots of files in our deployment folder. I just used pre-build events to clear out the .sql and .txt files before the build:

if exist "$(ProjectDir)$(OutputPath)*.publish.sql" del $(ProjectDir)$(OutputPath)*.publish.sql
if exist "$(ProjectDir)$(OutputPath)*.txt" del $(ProjectDir)$(OutputPath)*.txt