はじめに

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

CDC対象テーブル列追加

公式（CDC）ソース テーブルに対する変更の処理
CDC に関する公式ドキュメントを確認すると以下のことが書いてあります。

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

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

対処概要

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

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

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

注意事項

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

確認事項

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

確認

SQL Server の Change Data Capture（CDC）機能確認 を参考にCDCを設定しました。今回はローカル環境にインストールした SQL Server 2017 Developer Edition を使いました。

テーブル作成

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設定

EXECUTE sys.sp_cdc_enable_db;  
GO

手順2. CDC対象テーブル設定（キャプチャインスタンス1）

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 にてインスタンスを指定しています。
任意の名前で設定できます。

CDC を設定したDB のシステムテーブル内にある change_tables テーブルを確認すると、キャプチャインスタンス名が設定されていることが分かります。

手順3. 列追加

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

レコード追加

INSERT INTO [dbo].[member]
           ([id]
           ,[first_name]
           ,[last_name]
           ,[mail]
           ,[job_name])
     VALUES
           (1
           ,N'suzuki'
           ,N'jon'
           ,N'[email protected]'
           ,N'engineer')
GO

カラム追加

ALTER TABLE member
ADD job_id int NULL;

レコード追加がキャプチャされたことを確認できました。

手順4. CDC対象テーブル設定（キャプチャインスタンス2）

パラメータ @capture_instance を手順2.と異なる値に設定してみます。

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  

キャプチャインスタンス毎に変更を記録するテーブルが存在することを確認します。

capture_instance_1_CT テーブルには 新しい列 job_id がないことを確認します。

capture_instance_2_CT テーブルには 新しい列 job_id があることを確認します。

動作確認1

レコード追加

INSERT INTO [dbo].[member]
           ([id]
           ,[first_name]
           ,[last_name]
           ,[mail]
           ,[job_name]
           ,[job_id])
     VALUES
           (2
           ,N'tanaka'
           ,N'b-taro'
           ,N'[email protected]'
           ,N'engineer'
           ,100)
GO

capture_instance_1_CT テーブルに id:2レコードが追加されたこと、 新しい列 job_id がないことを確認します。

capture_instance_2_CT テーブルに id:2レコードが追加されたこと、 新しい列 job_id もキャプチャされていることを確認し、CDC設定前のid:1はキャプチャされていないことを確認します。

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

動作確認2

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

UPDATE member SET job_id = 99 WHERE id = 2
GO

capture_instance_1_CT テーブルは変更キャプチャ対象外の項目のため何も変化がないことが確認できます。また、変更がない項目も含めてキャプチャされていることをが分かります。

capture_instance_2_CT テーブルは変更が取得できています。

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

これまでの確認でcapture_instance_1_CT テーブルでは新しい列が終えていないので不要と考えCDC設定を解除します。

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テーブルが削除されました。

最後に

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