SQL Server の Change Data Capture（CDC）で列の変更があった場合の対処

# はじめに
[SQL Server の Change Data Capture（CDC）機能確認](https://qiita.com/tsurumiii/items/91f95add623b8c24f8d5) でCDCの設定をしてみました。そのときにテーブルに列を追加してもCDCで新しい列が無視されてしまう問題がありました。
これは個人的に使うときの問題であり SQL Server は敢えて、この仕様にしていることが分かりました。では新しい列の変更を追うにはどのようにすれば良いのでしょうか。
ある程度、方法が分かりましたので書きておきます！

##### CDC対象テーブル列追加

[公式（CDC）ソース テーブルに対する変更の処理](https://docs.microsoft.com/ja-jp/sql/relational-databases/track-changes/about-change-data-capture-sql-server?view=sql-server-2017#handling-changes-to-source-tables)
CDC に関する公式ドキュメントを確認すると以下のことが書いてあります。

- DDL の変更はあり得るため発生を防ぐことはできない
- コンシューマーへの影響を抑えるため DDL が変更されても結果セットは変更されないようにしている
- 変更データキャプチャが有効にされたときにキャプチャ対象として指定されていない列は無視される
- 追跡されている列が削除された場合、その後の変更エントリでその列に NULL 値が割り当てられる
- 既存の列のデータ型が変更された場合は、追跡されている列のデータが失われないようにするために、その変更が変更テーブルに反映される
- 追跡されているテーブルの列構造の変更が検出されると、その変更が `cdc.ddl_history` テーブルに書き込まれる

新しい列が無視される理由について理解できました。
では、新しい列が追加された場合の対処について見ていきます。

# 対処概要
1テーブルに対してキャプチャインスタンスを2つ迄作成することができます。
これを利用し、以下の手順で列追加があった場合でも変更を追うことができます。

1. DB に CDC設定
1. CDC対象テーブル設定（キャプチャインスタンス1）
1. 列追加
1. CDC対象テーブル設定（キャプチャインスタンス2）
1. 手順 2.「CDC対象テーブル設定（キャプチャインスタンス1）」の設定解除

列追加時、キャプチャインスタンス1からキャプチャインスタンス2に移行することで変更を追えるようになります。

##### 注意事項
「列追加→CDC対象テーブル設定→新しい列に値を追加」この作業をシリアルで行う必要があります。順番を守らないと設定中に変更があったデータがキャプチャされない可能性があります。

##### 確認事項
手順4 にてキャプチャインスタンス1 には新しい列が反映されていないことを確認し、キャプチャインスタンス2 には新しい列が反映されていることを確認してみます。

# 確認
[SQL Server の Change Data Capture（CDC）機能確認](https://qiita.com/tsurumiii/items/91f95add623b8c24f8d5) を参考にCDCを設定しました。今回はローカル環境にインストールした SQL Server 2017 Developer Edition を使いました。

##### テーブル作成

```sql
CREATE TABLE [dbo].[member](
    [id] [int] NOT NULL,
    [first_name] [nvarchar](50) NOT NULL,
	[last_name] [nvarchar](50) NOT NULL,
	[mail] [nvarchar](50) NOT NULL,
	[job_name] [nvarchar](50) NOT NULL
 CONSTRAINT [PK_member] PRIMARY KEY CLUSTERED 
(
    [id] ASC
))
GO
```

#### 手順1. DB に CDC設定
```sql
EXECUTE sys.sp_cdc_enable_db;  
GO
```

#### 手順2. CDC対象テーブル設定（キャプチャインスタンス1）
```sql
EXEC sys.sp_cdc_enable_table  
@source_schema = N'dbo',  
@source_name   = N'member',  
@capture_instance = N'capture_instance_1',
@role_name     = N'cdc_role',  
@filegroup_name = N'group1',
@supports_net_changes = 1
GO  
```
パラメータ @capture_instance にてインスタンスを指定しています。
任意の名前で設定できます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/324247/3b33fab6-aa74-77a4-b0d3-b7e9e9e9fb6d.png)
CDC を設定したDB のシステムテーブル内にある `change_tables` テーブルを確認すると、キャプチャインスタンス名が設定されていることが分かります。

#### 手順3. 列追加
CDC設定したテーブルに対してレコード追加と、カラム追加を行います。

レコード追加

```SQL
INSERT INTO [dbo].[member]
           ([id]
           ,[first_name]
           ,[last_name]
           ,[mail]
           ,[job_name])
     VALUES
           (1
           ,N'suzuki'
           ,N'jon'
           ,N'aaa@aaa.com'
           ,N'engineer')
GO
```
カラム追加

```sql
ALTER TABLE member
ADD job_id int NULL;
```

レコード追加がキャプチャされたことを確認できました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/324247/5eaf6e3c-4e17-ff10-de9d-5a33a7cbae25.png)

#### 手順4. CDC対象テーブル設定（キャプチャインスタンス2）
パラメータ @capture_instance を手順2.と異なる値に設定してみます。

```sql
EXEC sys.sp_cdc_enable_table  
@source_schema = N'dbo',  
@source_name   = N'member',  
@capture_instance = N'capture_instance_2',
@role_name     = N'cdc_role',  
@filegroup_name = N'group1',
@supports_net_changes = 1
GO  
```

キャプチャインスタンス毎に変更を記録するテーブルが存在することを確認します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/324247/6314f02f-cb85-a8d4-c482-96421e841db1.png)

`capture_instance_1_CT` テーブルには 新しい列 `job_id` がないことを確認します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/324247/1de4a5b7-bde5-94a2-81ed-b435aca5a0b0.png)

`capture_instance_2_CT` テーブルには 新しい列 `job_id` があることを確認します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/324247/cccb783b-a639-1da5-5c82-3a93d7845faf.png)

##### 動作確認1
レコード追加

```SQL
INSERT INTO [dbo].[member]
           ([id]
           ,[first_name]
           ,[last_name]
           ,[mail]
           ,[job_name]
           ,[job_id])
     VALUES
           (2
           ,N'tanaka'
           ,N'b-taro'
           ,N'bbb@bbb.com'
           ,N'engineer'
           ,100)
GO
```

`capture_instance_1_CT` テーブルに `id:2`レコードが追加されたこと、 新しい列 `job_id` がないことを確認します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/324247/c971b6f9-5488-f066-023d-31567b49b356.png)

`capture_instance_2_CT` テーブルに `id:2`レコードが追加されたこと、 新しい列 `job_id` もキャプチャされていることを確認し、CDC設定前の`id:1`はキャプチャされていないことを確認します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/324247/97172a9e-5dfe-9707-ecfd-b4115f5662cc.png)

補足
`id:2`レコードの`capture_instance_1_CT` `capture_instance_2_CT`に格納されたデータを見ると`_$start_lsn`が同じ値になっています。つまり、同じデータがキャプチャされたということです。

##### 動作確認2
レコード変更
`id:2`レコードの`job_id`を100→99に変更し動作を確認します。

```sql
UPDATE member SET job_id = 99 WHERE id = 2
GO
```

`capture_instance_1_CT` テーブルは変更キャプチャ対象外の項目のため何も変化がないことが確認できます。また、変更がない項目も含めてキャプチャされていることをが分かります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/324247/bd1749e7-550c-928a-8fc9-a97341c55398.png)

`capture_instance_2_CT` テーブルは変更が取得できています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/324247/d6b6837d-f892-d619-f5e3-009a539bcc5e.png)

#### 手順5. 手順 2.「CDC対象テーブル設定（キャプチャインスタンス1）」の設定解除
これまでの確認で`capture_instance_1_CT` テーブルでは新しい列が終えていないので不要と考えCDC設定を解除します。

```sql
EXEC sys.sp_cdc_disable_table  
@source_schema = N'dbo',  
@source_name   = N'member',  
@capture_instance = N'capture_instance_1'
GO  
```

CDC設定解除により `capture_instance_1_CT`テーブルが削除されました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/324247/3ede34b6-b6f4-0a8a-a9ee-16f555f5c355.png)

# 最後に
この手順で列追加があった場合でも変更が追えるになりますが、列追加がある度に手動で作業するのは面倒ですし、オペミスする可能性もありますので自動化することをお勧めします。
列に変更があったかどうかは`cdc.ddl_history`テーブルにて確認できるため、これらを駆使すれば自動化できそうです（所感）。