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. SQL 認証より Windows 認証を優先

セキュリティのベストプラクティス：CI/CD エージェントでは Windows 認証（統合セキュリティ）を優先してください。

Windows 認証を使う理由：

• 接続文字列にパスワードを保存する必要がありません
• 既存の Active Directory インフラストラクチャを活用できます
• 最小限のアクセス許可を持つサービスアカウントを使用できます
• Windows セキュリティログによる監査証跡が得られます
• 認証情報をローテーションする必要がありません

実装：

セルフホストエージェント（推奨）：

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

クラウドエージェント向けの代替手段（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. 環境の昇格戦略

標準的なフロー：開発 → QA → ステージング → 本番

一貫性のあるデプロイ方法：

# 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 認証を使用する
• サービスアカウントに最小権限の原則を適用する
• シークレットを Azure Key Vault / GitHub Secrets に保存する
• 平文のパスワードを使用しない
• 監査ログを有効にする

デプロイ

• 状態ベースのデプロイ戦略を採用する
• 本番環境へのデプロイ前にデプロイレポートを生成する
• 本番環境に手動承認ゲートを設ける
• 変更前にバックアップを取得する（本番環境）
• BlockOnPossibleDataLoss を有効にする（本番環境）
• DoNotDropObjectTypes を設定する
• ロールバック計画を文書化する

監視

• デプロイログを記録する
• デプロイ失敗時にアラートを発報する
• パフォーマンス指標を追跡する
• スキーマドリフトの検出を自動化する

リソース

• tSQLt 公式サイト
• Microsoft.Build.Sql ドキュメント
• SqlPackage リファレンス
• Azure DevOps SQL タスク
• SQL 用 GitHub Actions