CI/CD Pipelines for SQL Databases

From manual scripts to automated deployments

Branching strategies for databases

Not every branching model works well for databases. Application code is stateless — you can switch branches freely. Database schemas are stateful — merging conflicting column changes is painful.

Strategy	How It Works	Best For
Trunk-based	Everyone commits to main with short-lived feature branches (hours, not weeks)	Small teams, frequent releases
GitFlow	Long-lived develop and release branches with feature branches	Large teams, scheduled releases
Environment branches	Separate branches per environment (dev, staging, prod)	Strict change promotion workflows

For database projects, trunk-based development with short-lived feature branches is safest. Why? Long-lived branches accumulate schema conflicts. If two developers both add columns to the same table on different branches for weeks, the merge is messy.

Pull request workflow for SQL changes

1. Developer creates feature branch from main
2. Makes schema changes (ALTER TABLE, new stored procedure, etc.)
3. Builds locally: dotnet build VaultBankDB.sqlproj
4. Opens pull request → triggers CI pipeline
5. CI pipeline: build → test → schema compare → report
6. Code review by database owner (CODEOWNERS)
7. Approval gate passed → merge to main
8. CD pipeline deploys .dacpac to target environment

💡 Exam tip: Conflict resolution in SQL projects

When two branches modify the same database object, Git shows a merge conflict in the .sql file. Resolution follows the same process as application code — edit the conflicting file to include both changes (or pick one), then build the project to validate the combined schema. The key exam point: always build after resolving conflicts to catch subtle issues like duplicate column names or broken foreign key references.

Schema drift detection

Schema drift happens when someone makes a change directly on the production database without going through source control. Your SQL Database Project says the table has 10 columns, but production has 11.

SqlPackage detects drift by comparing the .dacpac (what should be deployed) against the live database:

# Generate a drift report (no changes applied)
SqlPackage /a:DeployReport \
  /sf:bin/Release/VaultBankDB.dacpac \
  /tcs:"Server=vaultbank.database.windows.net;Database=VaultBankDB;Authentication=Active Directory Default" \
  /OutputPath:drift-report.xml

The report shows every difference — added columns, modified procedures, missing indexes. Your pipeline can fail the build if drift is detected, forcing the team to reconcile before deploying.

# GitHub Actions step to detect drift
- name: Check for schema drift
  run: |
    SqlPackage /a:DeployReport \
      /sf:VaultBankDB/bin/Release/VaultBankDB.dacpac \
      /tcs:"$CONN_STRING" \
      /OutputPath:drift-report.xml
    # Fail if report contains unexpected changes
    if grep -q "<Operation Name" drift-report.xml; then
      echo "Schema drift detected! Review drift-report.xml"
      exit 1
    fi

💡 Scenario: Priya catches a rogue index at Vault Bank

A DBA at Vault Bank created an index directly on the production database to fix a slow query over the weekend. On Monday, the CI/CD pipeline flags schema drift — the index exists in production but not in the SQL Database Project. Priya adds the index to the project, submits a pull request, and the team reviews whether the index is appropriate for all environments. The pipeline passes and the drift is resolved.

This is exactly why drift detection matters — it catches well-intentioned but untracked changes before they cause deployment surprises.

Secrets management

Database connection strings contain sensitive information. Hard-coding them in pipeline files is a security risk.

Secret Store	Integration	Best For
GitHub Secrets	Native in GitHub Actions, referenced as environment variables	GitHub-hosted pipelines
Azure DevOps Variable Groups	Linked to pipelines, supports Key Vault integration	Azure DevOps pipelines
Azure Key Vault	Central secret store, accessed via managed identity or service principal	Enterprise environments, shared secrets across pipelines

GitHub Actions with Key Vault

- name: Get connection string from Key Vault
  uses: Azure/get-keyvault-secrets@v1
  with:
    keyvault: "vaultbank-kv"
    secrets: "SqlConnectionString"
  id: kv-secrets

- name: Deploy database
  run: |
    SqlPackage /a:Publish \
      /sf:VaultBankDB/bin/Release/VaultBankDB.dacpac \
      /tcs:"$SQLCONNECTIONSTRING"
  env:
    SQLCONNECTIONSTRING: ${{ steps.kv-secrets.outputs.SqlConnectionString }}

Never store connection strings, passwords, or API keys in:

• Pipeline YAML files committed to the repository
• Application settings files checked into source control
• Build logs (mask variables in pipeline output)

Pipeline controls

The exam expects you to know the gates and guardrails that protect production deployments.

Branch protection rules

Branch: main
├── Require pull request before merging
├── Require at least 2 approvals
├── Require status checks to pass (build, test, drift check)
├── Require conversation resolution
└── Restrict who can push (no direct commits)

CODEOWNERS

A CODEOWNERS file assigns automatic reviewers based on file paths:

# .github/CODEOWNERS
# Database schema changes require DBA team review
/VaultBankDB/Tables/     @vaultbank/dba-team
/VaultBankDB/Security/   @vaultbank/dba-team @vaultbank/security

# Stored procedures can be reviewed by backend devs
/VaultBankDB/StoredProcedures/  @vaultbank/backend-team

# Pipeline changes require DevOps review
/.github/workflows/      @vaultbank/devops-team

Approval gates and triggers

Pipeline controls for database deployments

Control	What It Does	Where to Configure
Branch policy	Requires PR, reviewers, and passing checks before merge	Repository settings or Azure DevOps branch policies
CODEOWNERS	Auto-assigns reviewers based on changed file paths	CODEOWNERS file in repository root
Environment protection	Requires manual approval before deploying to specific environments	GitHub Environments or Azure DevOps Environments
Deployment gates	Automated checks (health check, query performance baseline) before proceeding	Azure DevOps release gates or GitHub Actions environment rules
Scheduled triggers	Pipelines run at specific times (e.g., deploy to prod only during maintenance windows)	Cron schedule in pipeline YAML

Deploying with SqlPackage

SqlPackage is the engine that applies .dacpac changes to a target database. It compares the model to the target, generates ALTER scripts, and executes them.

# Publish (deploy) the .dacpac to a target database
SqlPackage /a:Publish \
  /sf:VaultBankDB/bin/Release/VaultBankDB.dacpac \
  /tcs:"Server=vaultbank.database.windows.net;Database=VaultBankDB;Authentication=Active Directory Default" \
  /p:BlockOnPossibleDataLoss=true \
  /p:DropObjectsNotInSource=false

Key parameters:

• BlockOnPossibleDataLoss=true — stops deployment if a change would drop a column with data
• DropObjectsNotInSource=false — keeps objects that exist in the target but not in the project (safer for incremental adoption)

💡 Exam tip: SqlPackage actions

Know the four main SqlPackage actions:

• Publish — deploys .dacpac to a target database (generates and runs ALTER scripts)
• DeployReport — generates an XML report of what WOULD change without applying anything
• Script — generates the deployment SQL script without executing it (for manual review)
• Extract — creates a .dacpac from an existing database (reverse-engineer the schema)

The exam may describe a scenario where a team wants to review changes before applying them. The answer is Script or DeployReport, not Publish.

Question

Why is trunk-based development preferred for database projects?

Click or press Enter to reveal answer

Answer

Long-lived branches accumulate schema conflicts that are difficult to merge. If two developers modify the same table on separate branches for weeks, reconciling the changes is error-prone. Trunk-based development with short-lived branches (merged within hours or days) keeps conflicts small and manageable.

Click to flip back

Question

What is schema drift and how do you detect it?

Click or press Enter to reveal answer

Answer

Schema drift occurs when the live database differs from the SQL Database Project definition — usually because someone made a change directly on the server. Detect it by running SqlPackage with the DeployReport action, which compares the .dacpac to the target and lists every difference. Pipelines can automate this check on every build.

Click to flip back

Question

What is a CODEOWNERS file?

Click or press Enter to reveal answer

Answer

A CODEOWNERS file maps file paths to required reviewers. When a pull request changes files matching a pattern, the specified teams are automatically assigned as reviewers. For database projects, this ensures DBA team review for schema changes and security team review for permission changes.

Click to flip back

Question

What does BlockOnPossibleDataLoss do in SqlPackage?

Click or press Enter to reveal answer

Answer

When set to true, SqlPackage stops the deployment if any change would cause data loss — such as dropping a column that contains data, reducing a column width, or changing a data type. This is a critical safety net for production deployments.

Click to flip back

Knowledge Check

Priya's team at Vault Bank wants to ensure that database schema changes cannot reach production without DBA review, passing unit tests, and no schema drift detected. Which combination of controls should she implement?

ABranch protection rules requiring status checks and CODEOWNERS for DBA reviewBManual deployment by DBAs only, with no automated pipelineCA single approval gate on the production environment with no CI checksDCODEOWNERS file alone, with no branch protection or CI pipeline

Check Answer

Knowledge Check

A deployment pipeline runs SqlPackage /a:Publish against the staging database. The command fails with 'Rows were detected. The schema update is terminating because data loss might occur.' What caused this error and how should the team proceed?

AA bug in SqlPackage — retry the deploymentBBlockOnPossibleDataLoss=true detected a destructive change like dropping a populated column. Review the change and either migrate data first or explicitly allow the loss.CThe staging database is locked by another transaction. Wait and retry.DThe .dacpac is corrupted. Rebuild the SQL Database Project.

Check Answer

🎬 Video coming soon

Next up: Data API Builder: REST and GraphQL from SQL — expose your database as REST and GraphQL APIs without writing backend code.