Deploying a Database Project with MSDeploy

Last year I blogged about how we use MSDeploy in our development group at EBSCO to deploy not just web applications but also Windows services, scheduled tasks and SQL databases.  

EXTENDING MSDEPLOY BEYOND THE WEB

As a followup to that post I will be blogging a series of posts (grouped with the tag “msdeployallthethings”) that will dig into those capabilities more deeply. 

Today we will use MSDeploy to deploy a Database Project.  MSDeploy offers several database-related providers, including:

• dbFullSql – SQL Server database deployments
• dbMySql – MySQL database deployments
• dbSqlite – Sqlite database deployments
• dbDacFx – DACPAC-based SQL database deployments

We use dbDacFx primarily because of the benefits DACPACs provide.

Environment Setup

I am using a Database project in Visual Studio 2015 for this example.  (FYI we have used this same pattern all the way back to VS2010 with some slight tweaks).  The Database project is available after installing the SQL Server Data Tools extension.

Also we are using the latest version of MSDeploy which is provided via WebDeploy 3.6.  

I will assume you have a working knowledge of MSBuild.  If you need a refresher, this is a good tutorial.

Step 1: Extending MSBuild

We will use MSBuild to create an environment-agnostic deployment package/command.  The easiest way to extend MSBuild is to simply add your MSBuild script to your project file. Unfortunately, this is not very visible and can often cause confusion for other developers.

WebDeploy provides a very nice visible hook for extending MSBuild by adding a [ProjectName].wpp.targets file.  WPP is an acronym for “Web Publishing Pipeline” which is what WebDeploy has named their MSBuild extensions.

We can mimic this functionality by adding an import statement to our project file which points to a [ProjectName].spp.targets.  In our case we will use SPP which will stand for “SQL Publishing Pipeline”.

  <Import Project=&quot;$(MSBuildProjectName).spp.targets&quot; />

Then we will create the [ProjectName].spp.targets file in the root project folder and add it to source control.  This file extends the MSBuild script that will be evaluated as part of the build. 

<?xml version=&quot;1.0&quot; ?>
<Project xmlns=&quot;http://schemas.microsoft.com/developer/msbuild/2003&quot;>
  
</Project>

Now any additions we make to MSBuild will be much more visible and easier to change in the future.

Step 2: Creating a MSDeploy package

WebDeploy provides a MSBuild task for MSDeploy that we will use to create a package from our DACPAC file.  The task is just a wrapper around the MSDeploy.exe command.  It doesn’t include support for all the options of the exe but it covers the vast majority.  To use the task we need to define the task based on the Microsoft.Web.Publishing.Tasks assembly in our targets file.

  <UsingTask TaskName=&quot;MSDeploy&quot; AssemblyFile=&quot;$(MSBuildExtensionsPath)MicrosoftVisualStudiov14.0WebMicrosoft.Web.Publishing.Tasks.dll&quot;/>  

We will create the package after the build is complete.  We can instruct MSBuild to execute our additions by defining a new Target named “AfterBuild”.

  <Target Name=&quot;AfterBuild&quot;>
    
  </Target>

Within this target we will setup some MSBuild properties and call the MSDeploy task with the appropriate attributes to create an MSDeploy package.

    <PropertyGroup>
      <FullOutputPath>$(MSBuildProjectDirectory)$(OutputPath)</FullOutputPath>
      <PackageFilename>$(MSBuildProjectName).zip</PackageFilename>
      <PackageFullPath>$(FullOutputPath)$(PackageFilename)</PackageFullPath>
      <MSDeployPath Condition=&quot;&#039;$(MSDeployPath)&#039;==&#039;&#039;&quot;>C:Program FilesIISMicrosoft Web Deploy V3msdeploy.exe</MSDeployPath>
      <DeployCmdFilename>$(MSBuildProjectName).SQL.deploy.cmd</DeployCmdFilename>
      <DeployCmdFile>$(FullOutputPath)$(DeployCmdFilename)</DeployCmdFile>
    </PropertyGroup>
    <MSdeploy
          Verb=&quot;sync&quot;
          Source=&quot;dbDacFx=&amp;quot;$(FullOutputPath)$(MSBuildProjectName).dacpac&amp;quot;&quot;
          Destination=&quot;package=&amp;quot;$(PackageFullPath)&amp;quot;&quot;
          ExePath=&quot;$(_MSDeployDirPath_FullPath)&quot;
          ImportDeclareParametersItems=&quot;$(FullOutputPath)parameters.xml&quot;
    />

We are using the SYNC verb to create a MSDeploy package which contains the DACPAC.  You can use MSDeploy to sync a DACPAC directly to a SQL database but Parameterization is not supported in that use case.  We need to be able to change the target SQL server at deploy time (using Parameterization)so the package is environment agnostic.

Notice too, we have imported the declareParam file in the MSDeploy task.  We will create that file in step 4.

Step 3: Creating a deploy command

Once the project is built with the changes above you could use the MSDeploy.exe to deploy the resulting package in the bin/debug folder manually with the following command:

"C:Program FilesIISMicrosoft Web Deploy V3msdeploy.exe" -verb:sync -source:package=DatabaseProjectMSDeployPackage.zip -dest:dbDacFx=[TargetDatabaseConnectionString]

Obviously, you would need to input your target connection string.

This would work but its not a great user experience and would present some problems if you want to use your package in an automated fashion (perhaps in a Continuous Delivery process).

The better option to create a .cmd file that can make this call for the user along with the appropriate environment-specific Parameterization file given the environment name.

<ItemGroup>
      <DeployCMDLines Include=&quot;@ECHO OFF&quot; />
      <DeployCMDLines Include=&quot;IF %22%1%22==%22%22 GOTO Readme&quot; />
      
      <DeployCMDLines Include=&quot;:Deploy&quot; />
      <DeployCMDLines Include=&quot;ECHO Starting deployment ...&quot; />
      <DeployCMDLines Include=&quot;@ECHO ON&quot; />
      <DeployCMDLines Include=&quot;%22$(MSDeployPath)%22 -verb:sync -source:package=$(PackageFilename) -dest:dbDacFx=TargetDatabase -verbose -setParamFile:SetParameters.%1.xml %~2 %~3 %~4 %~5 %~6 %~7 %~8 %~9&quot; />
      <DeployCMDLines Include=&quot;@ECHO OFF&quot; />
      <DeployCMDLines Include=&quot;GOTO End&quot; />
      
      <DeployCMDLines Include=&quot;:Readme&quot; />
      <DeployCMDLines Include=&quot;ECHO.&quot; />
      <DeployCMDLines Include=&quot;ECHO Deploys database to target server using MSDeploy&quot; />
      <DeployCMDLines Include=&quot;ECHO.&quot; />
      <DeployCMDLines Include=&quot;ECHO $(DeployCmdFilename) ENVIRONMENT &#91;Other MSDeploy parameters (up to 8)&#93;&quot; />
      <DeployCMDLines Include=&quot;ECHO.&quot; />
      <DeployCMDLines Include=&quot;ECHO   ENVIRONMENT%09%09The environment to use for the SetParameters.&#91;environment&#93;.xml file.&quot; />
      <DeployCMDLines Include=&quot;ECHO   MSDeploy params%09You may optionally add any other MSDeploy parameters to the call.  For example:&quot; />
      <DeployCMDLines Include=&quot;ECHO     -whatif&quot; />
      <DeployCMDLines Include=&quot;ECHO     -retryAttempts&quot; />
      <DeployCMDLines Include=&quot;ECHO     -verbose&quot; />
      <DeployCMDLines Include=&quot;ECHO.&quot; />
      <DeployCMDLines Include=&quot;GOTO End&quot; />
      
      <DeployCMDLines Include=&quot;:End&quot; />
    </ItemGroup>
    <WriteLinesToFile
      File=&quot;$(DeployCmdFile)&quot;
      Lines=&quot;@(DeployCMDLines)&quot;
      Overwrite=&quot;true&quot; /> 

This probably looks a little daunting but its really pretty simple.  We are defining the lines for the cmd file and then writing them to the cmd file.  Within the cmd lines we first check to make sure the user provided the environment name, if not we print out some instructions for them.

The command takes the specified environment runs MSDeploy to deploy the package to a “TargetDatabase” (this is a placeholder) and a reference to the SetParameters.[environment].xml file.

"C:Program FilesIISMicrosoft Web Deploy V3msdeploy.exe" -verb:sync -source:package=DatabaseProjectMSDeployPackage.zip -dest:dbDacFx=TargetDatabase -verbose -setParamFile:SetParameters.local.xml

Step 4: Parameterize your target database

[For an overview of Parameterization see my ealier post.]

In this example there is only one parameter that needs to be defined and thats for our target database connection string.  In the previous step we add a placeholder of “TargetDatabase”.  We will only use this to find the appropriate place to inject our real connection string.

<?xml version=&quot;1.0&quot; encoding=&quot;utf-8&quot; ?>
<parameters>
  <parameter name=&quot;ConnectionString-Database&quot;
     description=&quot;Connection string to the database.&quot;
     defaultValue=&quot;Data Source=(local)sqlexpress2014;Initial Catalog=LocalDatabase;Integrated Security=True&quot;>
    <parameterEntry
       type=&quot;ProviderPath&quot;
       scope=&quot;dbFullSql|dbSqlPackage|dbDacFx&quot;
       match=&quot;TargetDatabase&quot;/>
  </parameter>
</parameters>

Here you can see that our parameterEntry is defined with a Type of ProviderPath, a Scope of dbDacFx (and others) and match value of “TargetDatabase”.  The most common entry type in Parameterization is the XmlFile type for parameterizing config files.  ProviderPath is less widely known of but is specifically helpful in altering the deployment attributes of MSDeploy.   This entry is looking for our dbDacFx provider with the path of “TargetDatabase”.  It will find the destination in our MSDeploy call and update the target connection string with the value we have in the SetParameters file.

Verbose: Parameter entry 'ConnectionString-Database/1' is applicable to 'dbDacFx
/TargetDatabase' because of its scope.
Info: Adding MSDeploy.dbDacFx (MSDeploy.dbDacFx).
Info: Adding database (data source=(local)sqlexpress2014;initial catalog=LocalD
atabase;integrated security=True)

Lastly we need to create a SetParameters file for each of the environments we plan to target with the appropriate connection string.

<?xml version=&quot;1.0&quot; encoding=&quot;utf-8&quot; ?>
<parameters>
  <setParameter
      name=&quot;ConnectionString-Database&quot;
      value=&quot;Data Source=(local)sqlexpress2014;Initial Catalog=ReleaseDatabase;Integrated Security=True&quot; />
</parameters>

Don’t forget you will need to change the properties on these files to copy them to the output folder.

The Source

A working example of this solution is on GitHub if you want to try it yourself. 

https://github.com/rschiefer/DatabaseProjectMSDeployPackage

You should be able to clone the repo and build successfully without any changes.  If you want to try the .SQL.deploy.cmd command you will likely need to update the database connections strings in the Parameterization files to target your SQL server instance.

Feedback

Please let me know if you found this post helpful in the comments or on Twitter.  Also, leave feedback if you have a suggestion for a furture msdeployallthethings post.