スキーマの誤差を検出して解決する
SQL データベース プロジェクトでは、 Customers テーブルに 12 列が含まれていると表示されます。 運用環境には 13 があります。 だれかが、インシデント発生時に SQL Server Management Studio (SSMS) を通じて直接 LoyaltyTier 列を追加しました。 プロジェクトが存在することを認識していないため、次のデプロイではその列が静かに削除されます。 この種の状況はスキーマ ドリフトと呼ばれ、CI/CD パイプラインが警告なしに運用環境を中断する方法の 1 つです。
スキーマの誤差を理解する
スキーマの誤差は、プロジェクトで定義されているものと実際にライブ データベースに存在するものの間のギャップです。 一般的な原因には、次のようなものがあります。
-
手動変更: クエリ ウィンドウが開き、通常のワークフローの外部で
ALTER TABLEが実行されます。 - 緊急修正プログラム: 運用環境の問題が午前2時にパッチ適用されますが、その修正はプロジェクトに反映されないままです。
- サード パーティ製ツール: バックグラウンドでオブジェクトを作成または変更するエージェントまたは Object-Relational マッピング (ORM) フレームワークを監視します。
危険はドリフトそのものではありません。 次に何が起こるかです。 パイプラインによって .dacpacがデプロイされ、SqlPackage によって差分が計算され、プロジェクトで認識されないすべてのオブジェクトが削除されます。 デプロイが実行される前の誤差を検出することは重要です。
スキーマ比較による誤差の検出
スキーマ比較ツールを使用すると、2 つのデータベース定義を並べて保持できます。 ライブ データベース、SQL データベース プロジェクト、または .dacpac ファイルの任意の組み合わせを比較できます。 ライブ データベースをプロジェクトと比較すると、結果は、アクションの種類 (Create、 Alter、 Delete) ごとに、各オブジェクトに表示されるソース定義とターゲット定義でグループ化されたすべての違いを示します。
Visual Studio または Visual Studio Code で、SQL Server メニューまたはデータベース プロジェクト ビューからスキーマ比較を起動します。 ソースをライブ データベースに、ターゲットをプロジェクトにポイントします。 比較グリッドには、何が異なるか、配置した場合に何が変わるかを示します。
注
方向は元に戻すことができます。 データベースとプロジェクトを比較して誤差を見つけます。 プロジェクトとデータベースを比較し、デプロイが変更される内容をプレビューします。 同じツール、反対の視点。
スキーマ比較オプション
すべての違いが重要なわけではありません。 意味のある内容に焦点を当てるために比較を調整できます。
- 空白文字を無視 して、書式設定のみの違いをスキップします。
- 列の 位置がアプリケーションに関係ない場合は、列の順序を無視します。
- データを 含む列を削除するなどの破壊的操作にフラグを設定するために、データ損失の可能性をブロックします。
比較設定を .scmp ファイルとして保存し、リポジトリにコミットします。 ソース、ターゲット、比較オプション、除外されたオブジェクトの種類が格納され、チーム全体でドリフト チェックが繰り返し可能になります。
データベースからプロジェクトに変更をインポートする
誤差が見つかったら、ライブ データベースに適切なバージョンがあるか、それともプロジェクトなのかを決定する必要があります。 運用環境の修正プログラムが正しい場合は、プロジェクトに取り込んで、信頼できるソースが正確な状態で維持されるようにします。
スキーマ比較を使用して変更を適用する
比較結果で、インポートする特定の相違点を選択して適用します。 Visual Studio と Visual Studio Code のグラフィカル インターフェイスを使用すると、特定の変更を選択的に適用できます。 修正プログラムのインデックスを受け入れますが、監視エージェントの一時テーブルは無視します。たとえば、
SqlPackage 抽出を使用して自動化する
CI/CD シナリオまたはスケジュールされたドリフト チェックの場合は、SqlPackage Extract を使用してライブ スキーマをファイルにプルします。
sqlpackage /Action:Extract /SourceConnectionString:"{connection string}" /TargetFile:MyDatabaseProject /p:ExtractTarget=SchemaObjectType
📝 これにより、スキーマとオブジェクトの種類別に整理されたファイルにデータベース オブジェクトが抽出されます。 Git の下のプロジェクト フォルダーで、抽出後に git status を実行すると、変更されたファイルが正確に表示されます。 それが自動化されたドリフト レポートです。
次の 3 つのコマンドを使用すると、ドリフトカウントが得されます。
rm -rf MyDatabaseProject
sqlpackage /Action:Extract /SourceConnectionString:"{connection string}" /TargetFile:MyDatabaseProject /p:ExtractTarget=SchemaObjectType
git status --porcelain | wc -l
📝 出力は、変更されたファイルの数であり、これにより自動的にドリフトカウントが得られます。
デプロイの変更を適用する前に確認する
前のユニットで、ビルドとデプロイに関する Script と DeployReport のアクションを確認しました。 ドリフト検出コンテキストでは、これらの同じコマンドを使用して、運用環境に対して SqlPackage を実行する前に実行する内容を確認できます。
デプロイ スクリプトを生成する
Scriptアクションは、実際に実行することなく実行される正確な T-SQL を生成します。
dotnet build MyProject.sqlproj
sqlpackage /Action:Script /SourceFile:bin/Debug/MyProject.dacpac /TargetConnectionString:"{connection string}" /DeployScriptPath:Deployment.sql
Deployment.sqlを確認して、デプロイが実行されるすべてのALTER、CREATE、およびDROPを確認します。 運用環境に対して実行する前に、承認のためにスクリプトをパイプライン成果物として格納します。
配置レポートを生成する
DeployReport アクションでは、計画された操作の XML 概要が生成されます。
sqlpackage /Action:DeployReport /SourceFile:bin/Debug/MyProject.dacpac /TargetConnectionString:"{connection string}" /OutputPath:report.xml
レポートには、 Create、 Alter、 Drop、 TableDataMotionなどの操作が一覧表示されます。 パイプライン内の XML を解析して、データ モーション、クラスター化インデックスの再構築、列の削除などの影響の大きいイベントに対してアラートをトリガーします。
<DeploymentReport xmlns="http://schemas.microsoft.com/sqlserver/dac/DeployReport/2012/02">
<Alerts />
<Operations>
<Operation Name="Create">
<Item Value="[dbo].[Products].[IX_Products_CategorySlug]" Type="SqlIndex" />
</Operation>
<Operation Name="Alter">
<Item Value="[dbo].[Customers]" Type="SqlTable" />
</Operation>
</Operations>
</DeploymentReport>
DacpacVerify を使用して .dacpac の整合性を確認する
DacpacVerify では、2 つの .dacpac ファイルが比較され、展開前スクリプト、配置後スクリプト、SQLCMD 変数、データベース参照、プロパティ、データベース オブジェクトなど、それらの間の相違点が報告されます。 この比較は、元の SQL Server Data Tools (SSDT) プロジェクトから SDK スタイルに変換する場合や、パイプラインによって予想される成果物が生成されることを検証する場合に便利です。
.NET グローバル ツールとしてインストールし、2 つの .dacpac ファイルに対して実行します。
dotnet tool install -g microsoft.dacpacverify
dacpacverify before.dacpac after.dacpac
重要なポイント
スキーマの誤差は、ライブ データベースが SQL プロジェクトから逸脱した場合に発生します。多くの場合、修正プログラム、手動による変更、または直接の運用編集が原因です。 スキーマ比較ツールは、データベースとプロジェクトの違いを検出し、プロジェクトにプルするか、データベースにプッシュするかを選択できます。 チームが毎回一貫したオプションを使用するように、スキーマ比較設定を .scmp ファイルに保存します。 スケジュールに従って SqlPackage /Action:Extract を実行し、抽出したプロジェクトをリポジトリと比較することで、ドリフト検出を自動化します。 変更を適用する前に、 SqlPackage /Action:DeployReport を使用して、計画されているすべての CREATE、 ALTER、および DROP をプレビューします。 目標は、ドリフトを完全に防ぐことではなく、早期に検出し、次のデプロイでホットフィックスがロールバックに変わる前に対応することです。 次に、ビルドとデプロイのサイクル全体を自動化する CI/CD パイプラインをビルドします。