Launch Deal · 50% off 1st month ·

2.6 — CI/CD with SQL Database Projects

DP-800 Domain 2 · Skill 2.6

What You Need to Know

SQL Database Projects turn a database schema into source code: one .sql file per object, built into a .dacpac and deployed by comparing it with the target. DP-800 tests the whole lifecycle, from repository to production.

The project

Concept Detail
SDK-style project A .sqlproj that uses the Microsoft.Build.Sql SDK. It builds with dotnet build on any OS, which suits CI. It's what VS Code (SQL Database Projects extension) and Fabric source control use
Build Validates syntax and references (unresolved objects fail the build, for example SQL71501) and produces a .dacpac
Target platform Set in the project (Azure SQL Database, SQL Server 2025, SQL database in Fabric) so that the build checks the right T-SQL
Database references To other projects or .dacpacs (such as master or another database) for cross-database objects
Pre- and post-deployment scripts Run before or after the schema change. Post-deployment scripts are where reference and static data lives, usually as an idempotent MERGE

Deploying and checking drift

SqlPackage action Use
Publish Compare the .dacpac with the target and apply the differences (incremental, model-based)
Script Produce the T-SQL that a publish would run, for review or for an approval gate
DeployReport An XML summary of the changes a publish would make
DriftReport Shows changes made directly in the target since the last deployment (schema drift)
Extract Create a .dacpac from an existing database, for example to start a project

Publish properties guard production, for example BlockOnPossibleDataLoss=True, and DropObjectsNotInSource only when you really mean it.

Testing strategy

  • Unit tests: for example tSQLt tests of procedures and functions in an isolated database (fake tables, assert results), run on every pull request.
  • Integration tests: deploy the .dacpac to an ephemeral database (a container or a temporary Azure SQL database), load test data, and run application-level tests.
  • Static analysis: code analysis rules during the build, such as SELECT *, missing schema names or non-SARGable patterns.

Source control and pipeline controls

Control Purpose
Branching Feature branches; changes go to main only through pull requests
Branch protection / policies Require a successful build, passing tests and reviews before merging
CODEOWNERS Automatically request reviews from the DBA team for changes under /schema/security/
Environments with approvals Require reviewers before the production deployment job runs
Triggers Run the build on pull requests, deploy to test on a merge to main, and deploy to production on approval or a release tag
Conflict resolution Object-per-file layout keeps conflicts small. Resolve them in the .sql files, then rebuild to validate
Secrets management No secrets in the repository: OIDC federated identity to Azure, connection details in pipeline secrets or Key Vault, and managed identity for the database login

Key Concept: The repository is the source of truth. Build validates and creates a .dacpac, Publish applies the differences, Script and DeployReport preview them, and DriftReport catches out-of-band changes. Put reference data in idempotent post-deployment scripts, and gate production with tests, reviews and approvals.

Exam Traps

  • Hand-writing ALTER scripts when the project and SqlPackage publish compute the differences
  • Putting reference data in a pre-deployment script (the tables may not exist yet) or writing it non-idempotently
  • Storing the production connection string with a password in the pipeline YAML
  • Skipping the drift check, then overwriting a hotfix that someone applied directly in production

Check your understanding

Practice Scenario

🔒 This practice scenario is part of Skillmarked Pro

The lessons stay free. Pro unlocks the other 14 DP-800 practice scenarios and the 50-question timed mock exam, plus everything for every other exam.

See plans

Launch Deal: first month €9.95, then €19.90/month. Enter code LAUNCH50 at checkout. Ends 1 December.

Cancel anytime. Or €199 a year, which saves 17%.

Not sure yet? Try 10 free mock exam questions.

Already Pro? Log in.