Publishing Profiles: Environment-Specific Database Deployments with SSDT

Shannon Lowder

Shannon Lowder

— 2 min read

A dacpac contains the schema model. A publish profile controls how that model gets applied to a specific environment. Without publish profiles, every deployment would use the same settings — the same permissiveness for destructive changes, the same connection details, the same behavior for out-of-sync objects. With publish profiles, dev, staging, and production can have genuinely different deployment policies from the same source artifact. This is how you make the "deploy everywhere the same way" ideal work in practice without pretending that dev and production are the same environment.

What a Publish Profile Contains

A publish profile is an XML file that lives alongside your SSDT project in source control:

<!-- dev.publish.xml -->
<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="14.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <TargetConnectionString>Data Source=dev-sql;Initial Catalog=YourDatabase;...</TargetConnectionString>
    <BlockOnPossibleDataLoss>False</BlockOnPossibleDataLoss>
    <DropObjectsNotInSource>True</DropObjectsNotInSource>
    <GenerateSmartDefaults>True</GenerateSmartDefaults>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <DeployScriptFileName>YourDatabase.sql</DeployScriptFileName>
  </PropertyGroup>
</Project>
<!-- production.publish.xml -->
<Project ToolsVersion="14.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <TargetConnectionString>Data Source=prod-sql;Initial Catalog=YourDatabase;...</TargetConnectionString>
    <BlockOnPossibleDataLoss>True</BlockOnPossibleDataLoss>
    <DropObjectsNotInSource>False</DropObjectsNotInSource>
    <GenerateSmartDefaults>True</GenerateSmartDefaults>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <DeployScriptFileName>YourDatabase.sql</DeployScriptFileName>
  </PropertyGroup>
</Project>

Notice what's different: dev sets BlockOnPossibleDataLoss to False (destructive changes are allowed in dev — you want to be able to drop and recreate objects freely) and DropObjectsNotInSource to True (dev environments should match the project exactly). Production sets BlockOnPossibleDataLoss to True (never automatically destroy data) and DropObjectsNotInSource to False (preserve anything in the database that isn't in the project — safer on production where out-of-band objects might be intentional).

Separating Credentials from the Profile

The connection string in the publish profile includes the server and database name, but not the password. Passwords are passed at deploy time, not stored in source control:

# Deploy using SqlPackage, passing password separately
SqlPackage.exe /Action:Publish   /Profile:production.publish.xml   /TargetPassword:"$env:DB_DEPLOY_PASSWORD"

The deploy password comes from the CI/CD system's secret store (Azure Key Vault, or a build server credential store), not from a file on disk. The publish profile file is safe to commit to source control. The credential is not.

SQLCMD Variables for Environment-Specific Content

Sometimes the schema itself needs to reference environment-specific values — a linked server name, a filegroup name, a default schema for a specific environment. SSDT supports SQLCMD variables for this:

-- In the SSDT project .sql file, use a variable:
CREATE TABLE [$(SchemaName)].Customers (
    ...
);
<!-- In the publish profile, define the variable value: -->
<SqlCmdVariable Name="SchemaName">production</SqlCmdVariable>

The variable is substituted at deploy time with the value from the profile. This handles the cases where dev and production can't literally have the same schema without this kind of parameterization.

The CI/CD Integration

With publish profiles in source control and credentials managed separately, the CI/CD pipeline becomes straightforward:

# Build step: compile SSDT project to dacpac
MSBuild YourDatabase.sqlproj /p:Configuration=Release

# Deploy to dev (on every commit to main)
SqlPackage.exe /Action:Publish   /Profile:dev.publish.xml   /TargetPassword:$DEV_PASSWORD

# Deploy to production (on release approval)
SqlPackage.exe /Action:Publish   /Profile:production.publish.xml   /TargetPassword:$PROD_PASSWORD

Same dacpac, different profiles, different behavior per environment. The differences are explicit and version-controlled, not implicit and tribal. That's the goal: every deployment decision should be readable in a file, not reconstructable from memory. As always, I'm here to help.

Read more