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的用户登录，执行下面的命令：
执行命令后即启用了数据库的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_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的效果：
insert dbo.source_table
values ('ck_1','c1_1','c2_1','c3_1');
insert dbo.source_table
values ('ck_2','c1_2','c2_2','c3_2');
insert dbo.source_table
values ('ck_3','c1_3','c2_3','c3_3');
update dbo.source_table
set column_2='c2_2_c'
where column_key='ck_2';
update dbo.source_table
set column_key='ck_3_c'
where column_key='ck_3';
delete dbo.source_table
where column_key='ck_3_c';
select * from 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 2K8里还有多个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_<capture_instance>函数用于查询实例中满足要求的所有变更记录。cdc.fn_cdc_get_net_changes_<capture_instance>函数用于查询实例中满足要求的净变更记录，所谓的净变更记录既是最后一次DML操作后源表的记录，比如在对一行数据进行了多次update后，使用cdc.fn_cdc_get_all_changes_<capture_instance>会返回所有更新前和更新后的数据记录，而净变更只返回最后一次更新后的记录。
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功能。
小结
CDC是在SQL Server 2K8 CTP3中才加入特性，在前两个CTP中还没有出现过，可以看到还有些不尽人意，没有Oracle那么完善，希望在正式版中能进一步改进。
将cdc.st_instance_ct表中数据列出来后如下：
这个命令指定捕获源表Source_Table的变化，参数说明如下：
USE [Database for CDC];
GO
EXECUTE [sys].[sp_cdc_enable_db_change_data_capture];
GO
