LLI Wiki — My Digital Knowledge Base

Deploying schema changes with DACPAC and SqlPackage to many databases

Extract, publish, publish profiles, blocking on data loss, drift reports, parallel deployment per pool and pipeline integration.

When the same schema has to exist in dozens or thousands of databases, hand-written migration scripts do not scale. A DACPAC describes the desired state of a schema; SqlPackage compares it with a target and computes the changes. I use this to deploy reference schemas and to build a repeatable release process for many Azure SQL databases.

The pieces

Action What it does
Extract reads a database and writes a .dacpac (schema, optionally data with /p:ExtractAllTableData=true)
Publish applies a .dacpac to a target: creates or alters objects to match
Script same comparison, but writes the T-SQL instead of running it
DeployReport writes an XML report of what a publish would do
DriftReport compares a registered database with the schema it was deployed from

A DACPAC is a zip file. It carries the model, not a backup: do not confuse it with a BACPAC, which is for schema plus data export and import.

Extract and publish

# Extract, with data (small reference schemas)
sqlpackage /Action:Extract /SourceConnectionString:"$srcCs" `
    /TargetFile:"refer.dacpac" /p:ExtractAllTableData=true

# Publish with a profile
sqlpackage /Action:Publish /SourceFile:"refer.dacpac" `
    /TargetServerName:"sql-contoso-prd.database.windows.net" /TargetDatabaseName:"AppDb" `
    /AccessToken:"$token" /Profile:"prd.publish.xml"

For Azure SQL I pass a token (/AccessToken) obtained from the managed identity, so no password exists in the pipeline.

Publish profiles

A profile is an XML file holding the options, so every run uses identical settings and the file is reviewed in git like code.

<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <BlockOnPossibleDataLoss>True</BlockOnPossibleDataLoss>
    <DropObjectsNotInSource>False</DropObjectsNotInSource>
    <IgnorePermissions>True</IgnorePermissions>
    <ScriptDatabaseOptions>False</ScriptDatabaseOptions>
    <ExcludeObjectTypes>Users;RoleMembership;Permissions</ExcludeObjectTypes>
  </PropertyGroup>
</Project>

The options that matter most:

Look before you publish

Always generate the report first, and read it in a review step:

sqlpackage /Action:DeployReport /SourceFile:"app.dacpac" `
    /TargetConnectionString:"$cs" /OutputPath:"report.xml" /Profile:"prd.publish.xml"

The XML lists every operation and any alert (data loss, for instance). Your pipeline can publish it as an artifact and require approval before the real publish. Script gives the generated T-SQL for the same purpose.

Drift

Databases change outside the pipeline: a quick index added by hand, a hotfix. To detect it, publish with /p:RegisterDataTierApplication=true so the target remembers its source schema, then run DriftReport:

sqlpackage /Action:DriftReport /TargetConnectionString:"$cs" /OutputPath:"drift.xml"

Run it on a schedule and mail the difference. It is a cheap way to find out that production no longer matches what git says.

Deploying to many databases

For a fleet, the shape is: a list of targets (from an inventory table), grouped by elastic pool, and a parallel loop.

$byPool = $targets | Group-Object PoolName
$byPool | ForEach-Object -Parallel {
    $_.Group | ForEach-Object -Parallel {
        # one publish per database
    } -ThrottleLimit 2
} -ThrottleLimit 8

Databases in one pool share CPU and IO, so I limit concurrency inside a pool (two at a time) and parallelise across pools. A failure in one database must not stop the others: count it as failed, continue, and exit non-zero at the end. Record the deployed version per database in the inventory, and select only databases not yet at that version, so rerunning the same command resumes the work.

Pipeline integration

A typical pipeline: build the DACPAC from the SQL project, run DeployReport against a test target, publish to non-production, wait for approval, then run the fleet deployment at night for production. SqlPackage is installed on the agent, or comes as a .NET tool. Keep the DACPAC as the single build artifact promoted unchanged from one environment to the next; rebuilding per environment defeats the point.

Pitfalls

Mastodon LLIMastodon SocialFosstodon