SQLTreeo
SQLTreeo Compare · How-to

Detect schema drift in a CI pipeline

Fail a build or raise an alert when a SQL Server database drifts from its reference, with the sqlcompare command line in GitHub Actions or Azure DevOps.

Schema drift is the difference between what a database should look like and what it looks like now: an index added by hand in production, a procedure hot-fixed on one server, a column that never reached the test environment. The next deployment then fails, or works on test and breaks in production.

You catch drift by comparing the database with its reference automatically, every night or with every build. The sqlcompare command line is made for that: it compares, writes a report and tells the pipeline the outcome through its exit code.

Exit code Meaning
0 No differences, or the deployment succeeded
1 Differences found
2 Error, for example a server that cannot be reached or a mistyped option

Set up the agent

Download the command line zip from the downloads page and extract it on the build agent, for example in C:\tools\sqlcompare. It is a self-contained Windows x64 program; nothing else has to be installed.

Keep connection strings out of the pipeline file. sqlcompare reads them from the environment variables SQLCOMPARE_SOURCE and SQLCOMPARE_TARGET, which you fill from the secret store of your pipeline.

The check

sqlcompare schema --format html --output drift.html

With the two environment variables set, this compares the source with the target and writes an HTML report. The step fails when the exit code is not 0, which is exactly what you want: drift stops the pipeline and the report shows what is different.

Limit the comparison to what you deploy. For example without users, roles and permissions, which usually differ per environment:

sqlcompare schema --exclude Users,Roles,Permissions --format html --output drift.html

Other useful switches are --schema and --exclude-schema, --object and --exclude-object for names with wildcards, and the --ignore-… options listed by sqlcompare schema --help.

GitHub Actions

On a Windows runner where the tool is installed:

on:
  schedule:
    - cron: '0 3 * * *'
  workflow_dispatch:

jobs:
  schema-drift:
    runs-on: [self-hosted, windows]
    steps:
      - name: Compare production with the reference database
        shell: pwsh
        env:
          SQLCOMPARE_SOURCE: ${{ secrets.SHOP_REFERENCE_CONNECTION }}
          SQLCOMPARE_TARGET: ${{ secrets.SHOP_PRODUCTION_CONNECTION }}
        run: C:\tools\sqlcompare\sqlcompare.exe schema --exclude Users,Roles,Permissions --format html --output drift.html

      - name: Keep the report
        if: always()
        uses: actions/upload-artifact@v4
        with:
          name: schema-drift-report
          path: drift.html

The schedule runs the check every night at 03:00 UTC, and workflow_dispatch lets you start it by hand. Use shell: powershell when PowerShell 7 is not installed on the runner.

Azure DevOps

pool:
  name: MyWindowsAgents   # a self-hosted pool where the tool is installed

steps:
  - pwsh: C:\tools\sqlcompare\sqlcompare.exe schema --exclude Users,Roles,Permissions --format html --output $(Build.ArtifactStagingDirectory)\drift.html
    displayName: Compare production with the reference database
    env:
      SQLCOMPARE_SOURCE: $(ShopReferenceConnection)
      SQLCOMPARE_TARGET: $(ShopProductionConnection)

  - publish: $(Build.ArtifactStagingDirectory)\drift.html
    artifact: schema-drift-report
    condition: always()

Secret variables are not passed to scripts automatically in Azure DevOps, which is why they are mapped under env.

Warn instead of fail

To report drift without breaking the build, read the exit code yourself and fail only on a real error:

C:\tools\sqlcompare\sqlcompare.exe schema --format html --output drift.html
if ($LASTEXITCODE -notin 0, 1) { throw "The comparison failed." }
if ($LASTEXITCODE -eq 1) { Write-Warning "Schema drift found, see drift.html." }
exit 0

Use --format json when another tool has to read the result.

Deploy from the pipeline

The same command can bring the target in line with the source:

sqlcompare schema --deploy --script deployed.sql

The script is generated, executed against the target inside a transaction and saved for your records. When a batch fails, the transaction is rolled back and the exit code is 2. After a successful deployment the exit code is 0. Add --no-drops when objects that only exist in the target must stay.

Licensing on build servers

The command line runs during the 30-day trial and needs a license key after that: set SQLCOMPARE_LICENSE_KEY, or run sqlcompare license --key <key> once under the account the agent runs as. A build or automation server needs its own key, see the license agreement.