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:
BlockOnPossibleDataLoss: defaulttrue. SqlPackage stops when a change could lose data (for example a column madeNOT NULLover existing rows, or a table whose definition changed incompatibly). Keep it on. Turn it off only for a target you are about to refill.DropObjectsNotInSource: leave itfalseon shared databases. Withtrue, everything not in the DACPAC disappears, including other schemas, maintenance tables or replication objects.ExcludeObjectTypesandIgnorePermissions: keep users, roles and permissions out of schema deployments if another process manages access.ScriptDatabaseOptions=false: do not try to alter database-level settings, which Azure SQL often restricts.
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
- A DACPAC with data and a table definition change: dropping the old content before publishing may be needed; a data-loss block is then expected, not a bug.
- Transient Azure SQL errors (timeouts, throttling) deserve a bounded retry; login failures and permission errors do not.
- Verify after publishing: row counts or a version table prove that what you deployed is what you expected.
- Never run two deployments on the same target at once.