🚨 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 }
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!
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 }}"
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 Resources tSQLt Official Site Microsoft.Build.Sql Documentation SqlPackage Reference Azure DevOps SQL Tasks GitHub Actions for SQL

ssdt-cicd-best-practices-2025

安装