SQL Server の Change Data Capture（CDC）機能確認

# はじめに
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有効化方法](https://qiita.com/tsurumiii/items/b27726e02ec310933d5c)

サンプル用のDBとテーブルを用意します。（以下は例です）

```sql
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が有効化されます。

```sql
USE XXX;
GO 
EXECUTE sys.sp_cdc_enable_db;  
GO
```
対象DBに `ユーザ：cdc` が作成されました。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/324247/86b86f72-89a8-8e26-f844-4ba4dbaeaf3f.png" width=30%>

対象DBのシステムテーブルにCDC管理用のテーブルが作成されました。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/324247/2378a774-31e0-9657-c84f-a10c5fb055e9.png" width=30%>

### 参考 CDC無効化
```sql
USE XXX;
GO 
EXECUTE sys.sp_cdc_disable_db;  
GO
```

# キャプチャするテーブルを設定
CDC対象となるテーブルを指定します。1テーブルずつの指定になります。

```sql
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に指定したロールが作成されます。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/324247/b2a9d3ae-9265-0128-7481-fa344eb4c360.png" width=30%>

システムテーブルに変更情報が格納されるテーブルが作成されます。
テーブル単位で管理されます。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/324247/0343ee93-18de-0bb2-0020-d5f50d159d1e.png" width=30%>

更新情報を取得する関数が作成されます。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/324247/3438e71d-e6da-af8f-0eb4-1234770dca8c.png" width=40%>

CDCに必要なジョブが作成されます。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/324247/32e1fc3b-1fb0-6584-8808-dae308bd4997.png" width=30%>
補足：`cdc_cleanup` では `sp_MScdc_cleanup_job` が実行されており、変更情報の保持期間が設定できます。システムの特性によって保持期間を変更することをお勧めします。（保存期間を延すとデータが増えるため容量にも注意が必要です）

こちらで設定は完了です。
次にデータがどのようになっているか確認します。

# データ確認
どのようにデータがCDC用テーブルに格納されているのか確認します。

#### CDC対象テーブル管理

```sql
SELECT * from [cdc].[change_tables]
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/324247/792b514f-9a45-819c-b65b-8a4cadefb3fe.png)

または、以下のコマンドCDC対象テーブルの一覧を取得できます。

```sql
EXEC sys.sp_cdc_help_change_data_capture
```

#### CDC対象テーブルカラム管理

```sql
SELECT * FROM [cdc].[captured_columns]
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/324247/ec691855-efe7-de52-3c61-08a742b64835.png)

#### 変更データ確認

```sql
SELECT * FROM [cdc].[cdc].[dbo_member_CT] -- 作成されたテーブルを指定
```
テーブルに変更を加えていないので、変更履歴テーブル（dbo_member_CT）には何も入っていません。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/324247/f1297cbf-2d7c-c7b3-1016-595599d99372.png)

memberテーブルに対して、登録・変更・削除を行ってみます。

1. 「id：10、name：AAA」を登録
1. 「id：10、name：AAA」→「id：10、name：BBB」に変更
1. 「id：10、name：BBB」を削除

操作後、変更履歴テーブル（dbo_member_CT）を確認すると以下のようなデータが入っていて、
変更履歴を追うことができました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/324247/2857037c-cccd-6f21-bc8b-afd63d56d728.png)

_$operationの値にて操作の内容が分かります。
1：削除、2：登録、3：更新（更新前）、4：更新（更新後）
更新の場合は、3と4がセットで2レコードデータが作られます。

#### 注意点
データの変更を追うことはできますが、CDC設定前に既に入っていたデータは追えません。
またレプリケーションのように、別のDBにデータを転送する目的でCDCを使う場合は、
別途スナップショットなどで初期データを別DBに反映した後で、変更履歴を反映させる必要があります。

#### テーブル変更
データの変更履歴が取得できることが分かったので、次にテーブルにカラム追加をしてみます。

```sql
ALTER TABLE member ADD note varchar(50) null
```

DDL変更履歴を確認すると発行したコマンドが履歴として残っていました。

```sql
SELECT * FROM [cdc].[cdc].[ddl_history]
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/324247/09a6d818-ce56-9585-5d5a-33501e7fc0be.png)

`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