How to Implement CI/CD with SQLServer in PostgreSQL

Galaxy Glossary

How do I set up a CI/CD pipeline for SQL Server databases?

CI/CD for SQL Server automates building, testing, and deploying T-SQL changes from source control to each environment.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why automate SQL Server deployments with CI/CD?

Automating database releases removes manual steps, enforces code review, and guarantees every environment—from dev to prod—runs the same scripts. Teams catch errors earlier, shorten release cycles, and create reliable rollback points.

Which files should I commit to Git?

Commit every T-SQL change: DDL scripts, seed data, reference lists, and migration files. Version the .dacpac or schema.sql snapshot generated during CI so reviewers can see diffs.

How do I build and test in CI?

Use sqlcmd or SqlPackage inside a container. Spin up a disposable SQL Server 2022 container, execute migrations, then run unit tests with tSQLt or Pester-SQL. Tear down after tests pass.

What does a GitHub Actions pipeline look like?

The YAML below restores NuGet, builds a DACPAC, tests against a container, and deploys to staging when the main branch updates. Secrets store SQL_SA_PASSWORD and STG_CONN.

name: ci-cd-sqlserver on: push: branches: [main] jobs: build-test: runs-on: ubuntu-latest steps: - uses: actions/checkout@v4 - name: Build DACPAC run: dotnet build Database.csproj -c Release - name: Start SQL container run: | docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=${{ secrets.SQL_SA_PASSWORD }}" \ -p 1433:1433 -d mcr.microsoft.com/mssql/server:2022-latest - name: Deploy DACPAC to container run: sqlpackage /Action:Publish /SourceFile:bin/Release/Database.dacpac \ /TargetConnectionString:"Server=localhost,1433;User sa;Password=${{ secrets.SQL_SA_PASSWORD }};TrustServerCertificate=True;" - name: Run unit tests run: dotnet test Tests/Tests.csproj deploy-staging: needs: build-test runs-on: ubuntu-latest steps: - uses: actions/checkout@v4 - name: Publish to staging run: sqlpackage /Action:Publish /SourceFile:bin/Release/Database.dacpac \ /TargetConnectionString:"${{ secrets.STG_CONN }}".

How do I promote to production?

Gate the prod job behind a manual approval or pull-request merge. Store the prod connection string in an encrypted secret and call sqlpackage with /p:BlockOnPossibleDataLoss=true to abort risky drops.

Best practices for SQL Server CI/CD

Write idempotent scripts, name migrations sequentially, seed deterministic test data, and run smoke tests after deployment. Always back up before the first prod run and enable point-in-time recovery.

Why How to Implement CI/CD with SQLServer in PostgreSQL is important

How to Implement CI/CD with SQLServer in PostgreSQL Example Usage


-- 20240610_add_discount_to_products.sql
ALTER TABLE Products
ADD COLUMN discount NUMERIC(5,2) DEFAULT 0;

-- Execute in CI pipeline
sqlcmd -S localhost,1433 -U sa -P $SQL_SA_PASSWORD -d Ecommerce -i migrations/20240610_add_discount_to_products.sql

How to Implement CI/CD with SQLServer in PostgreSQL Syntax


sqlpackage /Action:Publish \
         /SourceFile:<path>/Database.dacpac \
         /TargetConnectionString:"Server=<server>;Database=<db>;User=<user>;Password=<pw>;TrustServerCertificate=True;" \
         /p:BlockOnPossibleDataLoss=true \
         /p:DropObjectsNotInSource=true \
         /v:CustomerName='Acme Inc.'

Example context:
Deploy the latest ecommerce schema to staging before merging:
sqlpackage /Action:Publish \
         /SourceFile:bin/Release/Ecommerce.dacpac \
         /TargetConnectionString:"Server=stg-sql;Database=Ecommerce;User sa;Password=$STG_PW;TrustServerCertificate=True;"

Common Mistakes

Relying on shared dev databases. Shared instances mask script conflicts and cause “works on my machine” bugs. Fix it by spinning up a fresh container per CI run.
Skipping data loss checks. Deploying with <code>/p:DropObjectsNotInSource=true</code> without <code>/p:BlockOnPossibleDataLoss=true</code> can truncate tables. Always enable blocking or manual review.

Frequently Asked Questions (FAQs)

Can I use Flyway or Liquibase instead?

Yes. Both tools version T-SQL scripts, integrate with GitHub Actions, and work with SQL Server. Replace sqlpackage steps with the Flyway or Liquibase CLI.