はじめに
SQL Serverにはデータの変更を追うことができる機能があります。
「Change Data Capture（CDC）= 変更データキャプチャ」と呼ばれています。
テーブルにデータが追加・削除・更新などの変更情報を追うことができます。
また同一レコードが更新された場合でもシーケンシャルに変更が分かります。
機能確認をしてみました。
CDCはSQL Server 2008～2015のEnterprise Edition、または、SQL Server 2016以上のStandard Edition / Enterprise Editionで利用できます。（もちろんDeveloper Editionはフル機能が開放されているので利用できます）
環境
AWS EC2にSQL Serverをインストールした環境で確認しました。
- Windows Server 2019
- SQL Server 2017 Standard Edition
前提
SQL Server Agentが実行されている必要があります。
SQL Server Agent有効化方法
サンプル用のDBとテーブルを用意します。（以下は例です）
CREATE TABLE [dbo].[member](
[id] [int] NOT NULL,
[name] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_member] PRIMARY KEY CLUSTERED
(
[id] ASC
))
GO
CDC有効化
CDCを使用するデータベースを指定して sp_cdc_enable_db を実行することでCDCが有効化されます。
USE XXX;
GO
EXECUTE sys.sp_cdc_enable_db;
GO
対象DBに ユーザ：cdc が作成されました。
対象DBのシステムテーブルにCDC管理用のテーブルが作成されました。
参考 CDC無効化
USE XXX;
GO
EXECUTE sys.sp_cdc_disable_db;
GO
キャプチャするテーブルを設定
CDC対象となるテーブルを指定します。1テーブルずつの指定になります。
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'member',
@role_name = N'cdc_role',
@filegroup_name = N'cdc_fg',
@supports_net_changes = 1
GO
- source_schema：スキーマ名を指定
- source_name：テーブル名を指定
- role_name：CDCで利用するロールを指定（指定したロールが無ければ新規でロールが作成される）
- filegroup_name (option)：変更が多いテーブルの場合、キャプチャデータが多くなるためファイルグループを適切に設定しCDC用の容量を確保する
- supports_net_changes (option)：0の場合はすべての変更を照会するサポート関数のみ作成され、1の場合はすべての変更を照会するサポート関数、差分変更のクエリに必要な関数が作成されます。データを一意で識別する必要があるため主キーまたは、一意キーが必要です。一意キーを利用している場合は、index_name(option)を指定する必要があります。
その他のオプションはこちらを参照ください。
https://docs.microsoft.com/ja-jp/sql/relational-databases/system-stored-procedures/sys-sp-cdc-enable-table-transact-sql?view=sql-server-2017
role_nameに指定したロールが作成されます。
システムテーブルに変更情報が格納されるテーブルが作成されます。
テーブル単位で管理されます。
更新情報を取得する関数が作成されます。
CDCに必要なジョブが作成されます。
補足：cdc_cleanup では sp_MScdc_cleanup_job が実行されており、変更情報の保持期間が設定できます。システムの特性によって保持期間を変更することをお勧めします。（保存期間を延すとデータが増えるため容量にも注意が必要です）
こちらで設定は完了です。
次にデータがどのようになっているか確認します。
データ確認
どのようにデータがCDC用テーブルに格納されているのか確認します。
CDC対象テーブル管理
SELECT * from [cdc].[change_tables]
または、以下のコマンドCDC対象テーブルの一覧を取得できます。
EXEC sys.sp_cdc_help_change_data_capture
CDC対象テーブルカラム管理
SELECT * FROM [cdc].[captured_columns]
変更データ確認
SELECT * FROM [cdc].[cdc].[dbo_member_CT] -- 作成されたテーブルを指定
テーブルに変更を加えていないので、変更履歴テーブル（dbo_member_CT）には何も入っていません。
memberテーブルに対して、登録・変更・削除を行ってみます。
- 「id：10、name：AAA」を登録
- 「id：10、name：AAA」→「id：10、name：BBB」に変更
- 「id：10、name：BBB」を削除
操作後、変更履歴テーブル（dbo_member_CT）を確認すると以下のようなデータが入っていて、
変更履歴を追うことができました。
_$operationの値にて操作の内容が分かります。
1：削除、2：登録、3：更新（更新前）、4：更新（更新後）
更新の場合は、3と4がセットで2レコードデータが作られます。
注意点
データの変更を追うことはできますが、CDC設定前に既に入っていたデータは追えません。
またレプリケーションのように、別のDBにデータを転送する目的でCDCを使う場合は、
別途スナップショットなどで初期データを別DBに反映した後で、変更履歴を反映させる必要があります。
テーブル変更
データの変更履歴が取得できることが分かったので、次にテーブルにカラム追加をしてみます。
ALTER TABLE member ADD note varchar(50) null
DDL変更履歴を確認すると発行したコマンドが履歴として残っていました。
SELECT * FROM [cdc].[cdc].[ddl_history]
ddl_history にデータが入るところまでは良かったのですが、追加したカラムが dbo_member_CT に反映されませんでした。 captured_columns にも更新したカラム情報は反映されていませんでした。
つまり sp_cdc_enable_table を実行した直後のカラム状態を保持しますが、その後テーブルに変更があってもCDCで管理しているテーブルやCDC用の関数などは変更されないです。追加カラムの変更が負えないのは辛いです。
対処としては一度 sp_cdc_disable_table にてテーブルをCDC無効化した後、再度 sp_cdc_enable_table で有効化することで最新の状態にすることができそうです。
しかし、無効化した間にデータ更新があった場合に追えなくなるというデメリットがあります。
カラム追加をする場合は、気を付けて使った方が良いことが分かりました。
（自動で情報更新されると、使い勝手が良かったのですが、、残念です）
まとめ
気を付けた方が良い点もありますが、変更データを追えるようになるため様々な用途で利用できそうです。
注意点
- 更新データが溜まるので容量に気を付けた方が良い
- トランザクションログを読み込みながら変更データをCDC管理用のテーブル
<schema_name>_<table_name>_CTに書き込むためオーバーヘッドがある - カラム変更をした場合は、CDC情報を最新化する必要がある(CDC無効化→有効化などで対処する)
参考
変更データ キャプチャについて (SQL Server)
https://docs.microsoft.com/ja-jp/sql/relational-databases/track-changes/about-change-data-capture-sql-server?view=sql-server-2017