CDC(Change Data Capture) を開発時に役立てよう

SQL Server

SQL Server 2008 から データの変更を確認する仕組みが2つ追加されました。
変更データキャプチャ(Change Data Capture) と 変更の追跡(Change Tracking) です。


この 2つの違いを簡単に書くと、
変更データキャプチャ：

  • Edition に制限あり(Enterprise, Developer, Evaluation で使用可)
  • 変更されたという記録と、変更されたデータも保持。(履歴データを持っている)

変更の追跡：

  • Edition に制限なし(Express でも使用可)
  • 変更されたという記録しか保持しない。(どう変わったかは保持しない)

詳しくはこちらで
変更データ キャプチャと変更の追跡の比較



今回は、開発の手助けになるような CDC(変更データキャプチャ) の使い方を紹介します。
CDC を使うので、SQL Server Developer Edition を対象とします。(Express はNG)


CDC では、1トランザクション内で同一行に対して複数回更新クエリを発行した場合でも、その全ての変更を保持しています。
ということは比較的大きなストアドや更新クエリを複数回実行するようなデータに対して、

  • 正しくN回更新されているか
  • 何回目の更新で、どのカラムが更新されているか

がとれます。


今回は、1つのテーブルを1トランザクションの間で INSERT/UPDATE/DELETE を行う例で紹介します。
CDC を使うには、SQL Server エージェントが起動している必要があるので、エージェントを起動します。
ここでは SQL Server Management Studio(SSMS) から設定しています。

では、使用するテーブルとデータを投入します。

use [Test]

-- テーブルの作成
create table [テスト] ( 
  [Id] int not null identity(1,1) primary key, 
  [金額] int not null,  
  [値1] nvarchar(10), 
  [値2] nvarchar(10), 
  [値3] nvarchar(10) 
) 

insert into [テスト] values 
(100, null, null, null), 
(200, null, null, null), 
(300, null, null, null), 
(400, null, null, null), 
(500, null, null, null) 
go
select * from [テスト]
go


続いて、CDC の設定を行います。SSMS のテンプレートにクエリが用意されていますので、そちらを使います。
SSMS のテンプレートは、ctrl + alt + T か、メニュー => 表示 => テンプレートエクスプローラー からエクスプローラーを開くことで選択出来ます。
今回は、データベースの全テーブルに対して、CDC を設定するテンプレートを利用しました。

-- CDC の設定
-- ================================
-- Enable Database for CDC Template
-- ================================
EXEC sys.sp_cdc_enable_db
GO

-- ===============================================
-- Enable All Tables of a Database Schema Template
-- ===============================================
DECLARE @source_schema sysname, @source_name sysname
SET @source_schema = N'dbo'
DECLARE #hinstance CURSOR LOCAL fast_forward
FOR
	SELECT name  
	FROM [sys].[tables]
	WHERE SCHEMA_NAME(schema_id) = @source_schema
	AND is_ms_shipped = 0
    
OPEN #hinstance
FETCH #hinstance INTO @source_name
	
WHILE (@@fetch_status <> -1)
BEGIN
	EXEC [sys].[sp_cdc_enable_table]
		@source_schema
		,@source_name
		,@role_name = NULL
		,@supports_net_changes = 1
			
	FETCH #hinstance INTO @source_name
END
	
CLOSE #hinstance
DEALLOCATE #hinstance
GO

これで、システムテーブルに幾つかのテーブルが出来ています。変更履歴を保持するテーブルは、cdc.dbo_テスト_CT という名前になっているはずです。
では、更新クエリを実行します。

-- 更新開始
begin tran

update [テスト] set [値1] = '最初' where [金額] < 300
update [テスト] set [金額] = [金額] + 100 where [金額] <= 400
delete [テスト] where [金額] <= 300
insert [テスト] values (300, '途中で', null, null)
update [テスト] set [値3] = '全部更新'
update [テスト] set [値2] = 'なんか', [値3] = '書き換え' where [金額] = 400

commit 

select * from [テスト]
go


今回は結構適当にクエリを書いたので、最終データがどうなっているかはあまり気にしていません。
それでは、変更履歴を見てみましょう。

-- CDC のテーブル確認
select * from [cdc].[dbo_テスト_CT]
go


今回のテーブルの定義とは別に、左に5列余分なのがついています。今回この中で見るべき列は、__$seqval と __$operation 列です。

  • __$operation は 1:削除 2:挿入 3:変更前 4:変更後 となっています。
  • __$seqval は更新順序でこの値が同じ値は、変更前/変更後 のペアになってます。


CDC を使えば、複数のクエリを実行した際に、この行が何回更新されたのか、何回目の更新で、どのカラムが更新されているかがわかります。
日次/月次の締め処理やら、複雑な在庫の更新等で大きなストアドを実行した際の開発の手助けになるのかなと思います。


その他 のカラムや CDC については興味があれば、こちらで確認してみてください。
変更データ キャプチャの基礎
クエリ全文

use [Test]
-- テーブルの作成
create table [テスト] ( 
  [Id] int not null identity(1,1) primary key, 
  [金額] int not null,  
  [値1] nvarchar(10), 
  [値2] nvarchar(10), 
  [値3] nvarchar(10) 
) 

insert into [テスト] values 
(100, null, null, null), 
(200, null, null, null), 
(300, null, null, null), 
(400, null, null, null), 
(500, null, null, null) 
go
select * from [テスト]
go

-- CDC の設定
-- ================================
-- Enable Database for CDC Template
-- ================================
EXEC sys.sp_cdc_enable_db
GO

-- ===============================================
-- Enable All Tables of a Database Schema Template
-- ===============================================
DECLARE @source_schema sysname, @source_name sysname
SET @source_schema = N'dbo'
DECLARE #hinstance CURSOR LOCAL fast_forward
FOR
	SELECT name  
	FROM [sys].[tables]
	WHERE SCHEMA_NAME(schema_id) = @source_schema
	AND is_ms_shipped = 0
    
OPEN #hinstance
FETCH #hinstance INTO @source_name
	
WHILE (@@fetch_status <> -1)
BEGIN
	EXEC [sys].[sp_cdc_enable_table]
		@source_schema
		,@source_name
		,@role_name = NULL
		,@supports_net_changes = 1
			
	FETCH #hinstance INTO @source_name
END
	
CLOSE #hinstance
DEALLOCATE #hinstance
GO
----

-- 更新開始
begin tran

update [テスト] set [値1] = '最初' where [金額] < 300
update [テスト] set [金額] = [金額] + 100 where [金額] <= 400
delete [テスト] where [金額] <= 300
insert [テスト] values (300, '途中で', null, null)
update [テスト] set [値3] = '全部更新'
update [テスト] set [値2] = 'なんか', [値3] = '書き換え' where [金額] = 400

commit 

select * from [テスト]
go
-- CDC のテーブル確認
select * from [cdc].[dbo_テスト_CT]
go



-- 後片付け(上のクエリから続けて流すと CDC の結果が表示されないと思うので気を付けて！)
-- ===================================================
-- Disable All Capture Instances for a Schema Template
-- ===================================================
DECLARE @capture_instances table (
		source_schema           sysname,    
		source_table            sysname,    
		capture_instance		sysname,	
		object_id			int,		
		source_object_id		int,		
		start_lsn			binary(10),	
		end_lsn			binary(10)	NULL,	
		supports_net_changes	bit,		
		has_drop_pending		bit		NULL,		
		role_name			sysname	NULL,	
		index_name			sysname	NULL,	
		filegroup_name		sysname	NULL,				 
		create_date			datetime,	
		index_column_list		nvarchar(max) NULL, 
		captured_column_list	nvarchar(max)) 

DECLARE @source_schema sysname,
	@source_name sysname,
	@capture_instance sysname

SET @source_schema = N'dbo'

INSERT INTO @capture_instances
EXEC [sys].[sp_cdc_help_change_data_capture]

DECLARE #hinstance CURSOR LOCAL fast_forward
FOR
	SELECT source_table, capture_instance  
	FROM @capture_instances
	WHERE source_schema = @source_schema
    
OPEN #hinstance
FETCH #hinstance INTO @source_name, @capture_instance
	
WHILE (@@fetch_status <> -1)
BEGIN
	EXEC [sys].[sp_cdc_disable_table]
		@source_schema
		,@source_name
		,@capture_instance
			
	FETCH #hinstance INTO @source_name, @capture_instance
END
	
CLOSE #hinstance
DEALLOCATE #hinstance
GO

-- =================================
-- Disable Database for CDC Template
-- =================================
EXEC sys.sp_cdc_disable_db
GO

drop table [テスト]
go