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

適用対象: SQL Server (サポートされているすべてのバージョン) Azure SQL Database Azure SQL Managed Instance

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

変更の追跡は Azure SQL データベースでサポートされています。 変更データ キャプチャは、SQL Server および Azure SQL Managed Instance でのみサポートされています。

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

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

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

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

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

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

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

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

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

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

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

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

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

変更データ キャプチャ

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

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

変更データ キャプチャの概念を示す図。

セキュリティ モデル

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

構成と管理

データベースの変更データ キャプチャを有効または無効にするには、 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 ロールのメンバーであるか、データベース データベース db_owner ロールのメンバーである必要があります。

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

列挙クエリとメタデータ クエリを変更する

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

メタデータにアクセスするためのその他の一般的な変更データ キャプチャ関数には、パブリック ロールを介してすべてのデータベース ユーザーがアクセスできますが、通常、返されるメタデータへのアクセスは、基になるソース テーブルへの SELECT アクセスと、定義されたゲート ロールのメンバーシップを使用して制御されます。

データ キャプチャが有効なソース テーブルを変更するための DDL 操作

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Note

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

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

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

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

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

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

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

  • Standard または Enterprise 以外の任意のKEEP_CDCエディションに対するオプションを使用してデータベースがアタッチまたは復元された場合、変更データ キャプチャには SQL Server Standard または Enterprise エディションが必要であるため、操作はブロックされます。 エラー メッセージ 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 操作のオーバーヘッドを最小限に抑えるように設計されています。

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

変更追跡の概念を示す図。

ADO.NET の変更追跡と同期サービス

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

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

  • 変更の追跡について (SQL Server)

    変更の追跡について説明します。変更の追跡のしくみについて概要を示し、変更の追跡が SQL Server データベース エンジン のその他の機能とどのように連携するかを説明します。

  • Microsoft Sync Framework デベロッパー センター

    Sync Framework および Sync Servicesに関する完全なドキュメントが用意されています。 Sync Servicesのドキュメントのトピック「SQL Server 変更の追跡の使用方法」には、詳細な情報とコードの例があります。

次のステップ

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

こちらもご覧ください