sql-deploy-pipeline

Robust, observable SQL Server deployment pipeline for SDK-style SSDT projects (Microsoft.Build.Sql), built on GitHub Actions.

Build once → deploy per environment, via a reviewed script — never a blind publish.

build (unprotected)                 deploy (one matrix leg per environment, protected)
┌──────────────────────┐            ┌────────────────────────────────────────────────┐
│ version (tag-driven) │            │ before-scripting hooks                         │
│ dotnet build → dacpac│   drop     │ sqlpackage DeployReport + Script               │
│ dotnet test          │ ─────────► │ DeployScriptTool transform                     │
│ dotnet pack → nupkg  │  artifact  │ DeployCore bootstrap → before-deploy hooks     │
│ push GitHub Packages │            │ execute (instrumented) → after-deploy hooks    │
└──────────────────────┘            │ step ledger → job summary + deploy.* tables    │
                                    └────────────────────────────────────────────────┘

What it does

• Two-stage pipeline: reusable build.yml (never environment-gated) and deploy.yml (a matrix leg per environment bound to a GitHub Environment — protection rules, required reviewers, and environment-scoped credentials SQL_SERVER / SQL_DATABASE / SQL_USER / SQL_PASSWORD). Environments deploy in parallel.
• Script-based deploys: every deploy generates a DeployReport + script, transforms it, archives both, then executes. The exact SQL that ran is always an artifact.
• Per-action observability: each SSDT action (CREATE/ALTER/DROP …) is instrumented — object name, status, duration — into deploy.DeploymentAction, plus client-side timestamped markers. The run itself (version, git SHA, run URL, actor, options) lands in deploy.DeploymentRun; deploy.CurrentVersion answers "what exactly is deployed here" from inside SQL.
• Run-once pre/post scripts (deploy-scripts/{pre,post}/{once,everytime}/): tracked by name + content hash in deploy.ScriptHistory. Multi-batch (GO) scripts fully supported — gating is per batch via the transform tool, not :r-include tricks. The DeployCore dacpac is published first, so the history table always exists before any gate runs (no chicken-and-egg).
• Pipeline hooks outside the dacpac (hooks/before-scripting, before-deploy, after-deploy) — e.g. drift fixes before the diff is computed, smoke checks after deploy. Timed and part of the ledger.
• Step ledger: every deployment ends with a markdown + JSON summary of all steps and hooks — status (success / failed / skipped (already applied) / not-run), duration, slowest steps — in the GitHub job summary and as artifacts.
• Local = CI: all logic is in scripts/*.sh; workflows and VS Code tasks are thin wrappers. Full cycle locally: scripts/full-deploy.sh local. Devcontainer included (dev box + SQL Server 2025 sidecar).
• Distribution: dacpacs pack into NuGet packages (Dbml.SqlDeploy.Core, the transform tool as a dotnet tool) pushed to GitHub Packages; tags create GitHub Releases with dacpacs + nupkgs attached.

Deployment options (per environment, deploy/environments/<env>.json)

option	default	effect
allowDataLoss	false	false keeps sqlpackage's data-loss guards in the script
expandVariables	true	inline SQLCMD $(Var) values; secretVariables are never inlined — the runner supplies them at execution time
moveIndexDropsLast	true	relocate index drops to the end of the schema steps — only when nothing later references the index (safe for drop+recreate)
collectStatistics	false	wrap each SSDT step in SET STATISTICS IO, TIME ON/OFF
rerunOnChange	false	run-once scripts re-run when their content hash changes

Quick start (local)

scripts/local-sql.sh up          # SQL Server 2025 container on localhost,14333
scripts/full-deploy.sh local     # build → hooks → script → transform → deploy
cat artifacts/local/summary.md   # the step ledger
scripts/full-deploy.sh local     # again: zero schema steps, once-scripts skipped

Or open in VS Code: every step is a task (Terminal → Run Task), the devcontainer brings its own SQL Server, and launch.json debugs the transform tool.

Using from another repo

jobs:
  build:
    uses: dkultasev/sql-deploy-pipeline/.github/workflows/build.yml@v1
  deploy:
    needs: build
    uses: dkultasev/sql-deploy-pipeline/.github/workflows/deploy.yml@v1
    secrets: inherit
    with:
      environments: '["dev","test"]'

Your project supplies: the .sqlproj (set PROJECT_DIR), optional deploy-scripts/ + hooks/ folders, and deploy/environments/*.json.

Repository layout

.github/workflows/   build.yml, deploy.yml (reusable) · ci.yml · cd.yml · release.yml
scripts/             ALL pipeline logic (bash) — shared by CI, VS Code, devcontainer
src/DeployCore/      deploy.* schema: runs, actions, script history (dacpac → nupkg)
src/DeployScriptTool/  script transform + instrumentation (.NET 8, ScriptDom-validated)
sample/SampleDb/     demo project: schema, run-once scripts, hooks
deploy/environments/ local / dev / test / prod configs
tests/               transform unit tests (fixtures modeled on real sqlpackage output)

Failure semantics

sqlcmd -b aborts on the first error; the runner then calls deploy.FailOpenRuns on a fresh connection, so the run and its in-flight action are closed as Failed, the ledger marks later steps not-run, and the job summary still renders. A failed run-once script is not recorded in ScriptHistory — it re-runs on the next deploy.