30年以上にわたり、Microsoft SQL Serverは無数の企業の企業データの保存と管理に貢献してきましたが、今でも地球上で最も広く使用されているソフトウェアアプリケーションの1つです。 DB-Enginesのデータベースランキングによると、SQL Serverは、OracleとMySQLに次いで3番目に人気のあるデータベース管理システムです。

Change data capture（CDC）は、多くの企業にとって、特にリアルタイムETLのユースケースでは必須の機能です。しかし、SQL ServerでのChange data captureはどうでしょうか。その答えは、こちらをご覧ください。

thumbnail image

Table of Contents

SQL ServerにおけるChange Data Capture とは?

最近では、データ統合のベストプラクティスの一つとして、データをデータウェアハウスやデータレイクなどの一元化されたリポジトリに移すことが挙げられています。統一された一元化されたリポジトリを維持することで、「single version of the truth」を保ち、ビジネス・インテリジェンス（BI）やアナリティクスを行うことが容易になります。

Change data capture (CDC)とは、特定のデータソースからの情報が追加または変更されたことを検知するためのいくつかの技術を指します。CDCのおかげで、ユーザーはデータベース全体ではなく、実際に更新が必要なデータだけを移行することができます。これにより、ETLやデータ統合のプロセスが劇的に速くなり、特にリソースを必要とするETLの変換段階での効率が向上します。CDCは、他にもいくつかのユースケースがあります（例：データベース監査のためのペーパートレイルとして使用する）。

Microsoft SQL Serverでは、CDCはログベースです。つまり、CDC は、データベースで行われたすべてのトランザクション（INSERT、UPDATE、DELETE などの操作）を記録したトランザクションログと呼ばれる別のファイルを監視して動作します。トランザクションログは、主にクラッシュ後のデータベースの回復を助けることを目的としていますが、CDCを有効化する目的でも使用されます。

CDCツールは、元のデータベースから、トランザクションログを読み込んだ後、各オペレーションを変更テーブルに適用します。CDCツールは、指定された期間に行われた変更をフィルタリングし、その情報を使って一元化されたデータリポジトリのコピー先テーブルを更新します。

SQL ServerでのChange Data Captureの仕組み

ここまでは、SQL ServerにおけるCDCの一般的な考え方について説明しましたが、ここからは技術的な詳細について説明します。

SQL Serverデータベースへの更新を追跡する前に、システム管理者はsys.sp_cdc_enable_dbプロシージャーを使用し、現在のデータベースに対してCDCを有効にしなければなりません。一方、sys.sp_cdc_enable_tableというプロシージャは、データベース内の特定のテーブルに対してCDCを有効化することができます。CDCがすでに有効になっているかどうかは、sys.databasesカタログビューのis_cdc_enabled列を照会することで確認することもできます。

逆に、プロシージャsys.sp_cdc_disable_dbおよびsys.sp_cdc_disable_table は、それぞれ指定されたデータベースまたはテーブルに対してCDCを無効化します。これにより、当該データベースやテーブルに関連するすべてのCDCメタデータが自動的に削除されます。

SQL Serverの更新テーブルでは、最初の5つの列はメタデータで、その中に各更新に関する追加情報が含まれています。これらのカラムは以下の通りです。

  • __$start_lsn: 特定の更新に対するコミット・ログ・シーケンス番号(LSN)の開始点。
  • __$end_lsn: 特定の更新に関するコミットのLSNの終了点。
  • __$seqval: 複数更新トランザクション内の特定の更新の順序。
  • __$operation: 実行された更新のタイプを示します。1はDelete操作を、2はInsert操作を、3は更新前のUpdate操作を、4は更新後のUpdate操作を表します。
  • __$update_mask: ビットマスクで、各ビットが指定されたカラムへの可能な変更を示します。更新がDeleteまたはInsert操作の場合、すべてのカラムのビットは1に設定されます。更新がUpdate操作である場合、更新されたカラムのみに1がセットされます。

SQL Serverの更新テーブルは、制御不能になるのを防ぐために、定期的にクリーンアップする必要があります。例えば、変更されたデータに対して既にETLプロセスを実行している場合は、変更テーブルの内容をフラッシュすることで、すでに古くなった情報と混同しないようにすることができます。SQL Serverの変更テーブルの情報を保持する期間は、デフォルトでは3日間です。 

最後に、SQL ServerでのCDCの利用にあたって、いくつかの雑学を紹介します。

  • CDCは、SQL Server 2019から、（またはCumulative Update 18（CU18）がインストールされている場合はSQL Server 2017から）、すべてのLinuxユーザーが利用できます。(CDCは、SQL Server 2008からWindowsユーザー向けに提供されています)。
  • CDC は、クラスター化カラムストア インデックスを持つデータベース テーブルでは利用できませんが、ユーザーは SQL Server 2016 のリリース以降、非クラスター化カラムストア インデックスを持つテーブルで CDC を実行することができます。
  • CDCは、Microsoftのクラウド版SQL ServerであるAzure SQL DatabaseのBasic、S0、S1、S2の各サービスプランでは使用できません。Standard 3（S3）以上のサービスプランでのみ使用可能です。

Integrate.ioはCDCにどう役に立つのか?

この記事でご紹介したように、SQL ServerのCDCは複雑で技術的にも大変な作業です。SQL Serverの変更データキャプチャのパラダイムを扱うには、SQLクエリ言語やMicrosoft SQL Serverプラットフォームを熟知し、操作することが必要です。

しかし、効率的なETLやデータ統合に依存する多くのユースケースでは、CDCは依然として不可欠です。この2つの問題をどのように解決すればよいのでしょうか。良いニュースは、SQL ServerのCDCには、より簡単なアプローチがあるということです。

多くの企業にとって、その答えは、Integrate.ioのような強力な自動ETLツールを活用することです。Integrate.ioプラットフォームは、データソースからクラウド上のデータウェアハウスやデータレイクへの迅速かつ効率的なデータ統合を可能にします。Integrate.ioには、ログベースやトリガーベースのCDC機能、コーディング不要のドラッグ＆ドロップ式のユーザーインターフェース、SQL Serverを含む100以上のプリビルドコネクタなどの機能があります。

Integrate.ioでSQL Serverデータベースへの接続を作成するのは簡単です。Integrate.ioにSQL Serverへのアクセスを許可し、接続が完了すると、SQL Serverデータベースのテーブルの読み取りと書き込みの両方が可能になり、ETLワークフローを効率化するためのCDCを実現することもできます。

Related Reading: Allowing Integrate.io Access to Microsoft SQL Server Databases

Integrate.ioのおかげで、CDCの実行と堅牢なETLデータパイプラインの構築がかつてないほど簡単になりました。Integrate.ioがお客様のSQL ServerのCDCのニーズにどのように対応できるかを知りたい方は、画面右上のデモリクエストボタンより、オンラインデモにお申し込みください。