🚨 CRITICAL GUIDELINES

Windows File Path Requirements

MANDATORY: Always Use Backslashes on Windows for File Paths

When using Edit or Write tools on Windows, you MUST use backslashes (\) in file paths, NOT forward slashes (/).

Examples:

❌ WRONG: D:/repos/project/file.tsx
✅ CORRECT: D:\repos\project\file.tsx

This applies to:

Edit tool file_path parameter
Write tool file_path parameter
All file operations on Windows systems

Documentation Guidelines

NEVER create new documentation files unless explicitly requested by the user.

Priority: Update existing README.md files rather than creating new documentation
Repository cleanliness: Keep repository root clean - only README.md unless user requests otherwise
Style: Documentation should be concise, direct, and professional - avoid AI-generated tone
User preference: Only create additional .md files when user specifically asks for documentation

SSDT CI/CD Best Practices 2025

Overview

This skill provides comprehensive guidance on implementing modern CI/CD pipelines for SQL Server database projects using SSDT, SqlPackage, and contemporary DevOps practices.

Key Principles (2025 Recommended Approach)

1. State-Based Deployment (Recommended)

Definition: Source code represents the current database state, not migration scripts.

How it Works:

All database objects (tables, procedures, views, functions) stored in separate .sql files
SqlPackage automatically generates incremental deployment scripts
Declarative approach: "This is what the database should look like"
SSDT compares source to target and calculates differences

Advantages:

Easier to maintain and understand
No risk of missing migration scripts
Git history shows complete object definitions
Branching and merging simplified
Rollback by redeploying previous version

Implementation:

# GitHub Actions example
- name: Build DACPAC (State-Based)
  run: dotnet build Database.sqlproj -c Release

- name: Deploy State to Target
  run: |
    sqlpackage /Action:Publish \
      /SourceFile:Database.dacpac \
      /TargetConnectionString:"${{ secrets.SQL_CONN }}" \
      /p:BlockOnPossibleDataLoss=True

Contrast with Migration-Based:

Migration-based: Sequential scripts (001_CreateTable.sql, 002_AddColumn.sql)
State-based: Object definitions (Tables/Customer.sql contains complete CREATE TABLE)

2. tSQLt Unit Testing (Critical for CI/CD)

Why tSQLt:

Open-source SQL Server unit testing framework
Write tests in T-SQL language
Produces machine-readable XML/JSON results
Integrates seamlessly with CI/CD pipelines

Key Features:

Automatic Transactions: Each test runs in a transaction and rolls back
Schema Grouping: Group related tests in schemas
Mocking: Fake tables and procedures for isolated testing
Assertions: Built-in assertion methods (assertEquals, assertEmpty, etc.)

Pipeline Abort on Failure:

# GitHub Actions with tSQLt
- name: Run tSQLt Unit Tests
  run: |
    # Deploy test framework
    sqlpackage /Action:Publish \
      /SourceFile:DatabaseTests.dacpac \
      /TargetConnectionString:"${{ secrets.TEST_SQL_CONN }}"

    # Execute tests and capture results
    sqlcmd -S test-server -d TestDB -Q "EXEC tSQLt.RunAll" -o test-results.txt

    # Parse results and fail pipeline if tests fail
    if grep -q "Failure" test-results.txt; then
      echo "Unit tests failed!"
      exit 1
    fi

    echo "All tests passed!"

- name: Deploy to Production (only runs if tests pass)
  run: |
    sqlpackage /Action:Publish \
      /SourceFile:Database.dacpac \
      /TargetConnectionString:"${{ secrets.PROD_SQL_CONN }}"

Test Structure:

-- tSQLt test example
CREATE SCHEMA CustomerTests;
GO

CREATE PROCEDURE CustomerTests.[test Customer Insert Sets Correct Defaults]
AS
BEGIN
    -- Arrange
    EXEC tSQLt.FakeTable 'dbo.Customers';

    -- Act
    INSERT INTO dbo.Customers (FirstName, LastName, Email)
    VALUES ('John', 'Doe', 'john@example.com');

    -- Assert
    EXEC tSQLt.AssertEquals @Expected = 1,
                             @Actual = (SELECT COUNT(*) FROM dbo.Customers);
    EXEC tSQLt.AssertNotEquals @Expected = NULL,
                                @Actual = (SELECT CreatedDate FROM dbo.Customers);
END;
GO

-- Run all tests
EXEC tSQLt.RunAll;

Azure DevOps Integration:

- task: PowerShell@2
  displayName: 'Run tSQLt Tests'
  inputs:
    targetType: 'inline'
    script: |
      # Execute tSQLt tests
      $results = Invoke-Sqlcmd -ServerInstance $(testServer) `
                                -Database $(testDatabase) `
                                -Query "EXEC tSQLt.RunAll" `
                                -Verbose

      # Check for failures
      $failures = $results | Where-Object { $_.Class -eq 'Failure' }
      if ($failures) {
        Write-Error "Tests failed: $($failures.Count) failures"
        exit 1
      }

3. Windows Authentication Over SQL Authentication

Security Best Practice: Prefer Windows Authentication (Integrated Security) for CI/CD agents.

Why Windows Auth:

No passwords stored in connection strings
Leverages existing Active Directory infrastructure
Service accounts with minimal permissions
Audit trail via Windows Security logs
No credential rotation needed

Implementation:

Self-Hosted Agents (Recommended):

# GitHub Actions with self-hosted Windows agent
runs-on: [self-hosted, windows, sql-deploy]

steps:
  - name: Deploy with Windows Auth
    run: |
      sqlpackage /Action:Publish \
        /SourceFile:Database.dacpac \
        /TargetConnectionString:"Server=prod-sql;Database=MyDB;Integrated Security=True;" \
        /p:BlockOnPossibleDataLoss=True

Azure DevOps with Service Connection:

- task: SqlAzureDacpacDeployment@1
  inputs:
    authenticationType: 'integratedAuth'  # Uses Windows Auth
    serverName: 'prod-sql.domain.com'
    databaseName: 'MyDatabase'
    dacpacFile: '$(Build.ArtifactStagingDirectory)/Database.dacpac'

Alternative for Cloud Agents (Azure SQL):

# Use Managed Identity instead of SQL auth
- name: Deploy with Managed Identity
  run: |
    sqlpackage /Action:Publish \
      /SourceFile:Database.dacpac \
      /TargetConnectionString:"Server=tcp:server.database.windows.net;Database=MyDB;Authentication=ActiveDirectoryManagedIdentity;" \
      /p:BlockOnPossibleDataLoss=True

Never Do This:

# BAD: Plain text SQL auth password
TargetConnectionString: "Server=prod;Database=MyDB;User=sa;Password=P@ssw0rd123"

If SQL Auth Required:

# Use secrets/variables (least preferred method)
- name: Deploy with SQL Auth (Not Recommended)
  run: |
    sqlpackage /Action:Publish \
      /SourceFile:Database.dacpac \
      /TargetServerName:"${{ secrets.SQL_SERVER }}" \
      /TargetDatabaseName:"${{ secrets.SQL_DATABASE }}" \
      /TargetUser:"${{ secrets.SQL_USER }}" \
      /TargetPassword:"${{ secrets.SQL_PASSWORD }}" \
      /p:BlockOnPossibleDataLoss=True
  # Still not as secure as Windows Auth!

4. Version Control Everything

What to Version:

DatabaseProject/
├── Tables/
│   ├── Customer.sql
│   └── Order.sql
├── StoredProcedures/
│   └── GetCustomerOrders.sql
├── Tests/
│   ├── CustomerTests/
│   │   └── test_CustomerInsert.sql
│   └── OrderTests/
│       └── test_OrderValidation.sql
├── Scripts/
│   ├── Script.PreDeployment.sql
│   └── Script.PostDeployment.sql
├── Database.sqlproj
├── Database.Dev.publish.xml
├── Database.Prod.publish.xml
└── .gitignore

.gitignore:

# Build outputs
bin/
obj/
*.dacpac

# User-specific files
*.user
*.suo

# Visual Studio folders
.vs/

# Never commit credentials
*.publish.xml.user

Check-in Requirements:

Require code review for database changes
Mandate comments on all commits
Run automated tests before merge
Enforce naming conventions via branch policies

5. Deployment Reports Always Required

Before Production Deployment:

- name: Generate Deployment Report
  run: |
    sqlpackage /Action:DeployReport \
      /SourceFile:Database.dacpac \
      /TargetConnectionString:"${{ secrets.PROD_SQL_CONN }}" \
      /OutputPath:deploy-report.xml \
      /p:BlockOnPossibleDataLoss=True

- name: Parse and Review Report
  run: |
    # Extract key metrics from XML
    echo "=== DEPLOYMENT REPORT ==="
    # Parse XML for operations count
    # Check for data loss warnings
    # Display to user or post to PR

- name: Require Manual Approval
  uses: trstringer/manual-approval@v1
  with:
    approvers: database-admins
    minimum-approvals: 1
    instructions: "Review deploy-report.xml before approving"

- name: Deploy After Approval
  run: |
    sqlpackage /Action:Publish \
      /SourceFile:Database.dacpac \
      /TargetConnectionString:"${{ secrets.PROD_SQL_CONN }}"

6. Environment Promotion Strategy

Standard Flow: Dev → QA → Staging → Production

Consistent Deployment Options:

# Define environment-specific properties
environments:
  dev:
    blockOnDataLoss: false
    dropObjectsNotInSource: true
    backupBeforeChanges: false

  qa:
    blockOnDataLoss: true
    dropObjectsNotInSource: false
    backupBeforeChanges: true

  staging:
    blockOnDataLoss: true
    dropObjectsNotInSource: false
    backupBeforeChanges: true

  production:
    blockOnDataLoss: true
    dropObjectsNotInSource: false
    backupBeforeChanges: true
    requireApproval: true

Complete GitHub Actions Pipeline (2025 Best Practice)

name: SQL Server CI/CD Pipeline

on:
  push:
    branches: [main, develop]
  pull_request:
    branches: [main]

env:
  DOTNET_VERSION: '8.0.x'
  SQLPACKAGE_VERSION: '170.2.70'

jobs:
  build:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - name: Setup .NET 8
        uses: actions/setup-dotnet@v4
        with:
          dotnet-version: ${{ env.DOTNET_VERSION }}

      - name: Install SqlPackage
        run: dotnet tool install -g Microsoft.SqlPackage --version ${{ env.SQLPACKAGE_VERSION }}

      - name: Build Database Project
        run: dotnet build src/Database.sqlproj -c Release

      - name: Build Test Project
        run: dotnet build tests/DatabaseTests.sqlproj -c Release

      - name: Upload DACPAC Artifacts
        uses: actions/upload-artifact@v4
        with:
          name: dacpacs
          path: |
            src/bin/Release/*.dacpac
            tests/bin/Release/*.dacpac

  test:
    runs-on: windows-latest  # tSQLt requires SQL Server
    needs: build
    steps:
      - uses: actions/checkout@v4

      - name: Download Artifacts
        uses: actions/download-artifact@v4
        with:
          name: dacpacs

      - name: Setup Test Database
        run: |
          sqlcmd -S localhost -Q "CREATE DATABASE TestDB"

      - name: Deploy Database to Test
        run: |
          sqlpackage /Action:Publish `
            /SourceFile:Database.dacpac `
            /TargetConnectionString:"Server=localhost;Database=TestDB;Integrated Security=True;"

      - name: Deploy tSQLt Framework
        run: |
          sqlpackage /Action:Publish `
            /SourceFile:DatabaseTests.dacpac `
            /TargetConnectionString:"Server=localhost;Database=TestDB;Integrated Security=True;"

      - name: Run tSQLt Unit Tests
        run: |
          $results = Invoke-Sqlcmd -ServerInstance localhost `
                                    -Database TestDB `
                                    -Query "EXEC tSQLt.RunAll" `
                                    -Verbose

          $failures = $results | Where-Object { $_.Class -eq 'Failure' }
          if ($failures) {
            Write-Error "Tests failed: $($failures.Count) failures"
            exit 1
          }
          Write-Host "All tests passed!"

  deploy-dev:
    runs-on: [self-hosted, windows, sql-deploy]
    needs: test
    if: github.ref == 'refs/heads/develop'
    environment: dev
    steps:
      - name: Download Artifacts
        uses: actions/download-artifact@v4
        with:
          name: dacpacs

      - name: Deploy to Dev (Windows Auth)
        run: |
          sqlpackage /Action:Publish `
            /SourceFile:Database.dacpac `
            /TargetConnectionString:"Server=dev-sql;Database=MyDB;Integrated Security=True;" `
            /p:BlockOnPossibleDataLoss=False `
            /p:DropObjectsNotInSource=True

  deploy-staging:
    runs-on: [self-hosted, windows, sql-deploy]
    needs: test
    if: github.ref == 'refs/heads/main'
    environment: staging
    steps:
      - name: Download Artifacts
        uses: actions/download-artifact@v4
        with:
          name: dacpacs

      - name: Generate Deployment Report
        run: |
          sqlpackage /Action:DeployReport `
            /SourceFile:Database.dacpac `
            /TargetConnectionString:"Server=staging-sql;Database=MyDB;Integrated Security=True;" `
            /OutputPath:deploy-report.xml

      - name: Deploy to Staging (Windows Auth)
        run: |
          sqlpackage /Action:Publish `
            /SourceFile:Database.dacpac `
            /TargetConnectionString:"Server=staging-sql;Database=MyDB;Integrated Security=True;" `
            /p:BlockOnPossibleDataLoss=True `
            /p:BackupDatabaseBeforeChanges=True `
            /p:DropObjectsNotInSource=False

  deploy-production:
    runs-on: [self-hosted, windows, sql-deploy]
    needs: deploy-staging
    environment: production
    steps:
      - name: Download Artifacts
        uses: actions/download-artifact@v4
        with:
          name: dacpacs

      - name: Generate Deployment Report
        run: |
          sqlpackage /Action:DeployReport `
            /SourceFile:Database.dacpac `
            /TargetConnectionString:"Server=prod-sql;Database=MyDB;Integrated Security=True;" `
            /OutputPath:prod-deploy-report.xml

      - name: Manual Approval Required
        uses: trstringer/manual-approval@v1
        with:
          approvers: database-admins,devops-leads
          minimum-approvals: 2
          instructions: "Review prod-deploy-report.xml and approve deployment"

      - name: Deploy to Production (Windows Auth)
        run: |
          sqlpackage /Action:Publish `
            /SourceFile:Database.dacpac `
            /TargetConnectionString:"Server=prod-sql;Database=MyDB;Integrated Security=True;" `
            /p:BlockOnPossibleDataLoss=True `
            /p:BackupDatabaseBeforeChanges=True `
            /p:DropObjectsNotInSource=False `
            /p:DoNotDropObjectTypes=Users;Logins;RoleMembership `
            /DiagnosticsFile:prod-deploy.log

      - name: Upload Deployment Logs
        if: always()
        uses: actions/upload-artifact@v4
        with:
          name: production-deployment-logs
          path: prod-deploy.log

Azure DevOps Pipeline Example (2025)

trigger:
  branches:
    include:
      - main
      - develop

pool:
  vmImage: 'windows-2022'

variables:
  buildConfiguration: 'Release'
  dotnetVersion: '8.0.x'
  sqlPackageVersion: '170.2.70'

stages:
- stage: Build
  jobs:
  - job: BuildDatabase
    steps:
    - task: UseDotNet@2
      displayName: 'Install .NET 8'
      inputs:
        version: $(dotnetVersion)

    - task: DotNetCoreCLI@2
      displayName: 'Build Database Project'
      inputs:
        command: 'build'
        projects: '**/*.sqlproj'
        arguments: '-c $(buildConfiguration)'

    - task: PublishBuildArtifacts@1
      displayName: 'Publish DACPAC'
      inputs:
        PathtoPublish: '$(Build.SourcesDirectory)/bin/$(buildConfiguration)'
        ArtifactName: 'dacpacs'

- stage: Test
  dependsOn: Build
  jobs:
  - job: RunUnitTests
    steps:
    - task: DownloadBuildArtifacts@1
      inputs:
        artifactName: 'dacpacs'

    - task: SqlAzureDacpacDeployment@1
      displayName: 'Deploy to Test Database'
      inputs:
        authenticationType: 'integratedAuth'
        serverName: 'test-sql-server'
        databaseName: 'TestDB'
        dacpacFile: '$(System.ArtifactsDirectory)/dacpacs/Database.dacpac'

    - task: PowerShell@2
      displayName: 'Run tSQLt Tests'
      inputs:
        targetType: 'inline'
        script: |
          $results = Invoke-Sqlcmd -ServerInstance 'test-sql-server' `
                                    -Database 'TestDB' `
                                    -Query "EXEC tSQLt.RunAll"

          $failures = $results | Where-Object { $_.Class -eq 'Failure' }
          if ($failures) {
            throw "Tests failed: $($failures.Count) failures"
          }

- stage: DeployProduction
  dependsOn: Test
  condition: and(succeeded(), eq(variables['Build.SourceBranch'], 'refs/heads/main'))
  jobs:
  - deployment: DeployToProduction
    environment: 'Production'
    strategy:
      runOnce:
        deploy:
          steps:
          - task: SqlAzureDacpacDeployment@1
            displayName: 'Generate Deployment Report'
            inputs:
              deployType: 'DeployReport'
              authenticationType: 'integratedAuth'
              serverName: 'prod-sql-server'
              databaseName: 'ProductionDB'
              dacpacFile: '$(Pipeline.Workspace)/dacpacs/Database.dacpac'
              outputFile: 'deploy-report.xml'

          - task: SqlAzureDacpacDeployment@1
            displayName: 'Deploy to Production'
            inputs:
              authenticationType: 'integratedAuth'
              serverName: 'prod-sql-server'
              databaseName: 'ProductionDB'
              dacpacFile: '$(Pipeline.Workspace)/dacpacs/Database.dacpac'
              additionalArguments: '/p:BlockOnPossibleDataLoss=True /p:BackupDatabaseBeforeChanges=True'

Best Practices Checklist

Source Control

All database objects in source control
.gitignore configured for build outputs
No credentials committed
Test scripts versioned separately
Branching strategy defined (gitflow, trunk-based, etc.)

Testing

tSQLt framework deployed
Unit tests cover critical stored procedures
Tests grouped logically in schemas
Pipeline aborts on test failure
Test results published to dashboard

Security

Windows Authentication used for CI/CD
Service accounts follow principle of least privilege
Secrets stored in Azure Key Vault / GitHub Secrets
No plain text passwords
Audit logging enabled

Deployment

State-based deployment strategy
Deployment reports generated before production
Manual approval gates for production
Backup before changes (production)
BlockOnPossibleDataLoss enabled (production)
DoNotDropObjectTypes configured
Rollback plan documented

Monitoring

Deployment logs captured
Failed deployments trigger alerts
Performance metrics tracked
Schema drift detection automated

ssdt-cicd-best-practices-2025