変更データは、テーブル値関数 (TVF) を介して変更データ キャプチャ コンシューマーに提供されます。 これらの関数のすべてのクエリでは、返される結果セットの開発時に考慮する対象となるログ シーケンス番号 (LSN) の範囲を定義するために、2 つのパラメーターが必要です。 間隔をバインドした上位 LSN 値と下位 LSN 値の両方が、間隔内に含まれていると見なされます。
TVF のクエリに使用する適切な LSN 値を決定するのに役立ついくつかの関数が用意されています。 この関数 sys.fn_cdc_get_min_lsn は、キャプチャ インスタンスの有効期間に関連付けられている最小の LSN を返します。 有効期間は、変更データがキャプチャ インスタンスで現在使用できる時間間隔です。 この関数 sys.fn_cdc_get_max_lsn は、有効期間内で最大の LSN を返します。 sys.fn_cdc_map_time_to_lsn関数とsys.fn_cdc_map_lsn_to_timeは、従来のタイムラインに LSN 値を配置するのに役立ちます。 変更データ キャプチャでは閉じたクエリ間隔が使用されるため、連続するクエリ ウィンドウで変更が重複しないように、シーケンスで次の LSN 値を生成することが必要な場合があります。 sys.fn_cdc_increment_lsn関数とsys.fn_cdc_decrement_lsn関数は、LSN 値への増分調整が必要な場合に便利です。
LSN 境界の検証
使用する前に、TVF クエリで使用される LSN 境界を検証することをお勧めします。 有効期間外の Null エンドポイントまたはその他のエンドポイントがあるキャプチャ インスタンスでは、変更データキャプチャ TVF によってエラーが返されます。
たとえば、クエリ間隔の定義に使用されるパラメーターが無効であるか、範囲外の場合、または行フィルター オプションが無効な場合、すべての変更に対してクエリに対して次のエラーが返されます。
Msg 313, Level 16, State 3, Line 1
An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_all_changes_ ...
net changes クエリに対して返される対応するエラーは次のとおりです。
Msg 313, Level 16, State 3, Line 1
An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_net_changes_ ...
注
メッセージ 313 のメッセージは誤解を招き、失敗の実際の原因を伝えていないことが認識されます。 この厄介な使用は、TVF 内から明示的なエラーを発生できないことに起因します。 それでも、不正確であっても認識可能なエラーを返すことは、単に空の結果を返すよりも望ましいと見なされました。 空の結果セットは、変更を返さない有効なクエリと区別できません。
次に示すように、すべての変更に対してクエリを実行すると、承認エラーによってエラーが返されます。
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'fn_cdc_get_all_changes_...', database 'MyDB', schema 'cdc'.
ネット変更のクエリを実行する場合も同じことが当てはまります。
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object fn_cdc_get_net_changes_...', database 'MyDB', schema 'cdc'.
これらの既知の TVF エラーをインターセプトし、エラーに関するより意味のある情報を返す方法のデモについては、TRY CATCH を使用して Net Changes を列挙するテンプレートを参照してください。
注
SQL Server Management Studio で変更データ キャプチャ テンプレートを見つけるには、[ 表示 ] メニューの [テンプレート エクスプローラー] をクリックし、[ SQL Server テンプレート] を 展開して、[ データ キャプチャの変更 ] フォルダーを展開します。
クエリ関数
追跡対象のソース テーブルの特性とキャプチャ インスタンスの構成方法に応じて、変更データを照会するための 1 つまたは 2 つの TVF が生成されます。
関数cdc.fn_cdc_get_all_changes_<capture_instance>は、指定された間隔で発生したすべての変更を返します。 この関数は常に生成されます。 エントリは常に、最初に変更のトランザクション コミット LSN によって並べ替えられた後、トランザクション内の変更をシーケンスする値によって返されます。 選択した行フィルター オプションに応じて、更新時に最終行が返されるか (行フィルター オプション "all") か、更新時に新しい値と古い値の両方が返されます (行フィルター オプション "all update old")。
ソース テーブルが有効な場合、パラメーター @supports_net_changesが 1 に設定されると、関数cdc.fn_cdc_get_net_changes_<capture_instance>が生成されます。
注
このオプションは、ソース テーブルに定義済みの主キーがある場合、またはパラメーター @index_name を使用して一意のインデックスを識別している場合にのみサポートされます。
netchanges 関数は、変更されたソース テーブル行ごとに 1 つの変更を返します。 指定した間隔で行に対して複数の変更がログに記録された場合、列の値には行の最終的な内容が反映されます。 ターゲット環境を更新するために必要な操作を正しく識別するには、TVF は、間隔中の行に対する最初の操作と行の最終操作の両方を考慮する必要があります。 行フィルター オプション 'all' を指定すると、
net changesクエリによって返される操作は、挿入、削除、または更新 (新しい値) になります。 集計マスクの計算にはコストがかかるため、このオプションは常に更新マスクを null として返します。 行に対するすべての変更を反映する集計マスクが必要な場合は、'all with mask' オプションを使用します。 ダウンストリーム処理で挿入と更新を区別する必要がない場合は、'all with merge' オプションを使用します。 この場合、操作の値は、削除の場合は 1、挿入または更新のいずれかである操作の場合は 5 の 2 つの値のみを受け取ります。 このオプションを使用すると、派生操作を挿入と更新のどちらにする必要があるかを判断するために必要な追加の処理が不要になり、この区別が不要な場合にクエリのパフォーマンスを向上させることができます。
クエリ関数から返される更新マスクは、変更データの行で変更されたすべての列を識別するコンパクトな表現です。 通常、この情報は、キャプチャされた列の小さなサブセットに対してのみ必要です。 マスクからアプリケーションで直接使用できる形式で情報を抽出するのに役立つ関数を使用できます。 関数sys.fn_cdc_get_column_ordinalは、特定のキャプチャ インスタンスの名前付き列の序数位置を返しますが、関数sys.fn_cdc_is_bit_setは、関数呼び出しで渡された序数に基づいて、指定されたマスク内のビットのパリティを返します。 これら 2 つの関数を組み合わせることで、更新マスクからの情報を効率的に抽出し、変更データの要求と共に返すことができるようになります。 これらの関数の使用方法のデモンストレーションについては、テンプレート「All With Mask を使用して Net Changes を列挙する」を参照してください。
クエリ関数のシナリオ
次のセクションでは、クエリ関数のcdc.fn_cdc_get_all_changes_<capture_instance> とcdc.fn_cdc_get_net_changes_<capture_instance>を使用して変更データ キャプチャ データに対してクエリを実行する一般的なシナリオについて説明します。
キャプチャ インスタンスの有効期間内のすべての変更に対するクエリ
変更データに対する最も簡単な要求は、キャプチャ インスタンスの有効期間内のすべての現在の変更データを返す要求です。 この要求を行うには、まず、有効期間の LSN 境界の下限と上限を決定します。 次に、これらの値を使用して、パラメーター@from_lsnおよび@to_lsnを識別し、cdc.fn_cdc_get_all_changes_<capture_instance>またはcdc.fn_cdc_get_net_changes_<capture_instance>のクエリ関数に渡します。 関数 sys.fn_cdc_get_min_lsn を使用して下限を取得し、 sys.fn_cdc_get_max_lsn して上限を取得します。 クエリ関数cdc.fn_cdc_get_all_changes_<capture_instance>を使用して現在有効なすべての変更を照会するサンプル コードについては、有効な範囲のすべての変更を列挙するテンプレートを参照してください。 関数cdc.fn_cdc_get_net_changes_<capture_instance>を使用する同様の例については、有効範囲のテンプレート「Net Changes を列挙する」を参照してください。
最後の変更セット以降のすべての新しい変更のクエリ
一般的なアプリケーションの場合、変更データのクエリは継続的なプロセスであり、前回の要求以降に発生したすべての変更に対して定期的な要求が行われます。 このようなクエリでは、 関数sys.fn_cdc_increment_lsn を使用して、前のクエリの上限から現在のクエリの下限を派生させることができます。 このメソッドは、クエリ間隔が常に閉じた間隔として扱われ、両方のエンドポイントが間隔に含まれるため、行が繰り返されないようにします。 次に、 関数sys.fn_cdc_get_max_lsn を使用して、新しい要求間隔の高いエンドポイントを取得します。 最後の要求以降のすべての変更を取得するためにクエリ ウィンドウを体系的に移動するには、サンプル コードのテンプレート「以前の要求以降のすべての変更を列挙する」を参照してください。
これまでのすべての新しい変更を検索する
クエリ関数によって返される変更に対する一般的な制約は、前の要求から現在の日時までの間に発生した変更のみを含める場合です。 このクエリでは、前の要求で使用された @from_lsn 値にsys.fn_cdc_increment_lsn関数を適用して下限を決定します。 時間間隔の上限は特定の時点として表されるため、クエリ関数で使用するには、その上限を LSN 値に変換する必要があります。 datetime 値を対応する LSN 値に変換する前に、指定した上限を通じてコミットされたすべての変更がキャプチャ プロセスで処理されていることを確認する必要があります。 これは、条件を満たしたすべての変更が変更テーブルに反映されるようにするために必要です。 これを行う 1 つの方法は、データベース変更テーブルに対して記録されている現在の最大コミット lsn が要求間隔の目的の終了時刻を超えているかどうかを定期的に確認する待機ループを構成することです。
遅延ループで、キャプチャ プロセスが関連するすべてのログ エントリを既に処理していることを確認したら、関数 sys.fn_cdc_map_time_to_lsn を使用して、LSN 値として表される新しいハイエンド ポイントを決定します。 指定した時間を通じてコミットされたすべてのエントリが確実に取得されるようにするには、関数sys.fn_cdc_map_time_to_lsnを呼び出し、オプション "largest less than or equal" を使用します。
注
非アクティブな期間に、テーブル cdc.lsn_time_mappingにダミー エントリが追加され、キャプチャ プロセスが特定のコミット時間までの変更を処理したという事実をマークします。 これにより、プロセスに対する最近の変更がない場合にキャプチャ プロセスが遅れているように見えるのを防ぐことができます。
テンプレート 「今までのすべての変更を列挙する」では、前の戦略を使用して変更データを照会する方法を示します。
すべての変更結果セットへのコミット時間の追加
データベース変更テーブル内のエントリが関連付けられている各トランザクションのコミット時間は、テーブル cdc.lsn_time_mappingで使用できます。 すべての変更の要求で返された __$start_lsn 値をcdc.lsn_time_mappingテーブル エントリのstart_lsn値と結合することで、変更データと共にtran_end_timeを返して、変更をソースでのトランザクションのコミット時間にスタンプすることができます。 テンプレート「すべての変更の結果セットへのコミット時間の追加」では、この結合を実行する方法を示します。
変更データを同じトランザクションの他のデータと結合する
場合によっては、ソースでコミットされたときにトランザクションに関して収集された他の情報と変更データを結合すると便利です。 テーブルcdc.lsn_time_mappingのtran_begin_lsn列には、このような結合を実行するために必要な情報が表示されます。 ソースの更新が行われると、システム動的ビュー sys.dm_tran_database_transactionsからの database_transaction_begin_lsn の値を、変更データと結合するその他の情報と共に保存する必要があります。 関数fn_convertnumericlsntobinaryを使用して、database_transaction_begin_lsn値とtran_begin_lsn値を比較します。 この関数を作成するコードは、テンプレートの関数の作成fn_convertnumericlsntobinaryで使用できます。 テンプレートは、指定されたtran_begin_lsnを使用してすべての変更を返し、結合を有効にする方法を示しています。
Datetime ラッパー関数を使用してクエリを行う方法
変更データのクエリを実行する一般的なアプリケーション シナリオは、datetime 値で囲まれたスライディング ウィンドウを使用して定期的に変更データを要求することです。 このクラスのコンシューマーに対して、変更データ キャプチャは、変更データ キャプチャ クエリ関数のカスタム ラッパー関数を作成するスクリプトを生成する ストアド プロシージャ sys.sp_cdc_generate_wrapper_function を提供します。 これらのカスタム ラッパーを使用すると、クエリ間隔を datetime ペアとして表すことができます。
ストアド プロシージャの呼び出しオプションを使用すると、呼び出し元がアクセスできるすべてのキャプチャ インスタンスに対してラッパーを生成したり、指定したキャプチャ インスタンスのみにラッパーを生成したりできます。 サポートされているオプションには、キャプチャ間隔のハイエンド ポイントを開くか閉じるかを指定する機能、使用可能なキャプチャ列のうち、結果セットに含める必要がある列、および関連する更新フラグを含める列を指定する機能も含まれます。 このプロシージャは、生成された関数名 (キャプチャ インスタンス名から派生) とラッパー ストアド プロシージャの create ステートメントの 2 つの列を含む結果セットを返します。 すべての変更クエリをラップする関数は常に生成されます。 キャプチャ インスタンスの作成時に @supports_net_changes パラメーターが設定された場合は、net changes 関数をラップする関数も生成されます。
アプリケーション デザイナーは、スクリプト生成ストアド プロシージャを呼び出してラッパー ストアド プロシージャの create ステートメントを生成し、結果の作成スクリプトを実行して関数を作成する必要があります。 これは、キャプチャ インスタンスが作成されるときに自動的には発生しません。
Datetime ラッパーはユーザーが所有しており、呼び出し元の既定のスキーマには作成されません。 生成された関数は、ほとんどのユーザーに変更を加えずに適しています。 ただし、関数を作成する前に、生成されたスクリプトにさらにカスタマイズをいつでも適用できます。
すべての変更クエリをラップする関数の名前がfn_all_changes_後にキャプチャ インスタンス名が続きます。 net changes ラッパーに使用されるプレフィックスはfn_net_changes_。 両方の関数は、関連する変更データ キャプチャ TVF と同様に、3 つの引数を受け取ります。 ただし、ラッパーのクエリ間隔は、2 つの LSN 値ではなく、2 つの datetime 値で囲まれます。 両方の関数セットの @row_filter_option パラメーターは同じです。
生成されるラッパー関数は、変更データ キャプチャタイムラインを体系的にウォークするための次の規則をサポートしています。前の間隔の @end_time パラメーターを後続の間隔の @start_time パラメーターとして使用することが期待されます。 ラッパー関数は、datetime 値を LSN 値にマッピングし、この規則に従っている場合にデータが失われたり繰り返されたりすることがないことを保証します。
ラッパーは、指定されたクエリ ウィンドウで閉じた上限または開いている上限をサポートするために生成できます。 つまり、呼び出し元は、コミット時間が抽出間隔の上限と等しいエントリを間隔内に含めるかどうかを指定できます。 既定では、上限が含まれます。
生成されたクエリ TVF は、 @from_lsn 値または @to_lsn 値に null 値を指定すると失敗しますが、datetime ラッパー関数は null を使用して、datetime ラッパーが現在のすべての変更を返せるようにします。 つまり、クエリ ウィンドウの下限として null が datetime ラッパーに渡された場合、キャプチャ インスタンスの有効期間の下限が、クエリ TVF に適用される基になる SELECT ステートメントで使用されます。 同様に、null がクエリ ウィンドウのハイエンド ポイントとして渡された場合、クエリ TVF から選択するときにキャプチャ インスタンスの有効期間の高いエンドポイントが使用されます。
ラッパー関数によって返される結果セットには、要求されたすべての列の後に操作列が含まれます。その後に、行に関連付けられている操作を識別するために 1 文字または 2 文字として再コーディングされます。 更新フラグが要求された場合、 @update_flag_list パラメーターで指定された順序で、操作コードの後にビット列として表示されます。 生成された datetime ラッパーをカスタマイズするための呼び出しオプションについては、 sys.sp_cdc_generate_wrapper_function (Transact-SQL) を参照してください。
テンプレートは、更新フラグを使用してラッパー TVF をインスタンス化し、生成されたラッパー関数をカスタマイズして、net changes クエリによって返される結果セットに指定された列の更新フラグを追加する方法を示しています。 スキーマの CDC ラッパー TVF のインスタンス化テンプレートは、特定のデータベース スキーマ内のソース テーブルに対して作成されたすべてのキャプチャ インスタンスのクエリ TVF の Datetime ラッパーをインスタンス化する方法を示しています。
datetime ラッパーを使用して変更データのクエリを実行する例については、「更新フラグ付きラッパーを使用して Net 変更を取得する」テンプレートを参照してください。 このテンプレートでは、ラッパーが更新フラグを返すように構成されている場合に、ラッパー関数を使用して net 変更を照会する方法を示します。 基になるクエリ関数が更新時に null 以外の更新マスクを返すには、行フィルター オプション 'all with mask' が必要であることに注意してください。 基になる LSN ベースのクエリを実行するときに、下位と上位の両方の datetime 間隔境界に対して NULL 値が渡され、キャプチャ インスタンスの有効期間の下限と上限を使用するように関数に通知されます。 このクエリは、キャプチャ インスタンスの有効な範囲内で発生したソース行に対する変更ごとに 1 行を返します。
Datetime ラッパー関数を使用してキャプチャ インスタンス間を移行する方法
変更データ キャプチャでは、1 つの追跡対象ソース テーブルに対して最大 2 つのキャプチャ インスタンスがサポートされます。 この機能の主な用途は、データ定義言語 (DDL) がソース テーブルに変更され、追跡に使用できる列のセットが拡張されたときに、複数のキャプチャ インスタンス間の遷移に対応することです。 新しいキャプチャ インスタンスに移行する場合、基になるクエリ関数の名前の変更から上位のアプリケーション レベルを保護する 1 つの方法は、ラッパー関数を使用して基になる呼び出しをラップすることです。 次に、ラッパー関数の名前が同じであることを確認します。 スイッチが発生すると、古いラッパー関数を削除し、新しいクエリ関数を参照する同じ名前の新しいラッパー関数を作成できます。 生成されたスクリプトを最初に変更して同じ名前のラッパー関数を作成することで、上位のアプリケーション レイヤーに影響を与えることなく、新しいキャプチャ インスタンスに切り替えることができます。
こちらもご覧ください
データ変更の追跡 (SQL Server)
変更データ キャプチャについて (SQL Server)
変更データ キャプチャの有効化と無効化 (SQL Server)
変更データ キャプチャの管理と監視 (SQL Server)