SSDT in the CI Pipeline: Database Deployments That Don't Require a Human

Shannon Lowder

Shannon Lowder

— 2 min read

By 2015, continuous integration was standard for application code: every commit triggered a build, every build ran tests, the green/red status was visible. The database was frequently the exception: schema changes deployed manually by a DBA, tested in staging only when someone remembered to, deployed to production during a maintenance window by a human running SqlPackage from a laptop. The deployment was the bottleneck, and the bottleneck was a person.

Getting the database into the CI/CD pipeline was the missing piece, and by 2015, the tooling was mature enough to do it well. Here's the pipeline I built and what it took to make it work.

The Pipeline Design

The database CI/CD pipeline I used had four stages:

  1. Build — compile SSDT project to dacpac, verify it builds without errors
  2. Test — deploy dacpac to a temporary database, run tSQLt tests against it, tear down the temporary database
  3. Stage — deploy dacpac to the staging environment, run smoke tests
  4. Production — deploy dacpac to production, with required approval gate before execution

Stages 1–3 ran automatically on every commit. Stage 4 required manual approval but then executed without a human touching SqlPackage directly.

The Build Step

# MSBuild via Azure DevOps (2015 was still TFS/VSTS, but the logic is the same)
MSBuild.exe YourDatabase.sqlproj   /p:Configuration=Release   /p:OutputPath=$(Build.ArtifactStagingDirectory)

The dacpac goes to the artifact staging directory and gets published as a build artifact. Every build produces a versioned, immutable dacpac. Deployments use the artifact, not a fresh build — the same artifact that passed the build and test stages is what gets deployed to staging and eventually production.

The Test Step

tSQLt is the SQL Server unit testing framework. Tests run as stored procedures inside the database. For CI, the pattern was:

  1. Deploy the dacpac to a fresh database on the build agent's SQL Server (or a dedicated test instance)
  2. Run tSQLt tests via EXEC tSQLt.RunAll
  3. Parse the results — failures fail the build
  4. Drop the test database
# Deploy to test database
SqlPackage.exe /Action:Publish   /SourceFile:YourDatabase.dacpac   /TargetConnectionString:"Server=test-sql;Database=YourDatabase_CI_$(Build.BuildId);Integrated Security=True"   /TargetDatabaseName:YourDatabase_CI_$(Build.BuildId)

# Run tSQLt tests
Invoke-SqlCmd -ServerInstance "test-sql"   -Database "YourDatabase_CI_$(Build.BuildId)"   -Query "EXEC tSQLt.RunAll"

# Drop test database (cleanup)
Invoke-SqlCmd -ServerInstance "test-sql"   -Query "DROP DATABASE [YourDatabase_CI_$(Build.BuildId)]"

The Production Gate

The approval gate before production deployment was important. Not because the deployment itself was risky — the dacpac was the same artifact that had already been tested and deployed to staging — but because the timing of a production deployment mattered. You didn't want a schema change deploying at 3 PM on a Friday automatically. The gate gave the team control over timing without making the deployment itself a manual process.

What the approval gate should not be: a review of whether the change is safe. That review happened in code review, before the commit merged. By the time the deployment was waiting for approval, the change had already been reviewed, tested, and staged. The approval gate was about timing, not safety.

What Changed Once This Was Working

The most significant change was psychological, not technical. When database deployments were manual, they accumulated. You'd hold three weeks of schema changes and deploy them all in one window to minimize disruption. That meant larger, riskier deployments with more things that could go wrong.

When deployments were automated, small schema changes went out with the application code that depended on them. A new column deployed the same day the application code that used it deployed. Fewer things changed in each deployment, each deployment was lower risk, and the "hold changes for the window" behavior disappeared because there was no window — just a pipeline and an approval gate.

The database stopped being the thing that slowed down releases. It became part of the release, on the same cadence as everything else. That's the goal. As always, I'm here to help.

Read more