SQL Server ベースのデータベースのデータをデータウェアハウス / データレイクに移行 / 同期をする際に使用する際に、従来からの ETL のパイプラインを作成することなく、Zero ETL (ゼロ ETL) を使用するというアプローチがあります。
Zero ETL を使用した場合、リッチな ETL のパイプラインを作成することなく、シンプルな設定ベースで、SQL Server ベースのデータベースからデータ同期を行うことが可能となります。
SQL Server ベースのデータベースでも Zero ETL を構築することができますが、その際に使用される機能 / 仕組みの特徴についてまとめておきたいと思います。
Contents
SQL Server ベースのデータベースからの Zero ETL で使用されている機能 / 仕組み
SQL Server ベースのデータベースからの Zero ETL を実現するためには、初期データとしてのテーブル全体のデータの他に、同期対象のテーブルの差分 (更新) データの取得が必要となります。
初期データとしては、同期設定を設定した際のテーブルの全件データを連携しますが、以降の更新データについては全件同期後の差分データを生成して同期先に連携する必要があります。
差分データの取得方法としては、次のような方法が使用されることが多いです。
- 一般で実行できるステートメントを使用せずに、トランザクションログを読み取る
- 一般で実行できるステートメントを使用して、トランザクションログを読み取る
Microsoft が提供する機能 / サービスでは、「1.」が使用され、3rd パーティー製の機能 / サービスでは「2.」が使用されているかと思います。
一般で実行できるステートメントを使用せずに、トランザクションログを読み取り
これは、3rd パーティ製品では実現することはできず、Microsoft が SQL Server の機能として実装している仕組みでのみ実現することができる方法となります。
具体的な実装例としては、次のような機能があります。
(他には Always On 可用性グループもありますが、汎用的な Zero ETL では使用されないため今回は記載を除外しています)
- トランザクションレプリケーション
- トランザクションレプリケーションのログリーダー (logread.exe) により、トランザクションログの読み取りを実施
- 変更データキャプチャ (Change Data Capture: CDC)
- CDC のキャプチャプロセスにより、トランザクションログの読み取りを実施
- 変更フィード (Change Feed)
- 変更キャプチャプロセスにより、トランザクションログの読み取りを実施
これらの機能では「トランザクションログから直接更新データを読み取り同期用のデータを生成」しています。
ログの読み取り時には、明示的に SQL Server に対して一般実行できるステートメントを実行しているのではなく、バックグラウンドタスクとして、トランザクションログを内部的に読み取るタスク / プロセスが実行されているという特徴があります。
これらの機能でトランザクションログが読み取られている場合、実行されているクエリのトレースを取得してもトランザクションログの取得についての具体的なクエリは取得することができません。
SQL Server の純正の機能として実装されており、Microsoft が実装しているものとなるため、「公式情報として公開されていないトランザクションログの内容を適切に読み取ることができ、トランザクションログの切り捨てとも密接に連携ができてる」というのが最大の特徴となるのではないでしょうか。
トランザクションログの切り捨てとも密接に連携されており、連携に必要となるトランザクションログがキャプチャされる前に切り捨てられることは原則としてなく、効率的にトランザクションログを読み取り、安定したデータのキャプチャを実現することができる仕組みとなります。
変更データキャプチャ (CDC) と変更フィード (Change Feed) の違い
「変更データキャプチャ (CDC)」と「変更フィード (Change Feed」はどちらもトランザクションログから変更データをキャプチャするという似たような思想ですが特徴的な違いがあります。
CDC の特徴
CDC の最大の特徴は、変更結果を保存するための「サイドテーブル (cdc.スキーマ名_キャプチャインスタンス名_CT という名称のテーブル)」が自動的に作成される点です。
CDC は「どのデータに対して変更が行われたのかを確認することができる形式で提供する」ことを目的とした機能となります。
そのため、トランザクションログからキャプチャされた変更の履歴についてはサイドテーブルに格納されており、再度テーブルにアクセスすることでデータの変更履歴を取得することができます。
CDC を利用するデータ同期機能 (CDC ベースの同期機能) では、このサイドテーブルのデータを活用しながらデータの同期を行うことになります。
サイドテーブルは保有期間に応じてデータのクリーンアップが自動的に実行されますが、データの更新量に応じてサイドテーブルのデータは増加傾向があるのが特徴となります。
変更フィードの特徴
変更フィードも CDC と同様に、トランザクションログから変更の履歴をキャプチャします。
しかし、キャプチャした内容はサイドテーブルには格納されず、データ同期先 (Synapse DWH / Microsoft Fabric) に連携が行われています。
変更フィードは、フィードの連携対象となる同期先にのみデータの連携をすることを目的としており、キャプチャされたデータをユーザーが他の目的で活用するということは想定していません。
そのため、CDC のようなサイドテーブルは作成されず、データの更新量に応じたデータファイルの使用状況の増加は顕著に発生しないという特徴があります。
変更フィードによりデータの同期が行える機能は現時点では、Synapse Link for SQL と Microsoft Fabric Database Mirroring のみとなりますが、CDC と異なりサイドテーブルが不要であり、データファイルの増加を抑えることができるというメリットがあります。
変更データキャプチャ (CDC) と変更フィード (Change Feed) で共通の制約
ADR のログの積極的な切り捨ての自動的な無効化
CDC と変更フィードで共通の制約として、高速データベース復旧 (ADR) によるトランザクションログの積極的な切り捨てが自動的に無効となります。
- 高速データベース復旧 (ADR) と Change Data Capure (CDC)
- SQL 用 Azure Synapse Link に関する制限事項と既知の問題
- Fabric Database Mirroring の制限事項 には記載されていませんでしたが、Synapse Link と同様の制約となり ADR は無効化されるかと思います。
ADR は未コミットの状態でも積極的にトランザクションログの切り捨てを行えるようにする積極的なトランザクションログの切り捨ての機能が実装されています。
しかし、トランザクションログのキャプチャと同時に動作していると未キャプチャのログが切り捨てられてしまう可能性があるため、CDC / 変更フィードを有効にしているデータベースでは ADR の積極的なログの切り捨て機能が自動的に無効化されます。
変更データキャプチャ (CDC) と変更フィード (Change Feed) の共存はできない
CDC と変更フィードですが両機能を同時に有効化することはできません。これについては、次のドキュメントに記載されています。
- SQL 用 Azure Synapse Link に関する制限事項と既知の問題
- Azure SQL データベースからの Microsoft Fabric ミラー化データベースの制限事項 (プレビュー)
そのため、次のようなシナリオは実現することはできません。
- CDC でデータのキャプチャを行っているデータベースで、変更フィードを有効化
- 変更フィードを有効化しているデータベースで、CDC ベースのログ同期を実行する
これは、Synapse Link / Fabric Mirroring を使用しているデータベースを 3rd パーティ製品の機能 / サービスで、データ同期をしたいというような要件があった場合に、影響する制約となる可能性があるため、CDC と変更フィードの共存については注意が必要となります。
Synapse Link と Fabric Mirroring の共存はできない
Microsoft としては Fabric にシフトしたいのだとは思いますが、現場では Synapse が使用されていることも多いです。
Azure SQL データベースからの Microsoft Fabric ミラー化データベースの制限事項 (プレビュー) に記載されていますが、Synapse Link と Fabric Mirroring は同時に有効にすることはできません。
- データベースで Change Data Capture (CDC)、Azure Synapse Link for SQL が有効になっている場合、またはデータベースが既に別の Fabric ワークスペースにミラー化されている場合は、Azure SQL データベースをミラーリングできません。
そのため、SQL Database のデータを Zero ETL で同期する際には、Synapse か Fabric のどちらか一方に対してのみ同期をとる構成とする必要があります。
一般で実行できるステートメントを使用して、トランザクションログを読み取る
トランザクションログを読み取る方法
SQL Server ではアンドキュメントな関数となりますが、次のようなテーブル値関数を使用することで、トランザクションログのデータを読み取ることができます。
- sys.fn_dblog (以前の DBCC LOG 相当)
- 現在のトランザクションログから、ログレコードの内容を読み取る
- sys.fn_dump_dblog
- トランザクションログのバックアップから、ログレコードの内容を読み取る
これらの関数を使用することで、トランザクションログのデータをクエリで明示的に読み取ることができます。
3rd パーティ製品で「トランザクションログを読み取って同期データを生成する」とされている場合、これらの関数を実行してトランザクションログのレコードが取得されており、上記の関数の実行権限が求められている記載があるかと思います。
この方法を使用した同期データの生成は次のような製品 / サービスで使用されています。
これらの製品 / サービスでは SQL Server が提供している関数をクエリにより明示的に実行することでトランザクションログの内容をレコードとして取得しています。
sys.fn_dblog / sys.fn_dump_dblog は前述のとおり、アンドキュメントな関数となります。
SQL Server のトランザクションログのフォーマットは公式のドキュメントとして公開されていません。
そのため、関数を実行してトランザクションログのログレコードを取得し同期データを生成 / 可視化しているような製品 / サービス / 機能は「トランザクションログを独自に解析しデータ同期に活用している」と言えるのではないでしょうか。
トランザクションログのログフォーマットの情報は非公開ですが、ApexSQL SQL Transaction reader のように、トランザクションログのログレコードの内容を可視化する製品や ACCESSING THE LOG FILE WITH FN_DBLOG ? PART 1, BASIC EXAMPLES / MSSQLLogAnalyzer のように、ログレコードを解析した情報が公開されています。
様々なステートメントを実行した結果を分析することで、独自に解析してどのような変更が行われたのかを複合化することは、労力は必要ですが現実的に実施することは可能です。
3rd パーティ製品で「トランザクションログを読みとりデータ同期を行っている」としている製品については、「トランザクションログのログフォーマットを独自に解析し、データ同期に必要となる変更データを生成している」ということになるのではないでしょうか。
変更データの取得方法
3rd パーティ製品で変更データを取得する方法としては次の 2 種類が挙げられるのではないでしょうか。
- トランザクションログベース
- CDC ベース
トランザクションログベース
トランザクションログベース (ログベース) については、前述のとおり sys.fn_dblog / sys.fn_dump_dblog を使用してトランザクションログのデータを読み取る方式となります。
この方式が使用されているのは、前述した次のようなサービスです。
これらのサービスを使用してデータの同期をする場合、SQL Server でトランザクションレプリケーションの有効化または、CDC を有効化し、これらの機能の対象として同期対象のテーブルを指定するケースが多いです。
トランザクションレプリケーションや CDC を有効化したテーブルについては、トランザクションログがレプリケーション用にマークされるため、単一のログレコードだけでデータ同期用のデータが生成しやすくなるフォーマットとなります。(更新系では生成されるログレコードに顕著に差が出てきます)
また、トランザクションレプリケーション / CDC を有効にすることで、トランザクションログの切り捨てタイミングの調整がしやすくなるということもあります。
ログベースの同期機能はログを読み取るだけでは完結できない
トランザクションログベースのデータ同期では、sys.fn_dblog / sys.fn_dump_dblog を読み取るだけでなく、トランザクションレプリケーション / CDC の設定と併用されることが多いです。
これらの機能を有効化することで、サイドテーブルが作成されることもあるのですが、サイドテーブルのデータは使用せずにトランザクションログのデータを基にして同期対象のデータが生成されます。
Qlick Replication, AWS DMS では、トランザクションレプリケーションまたはCDC / Google Cloud Datastream については、CDC の有効化が必要となっており、使用するサービスによって、トランザクションログベースのデータ同期を行う場合にどの機能を有効にすればよいかは異なってきます。
ログベースの同期機能で他の機能と組み合わせる必要のある理由としては次のような内容があるのではないでしょうか。
- トランザクションログから同期対象としたテーブルの複製用ログレコードの取得が容易となる
- 同期機能を活用することで、キャプチャされていないログの切り捨てが行われるのをある程度制御することができる
トランザクションログを読み取るだけでは、同期設定を行ったテーブルの変更に必要となるログの読み取り / 保持をすることができないため、SQL Server 標準の同期機能と組み合わせる必要があるという認識です。
トランザクションレプリケーションと CDC の違い
トランザクションレプリケーションは主キーが必要となるため、主キーがあるテーブルだけを連携すればよい場合はトランザクションレプリケーションを使用したログベースの機能、主キーが無いデータを同期する必要がある場合は CDC を使用したログベースの機能を活用することになるのではないでしょうか。
CDC を使用するログベースの機能の場合、サイドテーブルは直接は使用しないが機能の有効化のために必要となり、データファイルの使用状況の増加につながる可能性があります。
また、トランザクションレプリケーションベースの機能であれば、ある程度トランザクションログの切り捨てのタイミングを製品側で制御をすることができます。
CDC の場合はシンプルなバックグラウンドタスクでのみ実施されているため、トランザクションログの切り捨てを抑制するためには、Google Cloud Datastream で推奨されているような、ログの切り捨ての防止対策を設定する のような考慮も必要となってきます。
トランザクションレプリケーションによるログベースの機能が使用できるのであれば、CDC ではなく、レプリケーションベースの機能を使用したほうが、サイドテーブルが不要となり、データファイルの増加を抑えることができるのではないでしょうか。。
CDC ベース
CDC ベースは、トランザクションログを読み取るのではなく、CDC を有効にすることで作成されたサイドテーブルのデータを読み込んでデータ同期をする方法となります。
- Qlick Replication
- Google Cloud Datastream
- CData Sync
- SQL Server Connector for CData Sync
- CDC ではなく、変更の追跡 (Change Tracking) による同期も提供されていますが今回は割愛
トランザクションログを直接読むことができない環境でも、CDC ベースの同期機能が提供されている場合、サイドテーブル経由でデータの変更を検知しデータの同期を行うことができる可能性があります。
サイドテーブルのデータが必要となるため、データの更新量に応じたデータファイルサイズの使用状況の増加を意識する必要がありますが、変更されたデータをキャプチャ済みのデータを使用して同期を行うという構成となるため、データ同期の仕組みについてはシンプルな構成となります。
環境に依存することなくデータにアクセスすることが可能となりますが、サイドテーブルに対してのデータアクセスが頻繁に発生するため、変更データ取得のためのアクセス効率についてはログベースと比較して低い可能性が出てきます。
Google Cloud Datastream を使用した Azure SQL Database からの Zero ETL
Google Cloud Datastream については、Azure SQL Database からの連携もサポートされており CDC がサポートされている SKU であれば、Cloud Storage か Big Query に SQL Database のデータを同期することができます。
設定手順については、Azure SQL データベースを構成する に記載されていますが、現状、プレビューということもあってか、ドキュメントの内容は SQL Server 向けの記載が混在しており、全てのクエリを実行することはできないかと思います。
SQL Database を対象とした場合、sys.fn_dblog へのアクセス権をうまく付与することができなかったため、ログベースの同期機能を使用するように設定することはできませんでした。
Datastream は、ログベースと CDC ベースの 2 種類のデータ同期方法を提供しているので、SQL Database から Big Query にデータ同期をしたいという場合には、現時点では、CDC ベースの設定 (テーブルを変更を利用) を使用する必要があるのではないでしょうか。