変更データ キャプチャについて (SQL Server)

データ変更キャプチャは、SQL Server テーブルに適用される挿入、更新、削除アクティビティを記録します。 これにより、変更の詳細が簡単に使用できるリレーショナル形式になります。 ターゲット環境に変更を適用するために必要な列情報とメタデータは、変更された行に対してキャプチャされ、追跡対象のソース テーブルの列構造を反映する変更テーブルに格納されます。 テーブル値関数は、コンシューマーによる変更データへの体系的なアクセスを可能にするために提供されます。

このテクノロジの対象となるデータ コンシューマーの良い例は、抽出、変換、読み込み (ETL) アプリケーションです。 ETL アプリケーションは、 SQL Server のソース テーブルからデータ ウェアハウスやデータ マートに変更データをインクリメンタルに読み込みます。 データ ウェアハウス内のソース テーブルの表現にはソース テーブルの変更を反映する必要がありますが、ソースのレプリカを更新するエンドツーエンドのテクノロジは適切ではありません。 ここで必要となるのは、対象となる異質なデータ表現に対して適用できるように構成された変更データの確実なストリームです。 SQL Server の変更データ キャプチャはこの技術を提供します。

変更データキャプチャデータフロー

次の図は、変更データ キャプチャの主なデータ フローを示しています。

変更データ キャプチャの変更データのソースは SQL Server トランザクション ログです。 追跡対象のソース テーブルに対して挿入、更新、削除の各操作が適用されると、それらの変更を記述するエントリがこのログに追加されます。 このログは、キャプチャ プロセスへの入力として機能します。 これにより、ログが読み取られ、追跡対象テーブルの関連する変更テーブルに変更に関する情報が追加されます。 用意されている関数を使用すると、指定した範囲にこの変更テーブルに追加された変更を列挙できます。この情報は、フィルター処理された結果セットの形式で返されます。 通常は、このフィルター処理された結果セットを使用して、アプリケーション プロセスによって外部環境のソースの表現が更新されます。

変更データ キャプチャとキャプチャ インスタンスについて

データベース内の個々のテーブルの変更を追跡するには、まずそのデータベースで変更データ キャプチャを明示的に有効にする必要があります。 これを行うには、ストアド プロシージャ sys.sp_cdc_enable_dbを使用します。 データベースを有効にすると、ストアド プロシージャ sys.sp_cdc_enable_tableを使用して、ソース テーブルを追跡テーブルとして識別できます。 テーブルに対して変更データ キャプチャを有効にすると、関連付けられたキャプチャ インスタンスが作成されます。これにより、ソース テーブルの変更データの伝播がサポートされます。 キャプチャ インスタンスは、1 つの変更テーブルと、最大 2 つのクエリ関数で構成されます。 キャプチャ インスタンスの構成の詳細を記述するメタデータは、変更データ キャプチャ メタデータ テーブル cdc.change_tables、 cdc.index_columns、および cdc.captured_columnsに保持されます。 この情報は、ストアド プロシージャ sys.sp_cdc_help_change_data_captureを使用して取得できます。

キャプチャ インスタンスに関連付けられているオブジェクトはすべて、有効にされたデータベースの変更データ キャプチャ スキーマに作成されます。 キャプチャ インスタンス名の要件は、有効なオブジェクト名であることと、データベース キャプチャ インスタンス全体で一意であることです。 既定では、名前はソース テーブル <schema name_table name> です。 関連付けられている変更テーブルの名前は、キャプチャ インスタンス名の末尾に _CT を付けた名前になります。 すべての変更のクエリに使用される関数は、キャプチャ インスタンス名の前に fn_cdc_get_all_changes_ によって名前が付けられます。 キャプチャ インスタンスが net changesをサポートするように構成されている場合、 net_changes クエリ関数も作成され、キャプチャ インスタンス名の前 にfn_cdc_get_net_changes_ によって名前が付けられます。

テーブルの変更

変更データ キャプチャの変更テーブルの最初の 5 つの列は、メタデータ列です。 これらは、記録された変更に関係する追加情報を提供します。 残りの列には、識別されたソース テーブルのキャプチャ対象列の名前 (および通常は型) が反映されます。 これらの列は、ソース テーブルから収集されたキャプチャ対象列のデータを保持します。

ソース テーブルに適用された挿入または削除の各操作は、変更テーブル内の 1 つの行として表されます。 挿入操作の結果となる行のデータ列には挿入後の列の値が含まれ、 削除操作の結果となる行のデータ列には削除前の列の値が含まれます。 更新操作では、更新の前に列の値を識別する 1 つの行エントリと、更新後に列の値を識別する 2 番目の行エントリが必要です。

変更テーブルの各行には、変更アクティビティの解釈を可能にする追加のメタデータも含まれています。 __$start_lsn 列は、変更に割り当てられたコミット ログ シーケンス番号 (LSN) を識別します。 コミット LSN では、同じトランザクション内でコミットされた変更が識別されるだけでなく、それらのトランザクションが順序付けられます。 $seqval 列は、同じトランザクション内で発生したさらに多くの変更を順序付けるために使用できる列です。 __$operation 列は、変更に関連付けられている操作を記録します (1 = 削除、2 = 挿入、3 = 更新 (前イメージ)、4 = 更新 (後イメージ))。 $update_mask 列は、キャプチャ対象列ごとに 1 つのビットを定義する可変ビット マスクです。 挿入エントリと削除エントリの場合、更新マスクには常にすべてのビットが設定されます。 ただし、更新行には、変更された列に対応するビットのみが設定されます。

データベースのデータ キャプチャの有効期間を変更する

データベースの変更データ キャプチャの有効期間とは、キャプチャ インスタンスが変更データを利用できる期間です。 この有効期間は、データベース テーブルに対して最初のキャプチャ インスタンスが作成されたときに始まり、現在まで続きます。

変更テーブルに格納されるデータは、定期的かつ体系的にクリーンアップしないと、増大して管理しきれなくなります。 このため、変更データ キャプチャのクリーンアップ プロセスにより、保有期間に基づくクリーンアップ ポリシーが適用されます。 このプロセスでは、まず、時間制限を満たすように有効期間の下端が移動されます。 次に、有効期限が切れた変更テーブル エントリが削除されます。 既定では、3 日間のデータが保持されます。

最後に、キャプチャ プロセスが変更データの新しいバッチをコミットすると、変更テーブル エントリを持つトランザクションごとに新しいエントリが cdc.lsn_time_mapping に追加されます。 このマッピング テーブルでは、コミット ログ シーケンス番号 (LSN) とトランザクションのコミット時間の両方 (columns start_lsn と tran_end_time) が保持されます。 cdc.lsn_time_mappingで見つかった最大 LSN 値は、データベースの有効性ウィンドウの高い基準値を表します。 それに対応するコミット時刻は、保持に基づいたクリーンアップが新しい低水準点を計算する基準として使用されます。

キャプチャ プロセスではトランザクション ログから変更データが抽出されるため、変更がソース テーブルにコミットされてから、関連付けられている変更テーブル内に変更が表示されるまでの間に、組み込みの待機時間が発生します。 通常、この待機時間は小さくなりますが、キャプチャ プロセスが関連するログ エントリを処理するまで変更データを使用できない点に注意することが重要です。

キャプチャ インスタンスのデータ キャプチャの有効期間を変更する

データベースの有効期間と個々のキャプチャ インスタンスの有効期間が一致するのは一般的ですが、これは常に当てはまるとは限りません。 キャプチャ インスタンスの有効期間は、キャプチャ プロセスがそのキャプチャ インスタンスを認識して、関連する変更のログをその変更テーブルに記録し始めたときに始まります。 その結果、キャプチャ インスタンスが異なる時間に作成された場合、最初は各インスタンスに異なる低エンドポイントが設定されます。 sys.sp_cdc_help_change_data_captureによって返される結果セットの start_lsn 列には、定義済みの各キャプチャ インスタンスの現在の低エンドポイントが表示されます。 クリーンアップ プロセスによって変更テーブルのエントリがクリーンアップされると、すべてのキャプチャ インスタンスの start_lsn 値が、使用可能な変更データの新しい下限を反映して調整されます。 調整されるのは、その時点で start_lsn 値が新しい下限より小さいキャプチャ インスタンスだけです。 通常は、新しいキャプチャ インスタンスが作成されなければ、時間が経つにつれて、すべてのインスタンスの有効期間がデータベースの有効期間と一致するようになります。

有効期間は変更データのコンシューマーにとって重要です。これは、要求の抽出範囲が、キャプチャ インスタンスの現在の変更データ キャプチャの有効期間によって完全にカバーされている必要があるからです。 抽出範囲の下端が有効期間の下端より左にある場合は、積極的なクリーンアップによって変更データが失われる可能性があります。 抽出間隔の高いエンドポイントが有効期間の高いエンドポイントの右側にある場合、キャプチャ プロセスは抽出間隔で表される期間を通してまだ処理されておらず、変更データも欠落している可能性があります。

sys.fn_cdc_get_min_lsn関数はキャプチャ インスタンスの現在の最小 LSN を取得するために使用され、sys.fn_cdc_get_max_lsnは現在の最大 LSN 値を取得するために使用されます。 変更データのクエリを実行するときに、指定された LSN 範囲がこれら 2 つの LSN 値内にない場合、変更データ キャプチャクエリ関数は失敗します。

ソース テーブルに対する変更の処理

追跡対象のソース テーブルの列の変更に対応することは、ダウンストリーム コンシューマーにとって困難な問題です。 ソース テーブルで変更データ キャプチャを有効にしても、このような DDL の変更が発生するのを防ぐわけではありませんが、変更データ キャプチャは、基になるソース テーブルの列構造が変更されても、API を通じて返される配信された結果セットを変更せずに保持できるようにすることで、コンシューマーへの影響を軽減するのに役立ちます。 この固定された列構造は、定義済みのクエリ関数がアクセスする基になる変更テーブルにも反映されます。

固定列構造変更テーブルに対応するために、変更テーブルの設定を担当するキャプチャ プロセスでは、ソース テーブルで変更データ キャプチャが有効になっているときにキャプチャ用に識別されない新しい列は無視されます。 追跡対象の列が削除されると、後続の変更エントリの列に null 値が指定されます。 ただし、既存の列のデータ型が変更された場合、変更は変更テーブルに反映され、キャプチャ メカニズムによって追跡対象の列にデータ損失が発生しないようにします。 また、追跡されているテーブルの列構造の変更が検出されると、その変更が cdc.ddl_history テーブルに書き込まれます。 ダウンストリーム アプリケーションで行う必要がある可能性がある調整の警告を受け取るコンシューマーは、ストアド プロシージャ sys.sp_cdc_get_ddl_historyを使用します。

通常、DDL の変更が関連付けられているソース テーブルに適用されると、現在のキャプチャ インスタンスは図形を保持し続けます。 ただし、新しい列構造を反映するテーブルの 2 つ目のキャプチャ インスタンスを作成することはできます。 これにより、キャプチャ プロセスは、同じソース テーブルを 2 つの異なる列構造を持つ 2 つの異なる変更テーブルに変更を加えます。 その結果、一方の変更テーブルで現在実行中のプログラムに引き続きデータを提供しながら、もう一方の変更テーブルを開発環境で使用して新しい列データの組み込みに取り組むことができます。 このようにキャプチャ メカニズムで両方の変更テーブルに並行してデータを書き込むことができれば、一方の変更テーブルからもう一方の変更テーブルに、変更データを失うことなく移行できるようになります。 これは、2 つの変更データ キャプチャのタイムラインが重なるたびに発生する可能性があります。 遷移が行われると、古いキャプチャ インスタンスを削除できます。

キャプチャジョブとトランザクション レプリケーションログリーダーの関係

変更データ キャプチャ プロセスのロジックは、ストアド プロシージャ sp_replcmdsに埋め込まれています。これは、 sqlservr.exe の一部として構築された内部サーバー関数であり、トランザクション レプリケーションによってトランザクション ログから変更を収集するためにも使用されます。 データベースに対して変更データ キャプチャのみを有効にすると、sp_replcmdsを呼び出すための手段として変更データ キャプチャ SQL Server エージェント キャプチャ ジョブを作成します。 レプリケーションも存在する場合、トランザクション ログ リーダーのみを使用して、両方のコンシューマーの変更データのニーズを満たすことができます。 これにより、同じデータベースでレプリケーションと変更データ キャプチャの両方が有効になっている場合にログの競合を大幅に削減できます。

変更データをキャプチャするためのこれら 2 つの操作モード間の切り替えは、変更データ キャプチャが有効なデータベースのレプリケーション状態に変更が発生するたびに自動的に発生します。

キャプチャ プロセスの主なタスクは、ログをスキャンし、列データとトランザクション関連情報を変更データ キャプチャ変更テーブルに書き込みます。 キャプチャ プロセスでは、データを書き込むすべての変更データ キャプチャ変更テーブルでトランザクション的に一貫した境界を確保するために、各スキャン サイクルで独自のトランザクションを開いてコミットします。 新たに変更データ キャプチャが有効にされたテーブルがあるとそれが検出されて、ログの変更エントリをアクティブに監視するテーブルのセットにそのテーブルが自動的に追加されます。 同様に、変更データ キャプチャが無効にされた場合にもそれが検出されて、変更データをアクティブに監視するテーブルのセットからそのソース テーブルが削除されます。 ログのセクションの処理が完了すると、サーバー ログ切り捨てロジックに情報が送られて、切り捨ての対象となるログ エントリが識別されます。

キャプチャ プロセスは、追跡対象のテーブルに対する DDL の変更の履歴を保持するためにも使用されます。 変更データ キャプチャが有効になっているデータベースまたはテーブルが削除されたり、変更データ キャプチャが有効になっているテーブルの列が追加、変更、または削除されたりするたびに、変更データ キャプチャに関連付けられている DDL ステートメントのエントリがデータベース トランザクション ログに作成されます。 これらのログ エントリがキャプチャ プロセスによって処理されると、関連する DDL イベントが cdc.ddl_history テーブルに書き込まれます。 ストアド プロシージャ sys.sp_cdc_get_ddl_historyを使用して、追跡対象テーブルに影響する DDL イベントに関する情報を取得できます。

データ キャプチャ エージェント ジョブの変更

変更データ キャプチャが有効になっているデータベースには、通常、2 つの SQL Server エージェント ジョブが関連付けられています。1 つはデータベース変更テーブルへの書き込みに使用されるジョブ、もう 1 つは変更テーブルのクリーンアップを行うジョブです。 どちらのジョブも、Transact-SQL コマンドを実行する 1 つの手順で構成されています。 呼び出される Transact-SQL コマンドは、ジョブのロジックを実装する変更データ キャプチャの定義済みストアド プロシージャです。 これらのジョブは、データベースの最初のテーブルの変更データ キャプチャを有効にしたときに作成されます。 クリーンアップ ジョブは常に作成されます。 キャプチャ ジョブは、データベースに定義済みのトランザクション パブリケーションがない場合にのみ作成されます。 キャプチャ ジョブは、データベースに対して変更データ キャプチャとトランザクション レプリケーションの両方が有効になっている場合にも作成され、トランザクション ログ リーダー ジョブは、データベースでパブリケーションが定義されなくなったため削除されます。

キャプチャ ジョブとクリーンアップ ジョブはどちらも既定のパラメーターを使用して作成されます。 キャプチャ ジョブはすぐに開始され、 継続的に実行されます。各スキャン サイクルで最大 1000 のトランザクションが処理されます。サイクル間の待ち時間は 5 秒です。 クリーンアップ ジョブは毎日午前 2 時に実行されます。変更テーブルのエントリは 4320 分 (3 日間) 保持されます。1 つの DELETE ステートメントで最大 5000 のエントリを削除できます。

変更データ キャプチャのエージェント ジョブは、データベースで変更データ キャプチャが無効にされると削除されます。 また、変更データ キャプチャとトランザクション レプリケーションの両方が有効になっている場合に、データベースに最初のパブリケーションが追加されたときに削除されることもあります。

内部的には、変更データ キャプチャ エージェント ジョブは、それぞれ ストアド プロシージャ sys.sp_cdc_add_job と sys.sp_cdc_drop_jobを使用して作成および削除されます。 これらのストアド プロシージャは、管理者がこれらのジョブの作成および削除を制御できるように公開されています。

変更データ キャプチャのエージェント ジョブについて、既定の構成を管理者が明示的に制御することはできませんが、 ストアド プロシージャ sys.sp_cdc_change_job は、既定の構成パラメーターを変更できるように用意されています。 さらに、ストアド プロシージャ sys.sp_cdc_help_jobs では、現在の構成パラメーターを表示できます。 キャプチャ ジョブとクリーンアップ ジョブの構成パラメーターは、どちらも起動時に msdb.dbo.cdc_jobs テーブルから抽出されます。 sys.sp_cdc_change_jobを使用してこれらの値に加えられた変更は、ジョブが停止して再起動されるまで有効になりません。

変更データ キャプチャ エージェント ジョブを開始および停止できるようにするために、 sys.sp_cdc_start_job と sys.sp_cdc_stop_jobの 2 つのストアド プロシージャが追加されています。

この 2 つの SQL Server エージェント ジョブはどちらも、変更データ キャプチャ環境の基本的なニーズに対応できるように十分な柔軟性と構成可能性を備えていますが、 コア機能を提供する基になるストアド プロシージャが公開されているため、さらなるカスタマイズも可能です。

データベース エンジン サービスまたは SQL Server エージェント サービスが NETWORK SERVICE アカウントで実行されている場合、変更データ キャプチャが正しく機能しません。 この結果、エラー 22832 が発生します。

こちらもご覧ください

データ変更の追跡 (SQL Server)
変更データ キャプチャの有効化と無効化 (SQL Server)
変更データの管理 (SQL Server)
変更データ キャプチャの管理と監視 (SQL Server)