Database deployments should be repeatable. A SQL database project describes the desired schema, its build produces a .dacpac, and SqlPackage compares that model with a target database to work out the changes to apply.
But what happens when a pipeline publishes the same database model again? Even when nothing needs to change, determining that can still involve a full schema comparison.
The SqlPackage 170.6 preview introduces two complementary capabilities:
- Deployment history, enabled with
LogDeployment, records deployments in the target database. - Fast comparison, enabled with
EnableFastComparison, can use compatible deployment history to avoid a full schema comparison on a repeat deployment.
Let’s look at what these options do and try them out.
Preview scope: This walkthrough targets the NuGet package
Microsoft.SqlPackageversion170.6.40-preview. Its executable reports170.6.40.1. Preview behavior and metadata can change.
What does “versioning” mean here?
It is useful to distinguish deployment history from semantic-version enforcement.
These options provide a record of deployments and a way to recognize a previously deployed database model. They are not, by themselves, a migration ordering system, an automatic rollback mechanism, or a rule that prevents deployment of a lower application version.
With deployment logging enabled, project versions can also be recorded in deployment history and used in deployment-script conditions. SqlPackage does not automatically enforce version ordering.
Fast comparison is not simply a comparison of a friendly version string such as 1.0.0. Instead, SqlPackage compares the model checksum and deployment options with the last deployment record to determine whether it can skip the full schema comparison.
Two new options
| Publish property | Default | Purpose |
|---|---|---|
/p:LogDeployment=True |
False |
Logs deployment history to [sqlproj].[DeploymentHistory] in the target database. |
/p:EnableFastComparison=True |
False |
Enables fast comparison against the last deployment. Requires LogDeployment and an existing database with compatible deployment history. |
You can enable deployment history without enabling fast comparison. That is a useful starting point if you want deployment records while retaining normal schema comparison.
Do not combine DAC registration with deployment-history versioning. Leave
RegisterDataTierApplicationset toFalse(the default) when using this workflow. If the target database is already DAC-registered, unregister it before adopting deployment-history versioning; settingRegisterDataTierApplication=Falsedoes not remove an existing registration.
When you enable both new options:
- SqlPackage checks whether the existing database has compatible deployment history.
- If the model checksum and deployment options match the last deployment record, it skips the full schema comparison.
- Otherwise, it uses normal comparison.
A new database or a database without compatible history follows the normal comparison path as you would expect. However, an eligible subsequent publish can benefit from the shortcut.
Example: publishing the same database model twice
To see this in action, we’ll use an existing AdventureWorks2025 database on a local SQL Server instance. We’ll extract its schema to a .dacpac, then publish that model twice to a separate AdventureWorksPreview database.
This simulates a CI/CD pipeline retry: the same database model is deployed to the same environment again.
For this example, you need:
- Windows PowerShell.
- A compatible .NET SDK and runtime for installing and running SqlPackage.
- A running local SQL Server instance, reachable as
localhost, with theAdventureWorks2025sample database installed. - Full-Text Search installed on that SQL Server instance (AdventureWorks includes full-text indexes).
- Windows authentication with permission to read the source schema, create the target database, and deploy its schema and deployment-history objects.
AdventureWorks2025 is the read-only source for this walkthrough; we won’t publish to it or change its schema. AdventureWorksPreview is the deployment target.
The extraction is schema only. No AdventureWorks sample data is copied to the target.
1. Install the preview side by side
Install the preview into a separate tool directory rather than replacing a stable global installation:
dotnet tool install Microsoft.SqlPackage `
--tool-path .\tools\sqlpackage-preview `
--version 170.6.40-preview
if ($LASTEXITCODE -ne 0) {
throw "SqlPackage preview installation failed."
}
$sqlpackage = Join-Path $PWD "tools\sqlpackage-preview\sqlpackage.exe"
& $sqlpackage /Version
if ($LASTEXITCODE -ne 0) {
throw "Unable to run SqlPackage preview."
}
For the package used in this example, the version output is:
170.6.40.1
For more information about using the preview version see Download and Install SqlPackage – SQL Server | Microsoft Learn
2. Extract the AdventureWorks schema
Continue in the same PowerShell session:
$server = "localhost"
$sourceDatabase = "AdventureWorks2025"
$targetDatabase = "AdventureWorksPreview"
$artifactDirectory = Join-Path $PWD "artifacts"
$dacpac = Join-Path $artifactDirectory "AdventureWorks2025.dacpac"
New-Item -ItemType Directory -Path $artifactDirectory -Force | Out-Null
$sourceConnectionString = "Server=$server;Initial Catalog=$sourceDatabase;Integrated Security=True;Encrypt=True;TrustServerCertificate=True;"
& $sqlpackage `
/Action:Extract `
"/SourceConnectionString:$sourceConnectionString" `
"/TargetFile:$dacpac" `
/p:ExtractAllTableData=False `
/p:VerifyExtraction=True
if ($LASTEXITCODE -ne 0) {
throw "AdventureWorks schema extraction failed."
}
Update $server or $sourceDatabase as needed. The extraction produces .\artifacts\AdventureWorks2025.dacpac. With ExtractAllTableData=False and no individual tables selected for data extraction, it does not copy table data.
TrustServerCertificate=True is used only for this example. For a remote or production server, use a properly configured certificate and TrustServerCertificate=False.
3. Prepare the publish arguments
The target connection points to the separate AdventureWorksPreview database on the same SQL Server instance:
$connectionString = "Server=$server;Initial Catalog=$targetDatabase;Integrated Security=True;Encrypt=True;TrustServerCertificate=True;"
$publishArgs = @(
"/Action:Publish"
"/SourceFile:$dacpac"
"/TargetConnectionString:$connectionString"
"/p:LogDeployment=True"
"/p:EnableFastComparison=True"
)
Both feature flags are enabled from the first publish. This keeps the arguments identical for the repeat deployment.
4. Publish once to establish history
& $sqlpackage @publishArgs
if ($LASTEXITCODE -ne 0) {
throw "Initial database publish failed."
}
For a new target database, SqlPackage takes the normal comparison path, creates AdventureWorksPreview, and deploys the extracted AdventureWorks schema. With deployment logging enabled, it records deployment history in the target database.
Connect to localhost with your SQL query tool and inspect the target’s history:
USE [AdventureWorksPreview];
GO
SELECT *
FROM [sqlproj].[DeploymentHistory];
This intentionally does not depend on specific history-column names: the table’s metadata is part of a preview and may evolve. Treat the history table as tool-managed metadata rather than an application table to modify manually.
5. Publish the same model again
Run the same command, against AdventureWorksPreview, without re-extracting or changing the artifact or publish arguments:
& $sqlpackage @publishArgs
if ($LASTEXITCODE -ne 0) {
throw "Repeat database publish failed."
}
Now the target is an existing database with deployment history. If that history is compatible and the model checksum and deployment options match the last record, SqlPackage can skip the full schema comparison.
Look for this message in the publish output to confirm that the fast path was selected:
Fast comparison match found, source model checksum and deployment options match last logged deployment. Database reverse engineering and deployment plan generation is skipped. Disable fast comparison to run a full model comparison.
6. What happens when the model changes?
The two publishes above use the same extracted model. In a project-based pipeline, a schema change would produce a new .dacpac when you build the SQL project.
If you replace .\artifacts\AdventureWorks2025.dacpac with an updated model and publish again, a changed model checksum does not qualify for the matching-history shortcut. SqlPackage uses normal comparison to determine the schema changes.
The workflow remains declarative: you still deploy the desired database model, rather than maintaining an ordered list of migration scripts.
Use project versions in deployment scripts
Deployment history can also be used to refer back to a version defined in your SQL database project.
Set DacVersion in the SQL project’s .sqlproj file:
<PropertyGroup>
<DacVersion>1.0.0</DacVersion>
</PropertyGroup>
Build the project and publish its DACPAC with /p:LogDeployment=True. On supported targets, SqlPackage records the source DACPAC’s version in [sqlproj].[DeploymentHistory] and supplies these SQLCMD substitution variables to pre- and post-deployment scripts:
| Variable | Meaning |
|---|---|
$(DacVersion) |
The version of the incoming DACPAC. |
$(DacVersion_Previous) |
The last logged version for the same project in the target database; 0.0.0 when no previous deployment is recorded. |
These variables are not automatically supplied when LogDeployment=False. The incoming version comes from the DACPAC’s metadata, not a Publish-time version override. To exercise this example independently of fast comparison, publish with /p:EnableFastComparison=False.
The variables let you associate data changes with a particular release. For example, suppose version 1.0.0 requires a reference-data value to be inserted into an existing table before the schema changes are applied.
Assuming the target already contains [dbo].[ReferenceData] with [Code] and [Name] columns, add the following to the project’s pre-deployment script:
IF N'$(DacVersion)' = N'1.0.0'
BEGIN
INSERT INTO [dbo].[ReferenceData] ([Code], [Name])
SELECT N'STANDARD', N'Standard service'
WHERE NOT EXISTS
(
SELECT 1
FROM [dbo].[ReferenceData]
WHERE [Code] = N'STANDARD'
);
END;
The project version 1.0.0 is preserved in the DACPAC metadata and supplied as $(DacVersion).
When deploying version 1.0.0, the script inserts the value if it is missing. Publishing the same release again does not insert a duplicate. Publishing version 2.0.0 still records the deployment in history, but skips the reference-data insert because the condition only matches version 1.0.0.
Use $(DacVersion_Previous) instead when the condition concerns the version already deployed, such as data preparation needed specifically when upgrading from 1.0.0. This is the last logged version for the same project, not a live check of the target schema.
Important considerations
Matching history is not a live drift check
The matching-history path skips full schema comparison but it is not proof that the live database had not changed outside your pipeline.
If manual schema changes are possible, or you need to compare the current target schema explicitly, retain logging but turn fast comparison off:
& $sqlpackage `
/Action:Publish `
"/SourceFile:$dacpac" `
"/TargetConnectionString:$connectionString" `
/p:LogDeployment=True `
/p:EnableFastComparison=False
if ($LASTEXITCODE -ne 0) {
throw "Database publish with normal comparison failed."
}
Keep deployment scripts safe to rerun
In this preview, a fast-comparison match skips pre- and post-deployment scripts. They still run when SqlPackage uses normal comparison, even if it finds no schema changes. Keep those scripts safe to repeat.
Deployment history is for traceability only
Deployment history records what was deployed. It does not undo schema changes or restore deleted data. Continue to use your normal backup and recovery procedures.
Takeaway
The SqlPackage 170.6 preview combines deployment history for traceability with an opt-in shortcut for eligible repeat deployments.
Start with LogDeployment. Then evaluate EnableFastComparison where your schema is pipeline-managed and avoiding repeated comparison work makes sense. Keep normal comparison available whenever you need to examine the live target schema.
0 comments
Be the first to start the discussion.