此文章基于SQL Server 2008 CTP3 (Jun 04, 2007)。
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 [Database for CDC];
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的效果：
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;
将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）。
posted @ 2007-06-10 02:27
Cheney Shue 阅读(2438)
评论(12) 编辑 收藏 网摘 所属分类:
DW / BIDatabase