DAC Publish Profile

DAC Publish Profiles are easy to create using Visual Studio. In fact, you have probably already created one without even realising.

To create a DAC Publish Profile you simply Publish from within Visual Studio. Clicking the Save Profile button in the Publish screen saves your DAC Publish Profile into your Visual Studio project for later re-use.

Below is our step-by-step to creating your first DAC Publish Profile. However, for those familiar with the process, you need two DAC Publish Profiles, one for your CI pipeline and the other for upgrade deployments to your production databases. These are two very different requirements:

DAC Publish Profile for your CI Pipeline

In the CI scenario, you simply want to drop and re-create the database as you really do not want remnants of your old deployment interfering with your new build, deploy and automated test cycle. Our recommended settings for you CI pipeline are:

• CreateNewDatabase = True
  • Always drop and re-create the database in CI senarios
• BlockOnPossibleDataLoss = False
  • Clearly irrelevant in the CI senario
• ScriptFileSize = False
  • If your SSDT project specifies the size of the file groups to be used on you production server, then your CI server may not have the same disk array settings or space available. Nor do you want to wait for SQL Server to create the extents on the file system. Using this option, you can ensure that the file size is not scripted. This is well explained in Scott's blog post

Here is an example DAC Publish Profile for the CI pipeline

DAC Publish Profile for Upgrades to Production Databases

When upgrading production databases, you really need to take advantage of the fine grain control that DAC Publish Profiles provide.

• CreateNewDatabase = False
  • We certainly do not want to drop the database!
• IncludeCompositeObjects = True
  • Specifies whether to include referenced, external elements that also compose the source model and then update the target database in a single deployment operation
• GenerateSmartDefaults = True
  • When you add a non-nullable column to a table, this will ensure the deployment does not fail by doing the process in three steps: first adding a nullable column, then populating the column with default values (zeros for numeric fields, '' for strings) and then changing the column constraints to NOT NULL. Your post-deploy script should then populate the column with real values.
• BlockOnPossibleDataLoss = False
  • Specifies whether deployment should stop if the operation could cause data loss. However, you should be testing your upgrade during your build/deploy pipeline, checking that no data loss occured. Setting this option to false ensures that your pipeline does not fail for irrelevant reasons.
• DropObjectsNotInSource = True
  • Basically this option deletes any object in the database that are not defined in your SSDT solution. There should be NO drift between production and your source code repository. But if you do have developers or DBAs who constantly hack the production database to add thier own undocumented stored procs and routines, then this option will make their efforts in vain and hopefully get them to follow due process using source code control and DevOps. However, you may not want to drop user permissions, so the following overrides are recommended:
    • DropExtendedPropertiesNotInSource = False
    • DoNotDropExtendedProperties = True
    • DoNotDropLogins = True
    • DoNotDropUsers = True
    • DoNotDropRoleMembership = True
    • DoNotDropApplicationRoles = True
    • DoNotDropDatabaseRoles = True
    • DoNotDropPermissions = True
• ScriptFileSize = True
  • If your SSDT project specifies the size of the file groups to be used on you production server, then this will ensure the file size is specified when adding files to file groups.

There are hundreds of other DAC Deploy Options

Here is an example DAC Publish Profile for the database upgrade

Step-by-step guide to creating your first DAC Publish Profile

To create a DAC Publish Profile you simply Publish from within Visual Studio.

This will display the following dialog where you can change the database deployment settings. First edit the server connection string by clicking Edit (circled and marked 1. below). Your database will doubtless need to have one or more SQLCMD variables set for deployment purposes. Either type in the desired values for the CI server, or simply click Load Values (circled and marked 2. below). Note that both PublishDacPac PowerShell module and the Publish DACPAC using a DAC Publish Profile Azure Pipeline extension allow you to override these SQLCMD variables.

In the CI scenario, we always want to create a new database, so click the Advanced option button (circled and marked 1. below) which will reveal the advanced options dialog. Tick the Always re-create database option.

Next save the result by clicking Create Profile button (circled and marked 2. below). This saves the DAC Publish Profile as an XML file in your Visual Studio solution.

In Visual Studio Solution Explorer, highlight the newly created XML file and rename it to something more sensible, such as YourDatabase.CI.publish.xml as shown below:

Create an upgrade DAC Publish Profile

To create a second DAC Publish Profile, you have three options:

1. Go through the process outlined above
2. Double click an existing DAC Publish Profile and clich the Save Profile as button.
3. In Visual Studio Solution Explorer, copy and paste an existing DAC Publish Profile

In all cases, rename the new file YourDatabase.Upgrade.publish.xml or something similar.

You can either use the Advanced options dialog to select all the options described above, or simply open the DAC Publish Profile as an XML file and paste the options from below. To view the DAC Publish Profile in XML format, right click the file and select Open.

Here are the options described above so you can copy and paste them into your DAC Publish Profile.

    <CreateNewDatabase>False</CreateNewDatabase>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <GenerateSmartDefaults>True</GenerateSmartDefaults>
    <BlockOnPossibleDataLoss>False</BlockOnPossibleDataLoss>
    <DropObjectsNotInSource>True</DropObjectsNotInSource>
    <DropExtendedPropertiesNotInSource>False</DropExtendedPropertiesNotInSource>
    <DoNotDropExtendedProperties>True</DoNotDropExtendedProperties>
    <DoNotDropLogins>True</DoNotDropLogins>
    <DoNotDropUsers>True</DoNotDropUsers>
    <DoNotDropRoleMembership>True</DoNotDropRoleMembership>
    <DoNotDropApplicationRoles>True</DoNotDropApplicationRoles>
    <DoNotDropDatabaseRoles>True</DoNotDropDatabaseRoles>
    <DoNotDropPermissions>True</DoNotDropPermissions>
    <ScriptFileSize>True</ScriptFileSize>