ssdt-cicd-best-practices-2025

🚨 关键指南

Windows 文件路径要求

强制要求：在 Windows 上始终使用反斜杠表示文件路径

在 Windows 上使用 Edit 或 Write 工具时，必须在文件路径中使用反斜杠（\），而不是正斜杠（/）。

示例：

• ❌ 错误：D:/repos/project/file.tsx
• ✅ 正确：D:\repos\project\file.tsx

适用于：

• Edit 工具的 file_path 参数
• Write 工具的 file_path 参数
• Windows 系统上的所有文件操作

文档指南

除非用户明确要求，否则绝不创建新的文档文件。

• 优先事项：更新现有的 README.md 文件，而不是创建新文档
• 保持仓库整洁：保持仓库根目录整洁——除非用户另有要求，否则只保留 README.md
• 风格：文档应简洁、直接、专业——避免使用 AI 生成式语气
• 用户偏好：只有在用户明确要求提供文档时，才创建额外的 .md 文件

---

SSDT CI/CD 最佳实践 2025

概述

本技能提供有关使用 SSDT、SqlPackage 和现代 DevOps 实践，为 SQL Server 数据库项目实施现代 CI/CD 管道的全面指导。

核心原则（2025 年推荐做法）

1. 基于状态的部署（推荐）

定义：源代码表示数据库的当前状态，而不是迁移脚本。

工作方式：

• 所有数据库对象（表、存储过程、视图、函数）都存储在单独的 .sql 文件中
• SqlPackage 自动生成增量部署脚本
• 声明式方法：“这就是数据库应有的样子”
• SSDT 对比源与目标，并计算差异

优势：

• 更易于维护和理解
• 不存在遗漏迁移脚本的风险
• Git 历史记录展示完整的对象定义
• 简化分支和合并
• 通过重新部署以前的版本进行回滚

实现：

# 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

与基于迁移的方式对比：

• 基于迁移：按顺序排列的脚本（001_CreateTable.sql、002_AddColumn.sql）
• 基于状态：对象定义（Tables/Customer.sql 包含完整的 CREATE TABLE）

2. tSQLt 单元测试（CI/CD 的关键）

为什么选择 tSQLt：

• 开源的 SQL Server 单元测试框架
• 使用 T-SQL 语言编写测试
• 生成机器可读的 XML/JSON 结果
• 与 CI/CD 管道无缝集成

主要功能：

• 自动事务：每个测试都在事务中运行并回滚
• 架构分组：在架构中对相关测试进行分组
• 模拟：伪造表和存储过程，以便进行隔离测试
• 断言：内置断言方法（assertEquals、assertEmpty 等）

失败时中止管道：

# 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 }}"

测试结构：

-- 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', '[email protected]');

    -- 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 集成：

- 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 身份验证，而非 SQL 身份验证

安全最佳实践：优先为 CI/CD Agent 使用 Windows 身份验证（集成安全性）。

为什么选择 Windows 身份验证：

• 无需在连接字符串中存储密码
• 利用现有的 Active Directory 基础设施
• 使用权限最小化的服务帐户
• 通过 Windows 安全日志留下审计记录
• 无需轮换凭据

实现方式：

自托管 Agent（推荐）：

# 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：

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

云 Agent 的替代方案（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

切勿这样做：

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

如果必须使用 SQL 身份验证：

# 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. 对所有内容进行版本控制

纳入版本控制的内容：

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

提交要求：

• 数据库变更必须经过代码审查
• 所有提交都必须附带注释
• 合并前运行自动化测试
• 通过分支策略强制执行命名约定

5. 始终要求部署报告

生产环境部署前：

- 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. 环境晋级策略

标准流程：Dev → QA → Staging → Production

保持部署一致的选项：

# 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

完整 CI/CD 管道（GitHub Actions + Azure DevOps）

GitHub Actions 和 Azure DevOps Pipelines 的完整可用管道 YAML（DACPAC 构建、漂移检测、部署报告、环境晋级、SqlPackage 发布、审批门禁）位于 references/complete-pipelines.md。为 SSDT 项目配置 CI/CD 时，请加载该参考文档。

最佳实践检查清单

源代码管理

• 所有数据库对象都纳入源代码管理
• 已配置 .gitignore 以排除构建输出
• 未提交任何凭据
• 测试脚本单独进行版本管理
• 已定义分支策略（gitflow、主干开发等）

测试

• 已部署 tSQLt 框架
• 单元测试覆盖关键存储过程
• 测试按逻辑归入各架构
• 流水线在测试失败时中止
• 测试结果已发布到仪表板

安全

• CI/CD 使用 Windows Authentication
• 服务帐户遵循最小权限原则
• 机密存储在 Azure Key Vault / GitHub Secrets 中
• 不使用明文密码
• 已启用审计日志记录

部署

• 采用基于状态的部署策略
• 生产环境部署前生成部署报告
• 生产环境设置手动审批门禁
• 更改前进行备份（生产环境）
• 生产环境启用 BlockOnPossibleDataLoss
• 已配置 DoNotDropObjectTypes
• 已记录回滚计划

监控

• 已捕获部署日志
• 部署失败时会触发警报
• 已跟踪性能指标
• 已自动检测架构漂移

资源

• tSQLt 官方网站
• Microsoft.Build.Sql 文档
• SqlPackage 参考
• Azure DevOps SQL 任务
• 面向 SQL 的 GitHub Actions