One Pipeline, Sixteen Databases: Parameterised SQL Deployments in Azure DevOps

One Pipeline, Sixteen Databases: Runtime-Parameterized SQL Deployments in Azure DevOps

How we replaced a pile of copy-pasted database pipelines (and a lot of manual SSMS work) with a single YAML pipeline where you pick the repo, branch, folder, files, and target database at run time.

---

The problem

Our team runs a portfolio of internal applications — each with its own Git repo, its own release branches, and its own Azure SQL database. Sixteen databases, spread across multiple SQL servers and even multiple Azure subscriptions.

Database changes lived as numbered SQL scripts inside each app's repo:

Database/DB_Scripts/
  01-CreateAlterTable.sql
  02-MetaData.sql
  03-CreateAlterSP.sql
  ...

The convention was good — numbered files, executed in order, kept next to the app code they belong to. The execution was the problem. Every release, someone opened SSMS, connected to the right server (hopefully), opened the right scripts from the right branch (hopefully), and ran them in the right order (hopefully). Three "hopefullys" per deployment, multiplied by many apps.

The obvious fix — one pipeline per database — would have meant sixteen nearly identical YAML files to maintain. We had already been burned by copy-paste pipeline sprawl on the application side. We wanted one pipeline.

Design goals

1. One pipeline for every database. No copies.
2. Choose everything at run time: which repo the scripts come from, which branch, which folder, which specific files, and which target database.
3. Scripts stay where they live — in each app's repo, on the app's release branches. No central "scripts repo" to keep in sync.
4. Credentials can never be typed wrong. The person running the pipeline picks a database from a dropdown; the pipeline resolves the server, service connection, and credentials itself.
5. A dry-run mode. See exactly what would execute before anything touches a database.

What running it looks like

Click Run pipeline and you get six fields:

Field	Example
1) Source repository	Orders Portal (dropdown of allowed repos)
2) Branch	Release/PROD (free text)
3) Scripts folder path	Database/DB_Scripts
4) SQL files to run	* or 01,03 or 02-MetaData.sql
5) Target database	OrdersDB (dropdown)
6) Preview only	✅ / ☐

Field 4 is the one people love. It matches by exact name or prefix, comma-separated — and matched files always execute in filename order, no matter what order you type them. 03,01 still runs 01-… before 03-…, so the tables-before-stored-procs convention can't be violated by a typo.

The preview step prints a contract before anything runs:

==============================================
 Target  : sql-orders-prod.database.windows.net / OrdersDB
 Folder  : Database/DB_Scripts
 Filter  : 01,03
 Selected 2 of 11 scripts, run order:
   -> 01-CreateAlterTable.sql
   -> 03-CreateAlterSP.sql
 Skipped:
   x  02-MetaData.sql
   ...
==============================================

With Preview only ticked, that's all that happens. Untick it, and exactly that list executes.

How it works

1. Runtime parameters drive a dynamic checkout

Azure DevOps resolves ${{ parameters.* }} expressions at compile time — which happens after you fill in the run dialog. That means parameters can be used in places you might not expect, including the checkout step:

steps:
  - checkout: git://MyProject/${{ parameters.sourceRepo }}@${{ parameters.sourceBranch }}
    displayName: 'Checkout ${{ parameters.sourceRepo }} @ ${{ parameters.sourceBranch }}'

One line, and the pipeline checks out any repo in the project at any branch the runner typed. (First contact with each repo may require a one-time authorization click, depending on your project's job-scope settings.)

2. The database dropdown resolves everything sensitive

The only mapping baked into YAML is database → server + service connection + credentials. It uses compile-time conditional variable insertion:

variables:
  - ${{ if in(parameters.targetDatabase, 'OrdersDB', 'InvoicesDB') }}:
    - name: dbServer
      value: 'sql-orders-prod.database.windows.net'
    - name: azSvcConnection
      value: 'AzureRM - Orders Subscription'
    - group: 'SQL - sql-orders-prod'       # contains sqlUser + sqlPassword (secret)

  - ${{ if eq(parameters.targetDatabase, 'BillingDB') }}:
    - name: dbServer
      value: 'sql-billing-prod.database.windows.net'
    - name: azSvcConnection
      value: 'AzureRM - Billing Subscription'
    - group: 'SQL - sql-billing-prod'

Pick OrdersDB and only the first block exists; the billing credentials are never even loaded into the run. Different servers, different subscriptions, different credentials — all handled by which dropdown value you picked. Nobody types a password, a server name, or a connection string, ever.

One variable group per SQL server (not per database — databases on a server share credentials) keeps the library tidy.

3. A shared template selects, previews, and executes

The reusable steps template does four things:

1. Select — list *.sql in the chosen folder, apply the file filter (exact name or prefix match), warn loudly about filter entries that matched nothing.
2. Preview — print the Selected and Skipped lists in run order.
3. Combine — concatenate the selected files, in filename order, into one script with GO separators and -- ===== FILE: name ===== markers between them. The markers mean any SQL error in the log points straight at the offending file.
4. Execute — one SqlAzureDacpacDeployment@1 task (deployType: SqlTask) runs the combined script. IpDetectionMethod: AutoDetect + DeleteFirewallRule: true means the task adds the build agent's IP to the SQL server firewall for the run and removes it afterwards — no standing firewall holes, and the Azure service connection is needed for nothing else.

The execute step is wrapped in ${{ if eq(parameters.previewOnly, false) }}, so preview mode compiles the deployment step out of the pipeline entirely rather than skipping it at run time.

Gotchas we hit (so you don't)

• A parameter's default must appear in its values list. If it doesn't, the run dialog refuses to open with a cryptic "value is not a valid value" error. Easy to cause when you reorder the dropdown.
• Variable group and service connection names must match character-for-character. Including spaces and suffixes. "Variable group not found" almost always means a one-character difference.
• Print the directory listing on path errors. Our template's first version just said "folder not found." Adding a listing of what does exist at the repo root turned a ten-minute investigation into a two-second one — the scripts folder had been created one level deeper than intended.
• Prefix matching needs consistent zero-padding. 01 is unambiguous; a bare 1 matches 10-… and 11-… too. Two-digit prefixes from day one.
• There is no "already ran" state. This pipeline executes what you select — it doesn't track migration history. Keep scripts idempotent (IF NOT EXISTS, CREATE OR ALTER), or step up to a migrations tool when you need real state tracking.
• Freedom cuts both ways. Nothing technically stops someone pointing App A's scripts at App B's database. The preview banner prints repo, folder, and target together — make "preview first, read the banner" the team habit, and put an approval check (Azure DevOps Environments) in front of production databases.

The result

Database deployments went from a manual, error-prone SSMS ritual to: pick five values, tick preview, read the list, run. Same pipeline for every database, every server, every subscription. Onboarding database number seventeen is a ten-line mapping block and one variable group.

The pattern generalizes beyond SQL, too: dropdown for anything sensitive, free text for anything flexible, compile-time mapping to connect them, and a preview mode so nobody has to trust the pipeline blindly.