はじめに
SQL Serverの変更データキャプチャ機能(以降、CDC)を使用し、データ変更の履歴を取得します。
CDCを使えば自前でトリガーを仕込まなくてもトランザクションに紐づくデータ変更の履歴を取得することができるので、CRUD図の存在しないシステムの調査や監査証跡等で活用できると思います。
環境構築
SQL ServerとSQL Server Agentのインストール
以下の記事を参考に、Docker環境を用意します。
$ docker pull dbafromthecold/sqlserverlinuxagent:latest $ docker run -d -p 15789:1433 --env ACCEPT_EULA=Y --env SA_PASSWORD=lis6ot9I --name sqlserver linuxwithagent $ docker exec -it sqlserver bash
sqlcmdのインストール
SQLの発行にsqlcmdを使うためmssql-toolsをインストールします。
# curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - # curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | tee /etc/apt/sources.list.d/msprod.list # apt-get update # apt-get install -y mssql-tools unixodbc-dev
クエリの発行と変更データのキャプチャ
DBサーバに接続
# /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P lis6ot9I
DBの作成
1> CREATE DATABASE CDC_TEST; 2> GO 1> USE CDC_TEST; 2> GO Changed database context to 'CDC_TEST'.
テーブルの作成
履歴の取得対象となるテーブルを用意します。
1> CREATE TABLE TestTable ( 2> ColumnA INT NOT NULL IDENTITY(1,1) PRIMARY KEY, 3> ColumnB INT NOT NULL, 4> ColumnC TINYINT NOT NULL, 5> ColumnD NVARCHAR(20) NOT NULL, 6> ColumnE CHAR(1) NOT NULL, 7> ColumnF BIT NOT NULL DEFAULT(0) 8> ); 9> GO
初期データの挿入
1> INSERT INTO TestTable VALUES 2> (1, 2, 'a', 'b', 1), 3> (1, 2, 'a', 'b', 1), 4> (1, 2, 'a', 'b', 1), 5> (1, 2, 'a', 'b', 1) 6> GO (4 rows affected)
CDC機能の有効化
履歴取得の対象となるデータベースとテーブルに対してCDC機能を有効化します。
1> EXECUTE sys.sp_cdc_enable_db; 2> GO 1> EXECUTE sys.sp_cdc_enable_table 2> @source_schema = N'dbo' 3> , @source_name = N'TestTable' 4> , @role_name = N'cdc_Admin'; 5> GO Job 'cdc.CDC_TEST_capture' started successfully. Job 'cdc.CDC_TEST_cleanup' started successfully.
これでCDCを有効にしたテーブル TestTable に対して更新操作（INSERT、UPDATE、DELETE）が行われると、 cdc.スキーマ名_テーブル名_CT に変更履歴が出力されます。
1> SELECT COUNT(*) FROM cdc.dbo_TestTable_CT;
2> GO
-----------
0
(1 rows affected)
クエリの発行と履歴の確認
更新履歴（UPDATE）
変更前のデータは以下の通りです。
1> SELECT * FROM TestTable;
2> GO
ColumnA ColumnB ColumnC ColumnD ColumnE ColumnF
----------- ----------- ------- -------------------- ------- -------
1 1 2 a b 1
2 1 2 a b 1
3 1 2 a b 1
4 1 2 a b 1
(4 rows affected)
ColumnA が3の行を対象に、 ColumnB と ColumnD を更新してみます。
1> UPDATE TestTable SET ColumnB = 5, ColumnD = 'AAAA' WHERE ColumnA = 3;
2> GO
(1 rows affected)
1> SELECT * FROM TestTable;
2> GO
ColumnA ColumnB ColumnC ColumnD ColumnE ColumnF
----------- ----------- ------- -------------------- ------- -------
1 1 2 a b 1
2 1 2 a b 1
3 5 2 AAAA b 1
4 1 2 a b 1
(4 rows affected)
cdc.dbo_TestTable_CT を確認すると、以下の通り更新前後の履歴が出力されていることがわかります。
__$start_lsn と __$end_lsn ではLSN（Log Sequence Number：ログ順序番号）によって、更新時刻（や順序）を管理しています。
LSNはトランザクション単位に一意となるため、同一トランザクションで影響を受けたデータを特定することができます。
__$operation には履歴の種別（1:削除、2:挿入、3:更新前、4:更新後）が格納されます。
1> SELECT * FROM cdc.dbo_TestTable_CT; 2> GO __$start_lsn __$end_lsn __$seqval __$operation __$update_mask ColumnA ColumnB ColumnC ColumnD ColumnE ColumnF __$command_id ---------------------- ---------------------- ---------------------- ------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------- ----------- ------- -------------------- ------- ------- ------------- 0x00000025000004A00003 NULL 0x00000025000004A00002 3 0x0A 3 1 2 a b 1 1 0x00000025000004A00003 NULL 0x00000025000004A00002 4 0x0A 3 5 2 AAAA b 1 1 (2 rows affected)
挿入の履歴（INSERT）
適当なデータを1件挿入します。
1> INSERT INTO TestTable VALUES 2> (3, 9, 'C', 'E', 0) 3> GO (1 rows affected)
挿入の履歴が追加されました。
1> SELECT * FROM cdc.dbo_TestTable_CT; 2> GO __$start_lsn __$end_lsn __$seqval __$operation __$update_mask ColumnA ColumnB ColumnC ColumnD ColumnE ColumnF __$command_id ---------------------- ---------------------- ---------------------- ------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------- ----------- ------- -------------------- ------- ------- ------------- ... 0x00000025000007280004 NULL 0x00000025000007280003 2 0x3F 5 3 9 C E 0 1 (3 rows affected)
削除の履歴（DELETE）
ColumnB が1のデータ3件を削除します。
1> SELECT * FROM TestTable;
2> GO
ColumnA ColumnB ColumnC ColumnD ColumnE ColumnF
----------- ----------- ------- -------------------- ------- -------
1 1 2 a b 1
2 1 2 a b 1
3 5 2 AAAA b 1
4 1 2 a b 1
5 3 9 C E 0
(5 rows affected)
1> DELETE FROM TestTable WHERE ColumnB = 1;
2> GO
(3 rows affected)
削除の履歴が3件追加されました。
1> SELECT * FROM cdc.dbo_TestTable_CT; 2> GO __$start_lsn __$end_lsn __$seqval __$operation __$update_mask ColumnA ColumnB ColumnC ColumnD ColumnE ColumnF __$command_id ---------------------- ---------------------- ---------------------- ------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------- ----------- ------- -------------------- ------- ------- ------------- ... 0x00000025000009300007 NULL 0x00000025000009300002 1 0x3F 1 1 2 a b 1 1 0x00000025000009300007 NULL 0x00000025000009300005 1 0x3F 2 1 2 a b 1 2 0x00000025000009300007 NULL 0x00000025000009300006 1 0x3F 4 1 2 a b 1 3 (6 rows affected)
履歴の取得時刻を確認する方法
sys.fn_cdc_map_lsn_to_time にLSNを渡すと取得できます。
例えば __$start_lsn が 0x00000025000004A00003 の履歴の取得時刻は以下の通りです。
1> SELECT sys.fn_cdc_map_lsn_to_time(0x00000025000004A00003);
2> GO
-----------------------
2020-08-15 03:08:17.613
(1 rows affected)
履歴情報の整形
上記の履歴はわかりにくいので例えば以下のようなSQLで加工すると良いと思います。
1> SELECT sys.fn_cdc_map_lsn_to_time(__$start_lsn) AS 'TIME', 2> CASE WHEN __$operation = 1 THEN 'Delete' 3> WHEN __$operation = 2 THEN 'INSERT' 4> WHEN __$operation = 3 THEN 'BEFORE UPDATE' 5> WHEN __$operation = 4 THEN 'AFTER UPDATE' 6> END AS OPERATION, 7> ColumnA, 8> ColumnB, 9> ColumnC, 10> ColumnD, 11> ColumnE, 12> ColumnF 13> FROM cdc.dbo_TestTable_CT 14> ORDER BY __$start_lsn; 15> GO TIME OPERATION ColumnA ColumnB ColumnC ColumnD ColumnE ColumnF ----------------------- ------------- ----------- ----------- ------- -------------------- ------- ------- 2020-08-15 03:08:17.613 BEFORE UPDATE 3 1 2 a b 1 2020-08-15 03:08:17.613 AFTER UPDATE 3 5 2 AAAA b 1 2020-08-15 03:11:45.350 INSERT 5 3 9 C E 0 2020-08-15 03:16:47.667 Delete 1 1 2 a b 1 2020-08-15 03:16:47.667 Delete 2 1 2 a b 1 2020-08-15 03:16:47.667 Delete 4 1 2 a b 1 (6 rows affected)
日時で絞り込みたい場合にはWHERE句を追加します。
1> SELECT sys.fn_cdc_map_lsn_to_time(__$start_lsn) AS 'TIME', 2> CASE WHEN __$operation = 1 THEN 'Delete' 3> WHEN __$operation = 2 THEN 'INSERT' 4> WHEN __$operation = 3 THEN 'BEFORE UPDATE' 5> WHEN __$operation = 4 THEN 'AFTER UPDATE' 6> END AS OPERATION, 7> ColumnA, 8> ColumnB, 9> ColumnC, 10> ColumnD, 11> ColumnE, 12> ColumnF 13> FROM cdc.dbo_TestTable_CT 14> WHERE sys.fn_cdc_map_lsn_to_time(__$start_lsn) BETWEEN '2020-08-15 03:11:00' AND '2020-08-15 03:12:00' 15> ORDER BY __$start_lsn; 16> GO TIME OPERATION ColumnA ColumnB ColumnC ColumnD ColumnE ColumnF ----------------------- ------------- ----------- ----------- ------- -------------------- ------- ------- 2020-08-15 03:11:45.350 INSERT 5 3 9 C E 0 (1 rows affected)
同一トランザクションで複数回同一行を更新した場合の履歴
一つのトランザクションで同じ行を更新する場合、以下のように __$start_lsn で同一トランザクションの履歴であることがわかります。
1> SELECT * FROM TestTable;
2> GO
ColumnA ColumnB ColumnC ColumnD ColumnE ColumnF
----------- ----------- ------- -------------------- ------- -------
3 5 2 AAAA b 1
5 3 9 C E 0
(2 rows affected)
1> BEGIN TRANSACTION;
2> UPDATE TestTable SET ColumnB=1 WHERE ColumnA = 3;
3> UPDATE TestTable SET ColumnB=2 WHERE ColumnA = 3;
4> COMMIT TRANSACTION;
5> GO
(1 rows affected)
(1 rows affected)
1> SELECT * FROM TestTable;
2> GO
ColumnA ColumnB ColumnC ColumnD ColumnE ColumnF
----------- ----------- ------- -------------------- ------- -------
3 2 2 AAAA b 1
5 3 9 C E 0
(2 rows affected)
1> SELECT * FROM cdc.dbo_TestTable_CT;
2> GO
__$start_lsn __$end_lsn __$seqval __$operation __$update_mask ColumnA ColumnB ColumnC ColumnD ColumnE ColumnF __$command_id
---------------------- ---------------------- ---------------------- ------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------- ----------- ------- -------------------- ------- ------- -------------
...
0x00000026000008100004 NULL 0x00000026000008100002 3 0x02 3 5 2 AAAA b 1 1
0x00000026000008100004 NULL 0x00000026000008100002 4 0x02 3 1 2 AAAA b 1 1
0x00000026000008100004 NULL 0x00000026000008100003 3 0x02 3 1 2 AAAA b 1 2
0x00000026000008100004 NULL 0x00000026000008100003 4 0x02 3 2 2 AAAA b 1 2
(10 rows affected)
同一トランザクションで複数のテーブルを更新した場合の履歴
同一トランザクションで複数のテーブルを更新対象とする場合も同様に $__start_lsn が同じになります。
1> CREATE TABLE TestTable2 ( 2> ColumnA INT NOT NULL 3> ); 4> GO 1> EXECUTE sys.sp_cdc_enable_table 2> @source_schema = N'dbo' 3> , @source_name = N'TestTable2' 4> , @role_name = N'cdc_Admin'; 5> GO 1> BEGIN TRANSACTION; 2> UPDATE TestTable SET ColumnB = 3 WHERE ColumnA = 3; 3> INSERT INTO TestTable2 VALUES 4> (4), 5> (5) 6> COMMIT TRANSACTION; 7> GO (1 rows affected) (2 rows affected) 1> SELECT * FROM cdc.dbo_TestTable_CT; 2> SELECT * FROM cdc.dbo_TestTable2_CT; 3> GO __$start_lsn __$end_lsn __$seqval __$operation __$update_mask ColumnA ColumnB ColumnC ColumnD ColumnE ColumnF __$command_id ---------------------- ---------------------- ---------------------- ------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------- ----------- ------- -------------------- ------- ------- ------------- ... 0x0000002700000630001F NULL 0x00000027000006300002 3 0x02 3 2 2 AAAA b 1 1 0x0000002700000630001F NULL 0x00000027000006300002 4 0x02 3 3 2 AAAA b 1 1 (12 rows affected) __$start_lsn __$end_lsn __$seqval __$operation __$update_mask ColumnA __$command_id ---------------------- ---------------------- ---------------------- ------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------- ------------- 0x0000002700000630001F NULL 0x0000002700000630001C 2 0x01 4 2 0x0000002700000630001F NULL 0x0000002700000630001E 2 0x01 5 3 (2 rows affected)
