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.
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.
Sources
- SQL Database Projects overview, Microsoft Learn
- Build a SQL project from the command line (SDK-style), Microsoft Learn
- SqlPackage publish and other actions, Microsoft Learn
- Pre- and post-deployment scripts, Microsoft Learn
- Configure OpenID Connect in Azure (GitHub Actions), GitHub Docs