Schema Drift Is a Bug: How SSDT Changed My Release Process

Shannon Lowder

Shannon Lowder

— 2 min read

Schema drift is when your production database has diverged from what your source control says it should be. This happens constantly in environments where developers can make direct schema changes in SSMS, where "emergency fixes" get applied to production and the plan to commit them later never materializes, or where the deployment process is manual enough that something gets missed. Before SSDT, drift was a chronic condition. With SSDT, it's detectable — and detectable problems are fixable problems.

How Drift Happens in Practice

Scenario one: a developer discovers a missing index causing a performance problem in production. They add the index directly in SSMS. The immediate problem is solved. The index never makes it into the SSDT project. Three months later, the database is deployed to a new environment from the dacpac, the index isn't there, and the performance problem reappears. Nobody knows why.

Scenario two: a stored procedure is "temporarily" updated in production to work around a bug. The intent is to fix it properly and deploy through the normal process. The workaround stays in production for six months. The SSDT project has the original version. When the next deployment runs, it overwrites the workaround with the original broken version. At 2 AM.

These aren't exotic failure modes. They're routine without deliberate discipline around them.

The Drift Detection Workflow

SSDT's Schema Compare tool runs a comparison between the project and a live database, showing every difference. I built this into a weekly automated check that ran as an Azure VM scheduled task and sent an email report if it found differences:

# PowerShell: generate a schema comparison report
$dacpacPath = "\buildserverrtifacts\YourDatabase.dacpac"
$targetConnection = "Server=prod-sql;Database=YourDatabase;Integrated Security=True"

# Use SqlPackage in report mode
& "C:\Program Files\Microsoft SQL ServerP\DACin\SqlPackage.exe" `
  /Action:DeployReport `
  /SourceFile:$dacpacPath `
  /TargetConnectionString:$targetConnection `
  /OutputPath:C:\DriftReport\$(Get-Date -Format 'yyyyMMdd')-drift.xml

# Parse the XML report and alert if differences exist
[xml]$report = Get-Content "C:\DriftReport\$(Get-Date -Format 'yyyyMMdd')-drift.xml"
$changes = $report.DeploymentReport.Alerts.Alert | Where-Object { $_.Name -ne "None" }

if ($changes) {
    Send-MailMessage -To "dba@yourcompany.com" `
      -Subject "Schema Drift Detected: Production vs SSDT Project" `
      -Body "Differences found. Review: C:\DriftReport\..."
}

The weekly cadence caught drift before it accumulated to the point where reconciling it was a research project. If the alert fired, the response was immediate: either commit the out-of-band change to the project (if it was intentional) or roll it back in production (if it was a forgotten fix). Either way, the discrepancy got resolved.

The Release Process With SSDT

With drift detection in place, the release process became simpler and more predictable:

  1. Development branch merges to main — SSDT project files are part of the merge
  2. Build pipeline compiles the SSDT project to a dacpac and stores it as a build artifact
  3. Deployment pipeline: generate the delta script against the target environment, review it, apply it
  4. Post-deployment: run drift detection against the deployed environment to confirm the dacpac matches reality

Step 4 was the key addition. Running drift detection immediately after a deployment confirmed that what was deployed matched the dacpac — and caught the edge cases where a deployment partially succeeded, or where a constraint prevented a change from applying, and the error was swallowed by a misconfigured error handler.

The Cultural Shift

The technical tooling is the easy part. The harder part is building the habit that schema changes don't happen outside the SSDT project. This requires two things: a deployment pipeline smooth enough that going through it is easier than going around it, and an enforcement mechanism (the drift detection alert) that surfaces deviations quickly.

When the "quick fix in SSMS" generates an alert the next morning, the behavior changes. Not because the process is punitive, but because the cost of the workaround — the alert, the reconciliation, the explanation — exceeds the cost of doing it correctly. That's the right incentive structure. As always, I'm here to help.

Read more