データ変更の追跡 (SQL Server)

  • [アーティクル]

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

SQL Server では、データベースのデータに対する変更を追跡する変更データ キャプチャおよび変更の追跡という 2 つの機能が用意されています。 これらの機能では、データベース内のユーザー テーブルに対して行われた DML の変更 (挿入操作、更新操作、および削除操作) をアプリケーションで特定できます。 変更データ キャプチャと変更の追跡は、同じデータベースに対して有効にすることができます。特別な配慮は必要ありません。 変更データ キャプチャと変更の追跡をサポートする SQL Server のエディションについては、「 SQL Server 2022 の各エディションとサポートされている機能」を参照してください。

変更データ キャプチャまたは変更の追跡を使用する利点

データベースで変更されたデータをクエリする機能は、一部のアプリケーションの効率を高めるための重要な要件です。 一般に、データ変更を確認するには、アプリケーション開発者がトリガー、timestamp 列、および追加のテーブルを組み合わせて使用することで、カスタムの追跡方法をアプリケーションに実装する必要があります。 通常、このようなアプリケーションを作成するには実装に非常に手間がかかり、スキーマの更新も必要になり、多くの場合、パフォーマンスのオーバーヘッドが増加します。

カスタム ソリューションを開発する代わりに、アプリケーションで変更データ キャプチャまたは変更の追跡を使用してデータベースでの変更を追跡すると、次のような利点があります。

  • 開発時間が短縮されます。 SQL Server で使用可能な機能により、カスタム ソリューションを開発する必要はありません。

  • スキーマの変更は不要です。 削除された行を追跡するため、または変更追跡情報を格納するため (列をユーザー テーブルに追加できない場合) に列の追加、トリガーの追加、またはサイド テーブルの作成を行う必要がありません。

  • 組み込みのクリーンアップ メカニズムがあります。 変更の追跡のクリーンアップは、バックグラウンドで自動的に実行されます。 サイド テーブルに格納されるデータのカスタム クリーンアップは必要ありません。

  • 変更情報を取得するための関数が用意されています。

  • DML 操作のオーバーヘッドが低減します。 同期変更追跡では、オーバーヘッドが常に若干発生します。 しかし、変更の追跡を使用すると、オーバーヘッドを最小限に抑えることができます。 多くの場合、オーバーヘッドは、代わりのソリューション (特にトリガーを使用する必要があるソリューション) を使用する場合よりも少なくなります。

  • コミットされたトランザクションに基づいた変更の追跡 変更の順序は、トランザクションのコミット時間に基づきます。 このため、実行時間の長いトランザクションや重複するトランザクションが存在する場合でも、信頼性が高い結果を取得できます。 timestamp 値を使用するカスタム ソリューションは、このようなシナリオに対処するように設計する必要があります。

  • 構成および管理に使用できる標準的なツールがあります。 SQL Server には、標準の DDL ステートメント、SQL Server Management Studio、カタログ ビュー、およびセキュリティアクセス許可が用意されています。

変更データ キャプチャと変更の追跡の機能の違い

次の表に、変更データ キャプチャと変更の追跡の機能の違いを示します。 変更データ キャプチャの追跡メカニズムでは、非同期キャプチャによりトランザクション ログから変更がキャプチャされ、DML 操作後に変更を利用できます。 変更の追跡の追跡メカニズムでは、同期追跡により DML 操作に即して変更が追跡され、すぐに変更情報を利用できます。

機能 変更データ キャプチャ 変更追跡
追跡される変更
DML の変更 はい はい
追跡される情報
履歴データ はい いいえ
列が変更されたかどうか はい はい
DML 型 はい はい

変更データ キャプチャ

変更データ キャプチャでは、DML の変更が行われたという事実と変更された実際のデータの両方がキャプチャされ、ユーザー テーブルの変更情報の履歴が提供されます。 変更は、非同期プロセスを使用してトランザクション ログを読み取ることによってキャプチャされます。これは、システムへの影響が少ない方法です。

次の図に示すように、ユーザー テーブルに対して行われた変更は、対応する変更テーブルにキャプチャされます。 これらの変更テーブルには、時間の経過に伴う変更の履歴が表示されます。 SQL Server の変更データ キャプチャ関数を使用して、変更データを簡単かつ体系的に利用できます。

Diagram showing the concept of change data capture.

セキュリティ モデル

ここでは、変更データ キャプチャのセキュリティ モデルについて説明します。

構成と管理

データベースで変更データ キャプチャを有効または無効にするには、sys.sp_cdc_enable_db (Transact-SQL) または sys.sp_cdc_disable_db (Transact-SQL) の呼び出し元が固定サーバー ロール sysadmin のメンバーである必要があります。 テーブル レベルで変更データ キャプチャを有効または無効にするには、sys.sp_cdc_enable_table (Transact-SQL) および sys.sp_cdc_disable_table (Transact-SQL) の呼び出し元が sysadmin ロールのメンバーか database db_owner データベース ロールのメンバーである必要があります。

変更データ キャプチャ ジョブの管理をサポートするストアド プロシージャを使用できるのは、サーバー sysadmin ロールのメンバー、および database db_owner ロールのメンバーに制限されます。

変更の列挙とメタデータ クエリ

キャプチャ インスタンスに関連付けられた変更データにアクセスするには、関連付けられたソース テーブルのすべてのキャプチャ対象列に対する選択アクセスがユーザーに許可されている必要があります。 また、キャプチャ インスタンスの作成時にゲーティング ロールが指定されている場合、呼び出し元は、指定されたゲーティング ロールのメンバーである必要もあります。また、変更データ キャプチャ スキーマ（cdc）はゲーティングロールへの選択アクセスが必要です。

メタデータにアクセスするためのその他の一般的な変更データ キャプチャ関数には、public ロールですべてのデータベース ユーザーがアクセスできます。ただし、通常は、返されるメタデータへのアクセスも、基になるソース テーブルに対する選択アクセス、および定義されたすべてのゲーティング ロールのメンバーシップに基づいて制限されます。

変更データ キャプチャが有効になっているソース テーブルに対する DDL 操作

テーブルで変更データ キャプチャが有効になっているときにテーブルに DDL 操作を適用できるのは、固定サーバー ロール sysadminのメンバー、 database role db_ownerのメンバー、または database role db_ddladminのメンバーのみです。 テーブルに対する DDL 操作の実行が明示的に許可されているユーザーがこれらの操作を実行しようとすると、エラー 22914 が返されます。

変更データ キャプチャのデータ型に関する考慮事項

基本的な列の種類はすべて変更データ キャプチャでサポートされています。 次の表では、さまざまな列の型の動作と制限について説明します。

列の種類 変更テーブルで変更をキャプチャする 制限事項
スパース列 はい columnset を使用する場合は変更のキャプチャをサポートしません。
計算列 いいえ 計算列に対する変更は追跡されません。 列は適切な種類の変更テーブルに表示されますが、値は NULL になります。
XML はい 個々の XML 要素に対する変更は追跡されません。
Timestamp はい 変更テーブル内のデータ型はバイナリに変換されます。
BLOB データ型 はい BLOB 列の前の画像は、列自体が変更された場合にのみ保存されます。

変更データ キャプチャとその他の SQL Server 機能

ここでは、次の機能と変更データ キャプチャとの連携について説明します。

  • データベース ミラーリング
  • トランザクション レプリケーション
  • データベースの復元またはアタッチ

データベース ミラーリング

変更データ キャプチャが有効になっているデータベースをミラー化できます。 ミラーでキャプチャとクリーンアップが自動的に行われるようにするには、次の手順を実行します。

  1. ミラーで SQL Server Agent エージェントが実行されていることを確認します。

  2. プリンシパルがミラーにフェールオーバーした後、ミラーでキャプチャ ジョブとクリーンアップ ジョブを作成します。 ジョブを作成するには、ストアド プロシージャ sys.sp_cdc_add_job (Transact-SQL) を使用します。

データベース ミラーリングの詳細については、「データベース ミラーリング (SQL Server)」を参照してください。

トランザクション レプリケーション

変更データ キャプチャとトランザクション レプリケーションは、同じデータベースで共存できます。ただし、両方の機能が有効になっている場合、変更テーブルが異なる方法で作成されます。 変更データ キャプチャとトランザクション レプリケーションでは、トランザクション ログから変更を読み取る際に、常に同じプロシージャ ( sp_replcmds) が使用されます。 変更データ キャプチャのみが有効になっている場合は、SQL Server エージェント ジョブによって sp_replcmds が呼び出されます。 同じデータベースで両方の機能が有効になっている場合は、ログ リーダー エージェントによって sp_replcmds が呼び出されます。 このエージェントは、変更テーブルと distribution データベース テーブルの両方を作成します。 詳細については、「 Replication Log Reader Agent」を参照してください。

AdventureWorks2022 データベースで変更データ キャプチャが有効になっており、2 つのテーブルでキャプチャが有効になっているシナリオについて考えてみます。 変更テーブルを作成するために、キャプチャ ジョブによって sp_replcmds が呼び出されます。 データベースでトランザクション レプリケーションが有効になり、パブリケーションが作成されます。 次に、ログ リーダー エージェントがデータベースに対して作成され、キャプチャ ジョブが削除されます。 ログ リーダー エージェントは、変更テーブルにコミットされた最後のログ シーケンス番号からログのスキャンを続行します。 これにより、変更テーブル内のデータの一貫性が確保されます。 このデータベースでトランザクション レプリケーションが無効になっている場合、ログ リーダー エージェントが削除され、キャプチャ ジョブが再作成されます。

Note

変更データ キャプチャとトランザクション レプリケーションの両方でログ リーダー エージェントを使用している場合、レプリケートされた変更が最初に distribution データベースに書き込まれます。 次に、キャプチャされた変更が変更テーブルに書き込まれます。 両方の操作は同時にコミットされます。 distribution データベースへの書き込みの際に遅延が生じた場合、変更テーブルに変更が表示される前に、それに対応する遅延が発生します。

変更データ キャプチャが有効になっているデータベースの復元またはアタッチ

SQL Server は、データベースが復元またはアタッチされた後に変更データ キャプチャを有効のままにするかどうかを、次のロジックに従って判断します。

  • データベースを同じサーバーに同じデータベース名で復元した場合、変更データ キャプチャは有効のままです。

  • データベースを別のサーバーに復元した場合、既定では変更データ キャプチャが無効になり、関連するメタデータがすべて削除されます。

    変更データ キャプチャを保持するには、データベースを復元する際に KEEP_CDC オプションを使用します。 このオプションの詳細については、「 RESTORE」を参照してください。

  • データベースをデタッチしてから、同じサーバーまたは別のサーバーにアタッチした場合、変更データ キャプチャは有効のままです。

  • KEEP_CDC オプションを使用してデータベースを Standard または Enterprise 以外のエディションにアタッチまたは復元しようとすると、操作がブロックされます。これは変更データ キャプチャを使用するには、SQL Server Standard または Enterprise Edition が必要であるためです。 エラー メッセージ 932 が表示されます。

    SQL Server cannot load database '%.*ls' because change data capture is enabled. The currently installed edition of SQL Server does not support change data capture. Either disable change data capture in the database by using a supported edition of SQL Server, or upgrade the instance to one that supports change data capture.

sys.sp_cdc_disable_db を使用すると、復元またはアタッチされたデータベースから変更データ キャプチャを削除できます。

変更追跡

変更の追跡においては、テーブル内の行が変更されたという事実がキャプチャされますが、変更されたデータはキャプチャされません。 この機能を使用すると、変更された行をアプリケーションで特定し、最新の行データについてはユーザー テーブルから直接取得することができます。 したがって、変更の追跡で確認できる履歴の情報は変更データ キャプチャと比較すると限定されますが、 変更データがキャプチャされないため、履歴情報が必要ではないアプリケーションではストレージ オーバーヘッドがはるかに少なくて済みます。 変更は、同期追跡メカニズムを使用して追跡されます。 これは、DML 操作のオーバーヘッドを最小限に抑えるように設計されています。

次の図は、変更の追跡を使用すると効果的な同期のシナリオを示しています。 このシナリオのアプリケーションでは、テーブルの前回の同期後に変更されたすべてのテーブル行の現在の行データのみが必要です。 同期メカニズムを使用して変更が追跡されるため、アプリケーションで双方向同期を実行して、発生する可能性がある競合を確実に検出できます。

Diagram showing the concept of change tracking.

変更の追跡と Sync Services for ADO.NET

Sync Services for ADO.NET では、データベース間の同期が可能になり、直感的で柔軟性の高い API を使用して、オフラインおよびコラボレーションのシナリオを対象とするアプリケーションを構築できます。 Sync Services for ADO.NET には変更を同期する API が用意されていますが、この API ではサーバーまたはピア データベース内の変更は実際には追跡されません。 カスタム変更追跡システムを作成できますが、一般に複雑さやパフォーマンスのオーバーヘッドが大幅に増加します。 サーバーまたはピア データベースの変更を追跡するには、構成が簡単でパフォーマンスの高い追跡を実現できる SQL Server の変更の追跡を使用することをお勧めします。

変更の追跡と Sync Services for ADO.NET の詳細については、以下のリンクを参照してください。

次のステップ

タスク 記事
変更データ キャプチャの概要を示します。 変更データ キャプチャについて (SQL Server)
データベースまたはテーブルに対して変更データ キャプチャを有効または無効にする方法について説明します。 変更データ キャプチャの有効化と無効化 (SQL Server)
変更データ キャプチャを管理および監視する方法について説明します。 変更データ キャプチャの管理と監視 (SQL Server)
変更データ キャプチャのコンシューマーが使用できる変更データを操作する方法について説明します。 この記事では、LSN の下限と上限の検証、クエリ関数、およびクエリ関数のシナリオについて説明します。 変更データの処理 (SQL Server)
変更の追跡の概要を示します。 変更の追跡について (SQL Server)
データベースまたはテーブルに対して変更の追跡を有効または無効にする方法について説明します。 変更の追跡の有効化と無効化 (SQL Server)
変更の追跡を管理する方法、セキュリティを構成する方法、および変更の追跡を使用する場合のストレージとパフォーマンスへの影響を判断する方法について説明します。 変更の追跡の管理 (SQL Server)
変更の追跡を使用するアプリケーションが、追跡した変更を取得し、その変更を別のデータ ストアに適用して、ソース データベースを更新する方法について説明します。 また、フェールオーバーが発生してデータベースをバックアップから復元する必要がある場合に、変更の追跡が果たす役割についても説明します。 変更の追跡のしくみ (SQL Server)

関連項目