在SQL Server 2008中实现CDC

change data capture是在每次对数据库执行insert、update、delete操作的时候，捕获变更数据的方法，这在数据仓库中是常用到的技术手段。Oracle早在9i中就加入了这一特性，在多年后的今天，SQL Server总算是提供了同样的功能。

　　CDC的原理是每次对源表(Source Tables)执行insert、update、delete时，数据库事务日志会记录DML造成的变更数据，然后捕获处理过程将日志中源表的变更数据写入变更捕获表(Change Tables)，最后ETL工具使用CDC查询函数将变更数据抽取到数据仓库。

　　相比起在源表建立促发器，CDC对源表事务性能影响小，而且可以获取变更元数据。

　　这里示范一个CDC过程

　　第一步 启用数据库的SQL Server Agent。

　　第二步 建立一个数据库Database for CDC。

　　第三步 然后以服务角色sysadmin的用户登录，执行下面的命令：

　　USE[DatabaseforCDC];
GO
EXECUTE[sys].[sp_cdc_enable_db_change_data_capture];
GO

　　执行命令后即启用了数据库的CDC特性。启用CDC特性后系统会自动建立名为CDC的构架和用户，并建立了几个用于CDC的数据表。

　　第四步 建立源表Source_Table，且将column_1列作为唯一索引IX_Source_Table

　　第五步 执行命令：

　　EXEC sys.sp_cdc_enable_table_change_data_capture

　　@source_schema = 'dbo'

　　, @source_name = 'Source_Table'

　　, @role_name = 'change_table_user1'

　　, @index_name = 'IX_Source_Table'

　　, @capture_instance = 'ST_Instance'

　　, @supports_net_changes = 1

　　, @captured_column_list = 'column_key, column_1, column_2'

　　, @filegroup_name = 'PRIMARY';

　　这个命令指定捕获源表Source_Table的变化，参数说明如下：

参数	说明
source_schema	源表的构架名
source_name	源表名
role_name	获取变化数据的数据库角色，如果角色不存在，sp_cdc_enable_table_change_data_capture过程执行成功后会创建角色
index_name	唯一索引名，不是必须的，如果源表没有主键可以指定唯一索引以确定哪一行数据是变更数据
capture_instance	捕获实例，一个源表最多可以有两个实例
supports_net_changes	是否捕捉净变化数据，如果是的话，源表必须有主键或者指定唯一标识列
captured_column_list	需要在变更捕获表中保存的列
filegroup_name	变更捕获表使用的文件组

　　为源表Source_Table成功建立捕获实例ST_Instance后，系统自动建立了变更捕获表ST_Instance_CT，变更捕获表的命名规则是实例名+CT。

　　对源表执行DML命令时，每行受到DML影响的数据都会在变更捕获表中保存数据，不同的DML命令在变更捕获表中保存的数据不同。

命令类型	每行数据在变更捕获表的保存情况
insert	保存插入行的数据
delete	保存删除行的数据
update	保存两行数据，一行是更新前的，一行是更新后的

　　可以看到，变更捕获表中除了我们需要保存源表的三个列外，还多出了5个以”__$”开头的列，用于记录元数据。

列名	说明
__$start_lsn	日志中序列号，在对源表执行DML时，每个事务有一个日志序列号
__$end_lsn	空，不使用
__$seqval	在一个事务中，DML影响行的序列号
__$operation	识别执行的是何种DML，1=delete，2=insert，3=update（更新前），4=update（更新后）
__$update_mask	用2进制标识哪一列发生变更

　　可以先试着执行下面的命令，以检验CDC的效果：

　　insertdbo.source_table
values('ck_1','c1_1','c2_1','c3_1');
insertdbo.source_table
values('ck_2','c1_2','c2_2','c3_2');
insertdbo.source_table
values('ck_3','c1_3','c2_3','c3_3');
updatedbo.source_table
setcolumn_2='c2_2_c'
wherecolumn_key='ck_2';
updatedbo.source_table
setcolumn_key='ck_3_c'
wherecolumn_key='ck_3';
deletedbo.source_table
wherecolumn_key='ck_3_c';
select*fromcdc.st_instance_ct;

　　将cdc.st_instance_ct表中数据列出来后如下：

	__$start_lsn	__$end_lsn	__$seqval	__$operation	__$update_mask	column_key	column_1	column_2
1	0x0000002D000001A1002C	NULL	0x0000002D000001A10012	2	0x07	ck_1	c1_1	c2_1
2	0x0000002D000001A90004	NULL	0x0000002D000001A90002	2	0x07	ck_2	c1_2	c2_2
3	0x0000002D000001AB0004	NULL	0x0000002D000001AB0002	2	0x07	ck_3	c1_3	c2_3
4	0x0000002E0000007E0022	NULL	0x0000002E0000007E0020	3	0x04	ck_2	c1_2	c2_2
5	0x0000002E0000007E0022	NULL	0x0000002E0000007E0020	4	0x04	ck_2	c1_2	c2_2_c
6	0x0000002E000000830007	NULL	0x0000002E000000830002	3	0x01	ck_3	c1_3	c2_3
7	0x0000002E000000830007	NULL	0x0000002E000000830002	4	0x01	ck_3_c	c1_3	c2_3
8	0x0000002E0000008A0006	NULL	0x0000002E0000008A0004	1	0x07	ck_3_c	c1_3	c2_3

　　其中__$update_mask是受影响的列，对insert和delete来说，影响全部的列，所以值是111(十进制的7);对column_key更新时，值是001(十进制1);对column_2更新时，值是100(十进制4)。

　　变更数据查询

　　变更捕获表是那些CDC实例名+CT的表，表中记录了源表的数据变更历史。但通常要按照一定的要求查询这些变更。

　　在数据库的CDC构架中，除了变更捕获表外，还可看到有5个在数据库启用CDC时建立的表：

表名	作用
captured_columns	记录所有CDC实例要保存的列
change_tables	记录所有的CDC实例
ddl_history	记录所有源表由DDL产生的变更
index_columns	记录CDC实例使用的唯一索引
lsn_time_mapping	记录日志序列号的时间，每个DML事务都有一个日志序列号

　　除了关联查询这些CDC表外，SQL Server 2008里还有多个CDC函数和储存过程，用于查询变更数据。

　　1. 日志序列号与事务时间的变换

　　在change tables中没有记录事务发生的时间，只记录了事务的日志序列号(lsn)，而日志序号号对应的时间记录在lsn_time_mapping表中。sys.fn_cdc_map_lsn_to_time和sys.fn_cdc_map_time_to_lsn是两个用于转换日志序列号与事务时间的函数;sys.fn_cdc_map_time_to_lsn用于获取某一时间段内的所有日志序列号。

　　2. 最小和最大日志序列号

　　sys.fn_cdc_get_min_lsn和 sys.fn_cdc_get_max_lsn函数获得目前存在的最大和最小日志序列号。

　　3. 查询变更数据

　　cdc.fn_cdc_get_all_changes_函数用于查询实例中满足要求的所有变更记录。cdc.fn_cdc_get_net_changes_函数用于查询实例中满足要求的净变更记录，所谓的净变更记录既是最后一次DML操作后源表的记录，比如在对一行数据进行了多次update后，使用cdc.fn_cdc_get_all_changes_会返回所有更新前和更新后的数据记录，而净变更只返回最后一次更新后的记录。

　　4. 获取变更列

　　在对源表进行update操作后，有时需要知道更新的是哪一列。在变更捕获表中__$update_mask字段保存变更列的2进制编码。sys.fn_cdc_is_bit_set用于返回列序的二进制值，比如要知道第3 列是否变更，使用sys.fn_cdc_is_bit_set( 3, __$update_mask )，若返回1，则表明第3列变更，返回0，则表明没有变更。另外要知道实例中的列是第几列，可使用sys.fn_cdc_get_column_ordinal函数。

　　5. 获取源表DDL变更历史

　　sys.sp_cdc_get_ddl_history函数用于查询对源表使用数据定义语句的历史，通常在用DDL改变源表时，也要使用同样的DLL改变变更捕获表。比如删除源表中某一列，或者将某一列的值类型由int改成long，那么变更捕获表也要跟着变化。

　　变更数据的清理

　　变更捕获表中数据要周期性的加载到数据仓库中，被加载后的数据就要清理掉，否则用于cdc的数据会越来越多。使用sys.sp_cdc_cleanup_change_table存储过程清除变更数据。此外，在启用数据库CDC时，系统自动在SQL Server Agent中加入每日清除变更数据的作业。

　　停用CDC

　　sys.sp_cdc_disable_table_change_data_capture存储过程用于停用CDC实例。sys.sp_cdc_disable_db_change_data_capture存储过程用于停用数据库CDC功能。