SQL Server 2014 には、データベース内のデータの変更を追跡する 2 つの機能 ( 変更データ キャプチャ と 変更の追跡) が用意されています。 これらの機能により、アプリケーションはデータベース内のユーザー テーブルに対して行われた DML の変更 (挿入、更新、および削除の操作) を特定できます。 変更データ キャプチャと変更の追跡は、同じデータベースで有効にすることができます。特別な考慮事項は必要ありません。 変更データのキャプチャと変更の追跡をサポートする SQL Server のエディションについては、「 SQL Server 2014 の各エディションでサポートされる機能」を参照してください。
変更データ キャプチャまたは変更追跡を使用する利点
データベースで変更されたデータをクエリする機能は、一部のアプリケーションの効率を高めるための重要な要件です。 一般に、データ変更を確認するには、アプリケーション開発者がトリガー、timestamp 列、および追加のテーブルを組み合わせて使用することで、カスタムの追跡方法をアプリケーションに実装する必要があります。 通常、このようなアプリケーションを作成するには実装に非常に手間がかかり、スキーマの更新も必要になり、多くの場合、パフォーマンスのオーバーヘッドが増加します。
カスタム ソリューションを開発する代わりに、アプリケーションで変更データ キャプチャまたは変更追跡を使用してデータベースの変更を追跡すると、次の利点があります。
開発時間が短縮されます。 SQL Server 2014 では機能を利用できるため、カスタム ソリューションを開発する必要はありません。
スキーマの変更は必要ありません。 列を追加したり、トリガーを追加したり、削除された行を追跡したり、列をユーザー テーブルに追加できない場合に変更追跡情報を格納したりするサイド テーブルを作成する必要はありません。
組み込みのクリーンアップ メカニズムがあります。 変更追跡のクリーンアップは、バックグラウンドで自動的に実行されます。 サイド テーブルに格納されているデータのカスタム クリーンアップは必要ありません。
変更情報を取得するための関数が用意されています。
DML 操作のオーバーヘッドが少ない。 同期変更の追跡には、常にいくつかのオーバーヘッドが発生します。 ただし、変更の追跡を使用すると、オーバーヘッドを最小限に抑えることができます。 オーバーヘッドは、多くの場合、代替ソリューション 、特に使用トリガーを必要とするソリューションを使用する場合よりも小さくなります。
変更の追跡は、コミットされたトランザクションに基づいています。 変更の順序は、トランザクションのコミット時間に基づいています。 これにより、実行時間の長いトランザクションと重複するトランザクションがある場合に、信頼性の高い結果を取得できます。
timestamp値を使用するカスタム ソリューションは、これらのシナリオを処理するように特別に設計する必要があります。構成と管理に使用できる標準ツールが用意されています。 SQL Server 2014 には、標準の 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 ロールのメンバーであるか、データベース database db_owner ロールのメンバーである必要があります。
変更データ キャプチャ ジョブの管理をサポートするためのストアド プロシージャの使用は、サーバー sysadmin ロールのメンバーと database db_owner ロールのメンバーに制限されます。
列挙クエリとメタデータ クエリの変更
キャプチャ インスタンスに関連付けられている変更データにアクセスするには、関連付けられているソース テーブルのすべてのキャプチャ列への選択アクセス権をユーザーに付与する必要があります。 さらに、キャプチャ インスタンスの作成時にゲーティング ロールを指定する場合、呼び出し元も指定されたゲーティング ロールのメンバーである必要があります。 メタデータにアクセスするためのその他の一般的な変更データ キャプチャ関数には、パブリック ロールを介してすべてのデータベース ユーザーがアクセスできますが、通常、返されるメタデータへのアクセスは、基になるソース テーブルへの選択アクセスと、定義されたゲーティング ロールのメンバーシップを使用して制限されます。
データ キャプチャが有効なソース テーブルを変更するための DDL 操作
テーブルで変更データ キャプチャが有効になっている場合、DDL 操作は、固定サーバー ロール sysadminのメンバー、 database role db_ownerのメンバー、または database role db_ddladminのメンバーによってのみテーブルに適用できます。 テーブルに対して DDL 操作を実行する明示的な許可を持つユーザーは、これらの操作を試すとエラー 22914 を受け取ります。
変更データ キャプチャのデータ型に関する考慮事項
すべての基本列の種類は、変更データ キャプチャによってサポートされます。 次の表に、複数の列の種類の動作と制限事項を示します。
| 列の種類 | 変更テーブルでキャプチャされた変更 | 制限事項 |
|---|---|---|
| スパース列 | イエス | 列セットを使用する場合の変更のキャプチャはサポートされません。 |
| 計算列 | いいえ | 計算列への変更は追跡されません。 この列は、適切な型の変更テーブルに表示されますが、値は NULL になります。 |
| XML | イエス | 個々の XML 要素への変更は追跡されません。 |
| タイムスタンプ | イエス | 変更テーブルのデータ型はバイナリに変換されます。 |
| BLOB データ型 | イエス | BLOB 列の前のイメージは、列自体が変更された場合にのみ格納されます。 |
変更データ キャプチャとその他の SQL Server 機能
このセクションでは、次の機能が変更データ キャプチャと対話する方法について説明します。
データベース ミラーリング
トランザクション レプリケーション
データベースの復元またはアタッチ
データベース ミラーリング
変更データ キャプチャが有効になっているデータベースをミラー化できます。 キャプチャとクリーンアップがミラーで自動的に行われるようにするには、次の手順に従います。
SQL Server エージェントがミラーで実行されていることを確認します。
プリンシパルがミラーにフェールオーバーした後、ミラーにキャプチャ ジョブとクリーンアップ ジョブを作成します。 ジョブを作成するには、ストアド プロシージャ sys.sp_cdc_add_job (Transact-SQL) を使用します。
データベース ミラーリングの詳細については、「 データベース ミラーリング (SQL Server)」を参照してください。
トランザクション レプリケーション
変更データ キャプチャとトランザクション レプリケーションは同じデータベースに共存できますが、両方の機能が有効になっている場合、変更テーブルの作成は異なる方法で処理されます。 変更データ キャプチャとトランザクション レプリケーションでは、常に同じプロシージャ (sp_replcmds) を使用して、トランザクション ログから変更を読み取ります。 変更データ キャプチャが単独で有効になっている場合、SQL Server エージェント ジョブは sp_replcmdsを呼び出します。 同じデータベースで両方の機能が有効になっている場合、ログ リーダー エージェントは sp_replcmdsを呼び出します。 このエージェントは、変更テーブルとディストリビューション データベース テーブルの両方を設定します。 詳細については、「 レプリケーション ログ リーダー エージェント」を参照してください。
AdventureWorks2012 データベースで変更データ キャプチャが有効になっており、2 つのテーブルでキャプチャが有効になっているシナリオを考えてみましょう。 変更テーブルを設定するために、キャプチャ ジョブは sp_replcmdsを呼び出します。 トランザクション レプリケーションに対してデータベースが有効になり、パブリケーションが作成されます。 これで、データベースのログ リーダー エージェントが作成され、キャプチャ ジョブが削除されます。 ログ リーダー エージェントは、変更テーブルにコミットされた最後のログ シーケンス番号からログをスキャンし続けます。 これにより、変更テーブルのデータの一貫性が確保されます。 このデータベースでトランザクション レプリケーションが無効になっている場合は、ログ リーダー エージェントが削除され、キャプチャ ジョブが再作成されます。
注
変更データ キャプチャとトランザクション レプリケーションの両方にログ リーダー エージェントを使用する場合、レプリケートされた変更は最初にディストリビューション データベースに書き込まれます。 その後、キャプチャされた変更が変更テーブルに書き込まれます。 どちらの操作も一緒にコミットされます。 ディストリビューション データベースへの書き込みに待機時間がある場合は、変更テーブルに変更が表示されるまでに、対応する待機時間が発生します。
変更データ キャプチャが有効になっているデータベースの復元またはアタッチ
SQL Server では、次のロジックを使用して、データベースの復元またはアタッチ後も変更データ キャプチャが有効なままかどうかを判断します。
データベースが同じデータベース名の同じサーバーに復元された場合、変更データ キャプチャは有効なままです。
データベースが別のサーバーに復元された場合、既定では変更データ キャプチャは無効になり、関連するすべてのメタデータが削除されます。
変更データ キャプチャを保持するには、データベースの復元時に
KEEP_CDCオプションを使用します。 このオプションの詳細については、 RESTORE を参照してください。データベースがデタッチされ、同じサーバーまたは別のサーバーに接続されている場合、変更データ キャプチャは有効なままです。
KEEP_CDCオプションを使用してデータベースが Enterprise 以外のエディションにアタッチまたは復元された場合、変更データ キャプチャには SQL Server 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 の変更の追跡と同期サービス
ADO.NET の同期サービスを使用すると、データベース間の同期が可能になり、オフラインとコラボレーションのシナリオを対象とするアプリケーションを構築できる直感的で柔軟な API が提供されます。 ADO.NET の同期サービスは、変更を同期するための API を提供しますが、実際にはサーバーまたはピア データベースの変更を追跡しません。 カスタムの変更追跡システムを作成できますが、通常は複雑さとパフォーマンスのオーバーヘッドが大幅に発生します。 サーバーまたはピア データベースの変更を追跡するには、SQL Server 2014 で変更の追跡を使用することをお勧めします。構成が簡単で、高パフォーマンスの追跡が提供されるためです。
ADO.NET の変更の追跡と同期サービスの詳細については、次のリンクを使用してください。
-
変更の追跡について説明し、変更の追跡のしくみの概要と、変更の追跡が他の SQL Server データベース エンジン機能とどのように対話するかを説明します。
Microsoft Sync Framework デベロッパー センター
Sync Framework と Sync Services の完全なドキュメントを提供します。 Sync Services のドキュメントの「方法: SQL Server Change Tracking を使用する」のトピックには、詳細情報とコード例が含まれています。
関連タスク (必須)
| タスク | トピック |
| 変更データ キャプチャの概要について説明します。 | 変更データ キャプチャについて (SQL Server) |
| データベースまたはテーブルの変更データ キャプチャを有効または無効にする方法について説明します。 | 変更データ キャプチャの有効化と無効化 (SQL Server) |
| 変更データ キャプチャを管理および監視する方法について説明します。 | 変更データ キャプチャの管理と監視 (SQL Server) |
| 変更データ キャプチャ コンシューマーが利用できる変更データの扱い方について説明します。 このトピックでは、LSN 境界、クエリ関数、およびクエリ関数のシナリオの検証について説明します。 | 変更データの管理 (SQL Server) |
| 変更の追跡の概要について説明します。 | 変更の追跡について (SQL Server) |
| データベースまたはテーブルで変更の追跡を有効または無効にする方法について説明します。 | 変更履歴の有効化と無効化 (SQL Server) |
| 変更の追跡を管理し、セキュリティを構成し、変更の追跡を使用するときのストレージとパフォーマンスへの影響を判断する方法について説明します。 | 変更の追跡の管理 (SQL Server) |
| 変更の追跡を使用するアプリケーションが変更履歴を取得し、これらの変更を別のデータ ストアに適用して、ソース データベースを更新する方法について説明します。 このトピックでは、フェールオーバーが発生し、データベースをバックアップから復元する必要がある場合に、変更の追跡が果たす役割についても説明します。 | 変更の追跡のしくみ (SQL Server) |
こちらもご覧ください
変更データ キャプチャ関数 (Transact-SQL)
変更履歴追跡関数 (Transact-SQL)
Change Data Capture 格納プロシージャ (Transact-SQL)
データ キャプチャ テーブルの変更 (Transact-SQL)
変更データ キャプチャ関連の動的管理ビュー (Transact-SQL)