Database Build & Deploy Pipeline
View pipeline in Azure DevOps
Purpose
- Builds the
S0PMOBAI.sqlproj DACPAC via a reusable database deployment template.
- Promotes the packaged DACPAC through Dev -> Test -> Stage -> Prod with gated approvals and secrets per environment.
Resources
- Imports
templates/db/database-deployment.yml from S0AIDXXX-AI Delivery/s0aidxxx-templates (tag v1.0.4).
- Uses Azure DevOps variable groups for environment-specific SQL passwords.
Triggers
- Branches:
main, any releases/* branch.
- Paths:
src/db/*.
- Lower environments skip on release branches with
not(startsWith(variables['Build.SourceBranch'], 'refs/heads/releases/')).
Agent Pool
- Runs on
ubuntu-latest-publix.
Stage Overview
- Single template-driven stage with parameters:
buildPath: **/S0PMOBAI.sqlproj.artifactName: S0PMOBAI.buildConfiguration: Release.environmentObjects: defines release order, approvals, and SQL connection metadata.
- The template compiles the SQL project, publishes the DACPAC artifact, and deploys to each environment.
Environment Matrix
| Env | Service Connection | ADO Environment | Condition | SQL Server | Database | Auto Approve |
|---|
| Dev | s0pmobai-01-dev | DEV_S0PMOBAI | Skipped on releases/* branches | cutdasqlpmobchat01 | cutdsqdbpmobchat01 | Yes |
| Tst | s0pmobai-01-tst | DEV_S0PMOBAI | Skipped on releases/* branches | cuttasqlpmobchat01 | cuttsqdbpmobchat01 | Yes |
| Stg | s0pmobai-01-stg | DEV_S0PMOBAI | Skipped on releases/* branches | cutsasqlpmobchat01 | cutssqdbpmobchat01 | No |
| Prd | s0pmobai-01-prd | PRD_S0PMOBAI | Always active | cutpasqlpmobchat01 | cutpsqdbpmobchat01 | No |
Release Branch Behavior
- Dev/Test/Stage environments are guarded by the release-branch condition, preventing redeployments from
releases/*.
- Production remains active so release branches can deploy after validation.
Maintenance Notes
- Update template tags deliberately; new versions may add or change required inputs.
- Maintain SQL usernames, passwords, and service connections to match Azure DevOps environments.
- Validate schema changes locally with
dotnet build src/db/S0PMOBAI/S0PMOBAI.sqlproj before pushing updates.