Automate Database Builds Part Three: Create A Database Publish Profile

Automate Database Builds Part One: Extract DacPacs Using PowerShell Via sqlpackage.exe 
Automate Database Builds Part Two: Extract Database Structure for Visual Studio Solution
Automate Database Build Part Three: Create a Database Publish Profile

Introduction

Continuing with my automated database deployment series, this post touches on the build.publish.xml file that is used to script and publish a SQL Server 2012 project in Visual Studio. This step is necessary as we will need this file in our automated build.

Within a build.publish.xml file there is a “profile”. These profiles are essentially an xml file that define the settings when deploying a database. The examples below show how to create one, what the xml looks like, and some of the settings you can configure through the advanced settings. 

Walkthrough

Open up you database solution, right click the database project you want to publish and click “Publish”

createProfile1

Let’s go ahead and save the file before we edit it. The pictures below show that when we click on create profile and it will automatically save it to the solution. You don’t have to create the profile in order to use it, so if there are any ad-hoc publishing you want to do, or save more than one profile.

create Profile2

createProfile3

Now we edit the target database connection. Fill in whatever is appropriate for your database.

CREATEpROFILE4

Click ok to take you back to the Publish Database window. At this point you can either script or publish. Click publish and any changes in the solution will be applied to the target database.

create Profile5a

Even though we did not click “script” a script is still generated. You can click on the “view script” and you will see the sql script hat was deployed to your solution.

create Profile6

But what of the publish file? To see what the xml looks like right click the file in Solution Explorer and select “open with” and choose the “XML Text Editor”

create Profile7


<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="4.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
 <PropertyGroup>
 <IncludeCompositeObjects>True</IncludeCompositeObjects>
 <TargetDatabaseName>AdventureWorks2012DW</TargetDatabaseName>
 <DeployScriptFileName>AdventureWorks2012DW.sql</DeployScriptFileName>
 <ProfileVersionNumber>1</ProfileVersionNumber>
 <TargetConnectionString>Data Source=ALTEREDGIRATINA;Integrated Security=True;Pooling=False</TargetConnectionString>
 </PropertyGroup>
</Project>

So pretty straightforward, it’s an MSBuild file! it contains the target database and target instance, and we can see that it simply deploys the script. But all this is pretty straightforward, and database deployment is anything but. If we open up our publish file to get the default view and click on “Advanced” we see that we can get all sorts of options:

create Profile8

One setting I like to enable is dropping permissions that are not in the source. Once I have added that and saved the profile, when I open it up in XML Editor again the changes I made will be stored.


<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="4.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
 <PropertyGroup>
 <IncludeCompositeObjects>True</IncludeCompositeObjects>
 <TargetDatabaseName>AdventureWorks2012DW</TargetDatabaseName>
 <DeployScriptFileName>AdventureWorks2012DW.sql</DeployScriptFileName>
 <ProfileVersionNumber>1</ProfileVersionNumber>
 <TargetConnectionString>Data Source=ALTEREDGIRATINA;Integrated Security=True;Pooling=False</TargetConnectionString>
 <ScriptDatabaseOptions>True</ScriptDatabaseOptions>
 <BlockOnPossibleDataLoss>True</BlockOnPossibleDataLoss>
 <DropPermissionsNotInSource>True</DropPermissionsNotInSource>
 </PropertyGroup>
</Project>

Include Composite Objects in build.publish.xml

You can combine multiple database projects or dacpacs to create a single composite database schema. Using a composite database project enables reuse of database object definitions across different databases.

Composite projects are created by adding Database References to projects or dacpacs.  There are a few conditions by using references:

  • they must be strictly hierarchical
  • they must not contain circular dependencies.

w/r/t to using dacpacs as references, you can either build from a project or extract from a database. The hierarchy of references can be multi-layered, although you need to add references in each project to all its lower-level projects or dacpacs, whether directly or indirectly created.

To deploy a composite project you must set the Include composite objects option on the project you’re deploying from. This option is on the Advanced options in Publish and on the Debug properties tab. It is also available as a general option in Schema Compare. Note that in Schema Compare the option only affects publishing to a target database. This is important: if you do not set this option source project will be deployed without the referenced projects or dacpacs.  If you deploy to an existing database and forget to set this option but have set options to delete objects in the target that are not in the source, you will drop objects from the database.

If you save the build.publish.xml file, it sets the key value pair to “true”. By default it is implicitly set to false (that is, it won’t be in the xml set to “false”.


<IncludeCompositeObjects>True</IncludeCompositeObjects>

Summary

Publish files for database solution are essentially MSBuild files that use key value pairs declared at the project group level to define the settings for database deployment. It uses the script generated to deploy the solution. We create more than one publish file per database project for either different instances or different advanced settings, or we can use them without saving the profile through Visual Studio. If we wish to deploy via an automated build it makes sense to create a build.publish.xml file with the settings we want.

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Pokémon Nut. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

8 thoughts on “Automate Database Builds Part Three: Create A Database Publish Profile”

    1. Hi Ori. Interesting thought, I don’t think there is. I suppose you could create a file of type publish.xml from PowerShell and create/populate the values yourself?

      You have piqued my interest though, what scenario would you imagine using this?

      1. I think the proper way to do the automation is to create the publish profile (from scratch) automatically after the build (some changes may occur).
        Changes to publish profile are taking place in the deployment process.
        I change the publish profile, using XmlFile class in MSBuild Extension Pack (not Powershell) because I’m using MSBuild as my deployment tool.

      2. Certainly that is one way of doing it. But how I’ve been doing it is to use the same publish.xml file in all environments: local, dev, test, preprod. Yes and even prod. The reason for this is so that you can build once/deploy many. Ideally you’d build the database solution, package the dacpac and publish.xml file into a NuGet file and then deploy that to whatever environment you require. This also means you’d need to tokenize the publish.xml file for logins/usernames/passwords etc.

      3. If you add a variable to your project, you need to recreate the publish profile from scratch using SSDT publish GUI.
        If you forget to do it you might have problems, this is one of the reasons we are aiming towards full automation.
        I will be glad if you can find the way to create the publish profile from the command line.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s