SQL Server 2008 CDC尝鲜记

SQL Server 2008有一项新功能叫做“数据更新捕获”(简称CDC)。这项功能主要是为了帮助用户捕获SQL Server中的数据更新操作。CDC功能对于在数据仓库中进行ETL时评估那些数据需要增量抽取这一工作有极大的帮助,我们将可以设计自己的增量ETL方案,而不在需要使用触发器、HASH列或者SSIS中那个慢悠悠的SCD处理模块了。(SCD处理模块实质上应该也采用创建HASH列来计算那些新数据行和更新数据行的。)

下面是用于评估SQL Server 2008 CDC的一段脚本

  1. 首先创建一个测试数据库
    USE master
    GO
    CREATE DATABASE TestCDC
    GO
    这样我们就得到了一个空白的测试数据库
  2. 然后激活TestCDC数据库上的更新捕获功能
    USE TestCDC
    GO
    EXEC sp_cdc_enable_db_change_data_capture;
    GO
    执行了存储过程sp_cdc_enable_db_change_data_capture后,就会在数据库TestCDC中看到有一些新的表被创建了,分别是ddl_historychange_tablescaptured_columnsindex_columnslsn_time_mapping,并且这5张表都是在cdc架构下。
  3. 然后在TestCDC数据库中创建测试表
    USE TestCDC
    GO
    CREATE TABLE dbo.Product (
              ProductID int PRIMARY KEY NOT NULL,
              ProductName nvarchar(100),
              Category nvarchar(50))
    GO
  4. dbo.Product表上激活更新跟踪
    EXEC sp_cdc_enable_table_change_data_capture ‘dbo’, ‘Product’, @role_name= NULL, @supports_net_changes =1;
    成功提交上述命令后,就可以在数据表change_tablescaptured_columnsindex_columns表中看到相应的记录,其中change_table中一条,capture_column中三条,index_columns中一条。同时cdc架构下有增加了一张新表叫做dbo_Product_CT,这张表的结构和Product表的结构有点相似,Product表中的三列在dbo_Product_CT中都有,同时dbo_Product_CT表中还增加了_$start_lsn,_$end_lsn,_$seqval,_$operation和_$update_mask五个新的字段。
    其实在存储过程sp_cdc_enable_table_change_data_capture中有一系列的参数,在这里我们为了简化忽略了一个参数就是@captured_column_list,这个参数可以对表中特定的某些字段启用更新跟踪。
  5. 在Product表上提交INSERT语句
    INSERT INTO dbo.Product VALUES (1, N’ABC’, N’A’);
    提交完了这条命令后,就会在lsn_time_mappingdbo_Product_CT中分别看到一条新记录。
    其中dbo_Product_CT表中的_$operation字段的值是2,_$update_mask字段的值是0x07。
  6. _$operation字段是代表DML操作类型,1是delete,2是insert,3是update的旧值,4是update的新值。
    _$update_mask字段是表示一个字段列表的掩码,那些在DML操作中被更新了的字段位为1,而没有更新的字段位为0。在本例中Product表一共有三列被跟踪,所以应该是一个三位的二进制数,右边低位第一位是第一列ProductID,低位第二位是第二列ProductName,第三位就是Category了。因为这是一次INSERT,所以更新涉及到了所有的三列,所以_$update_mask字段就应该是0x7了。

  7. 继续在Product表上提交UPDATE语句
    UPDATE dbo.Product SET Category = N’B’ WHERE ProductID = 1;
    提交完这条命令后,当然也会在lsn_time_mapping和dbo_Product_CT中看到新记录了。不过这次lsn_time_mapping中是一条,而dbo_Product_CT中则是两条。(为什么会这样呢?建议大家自己试一下咯,一试就明白了。)
    其中dbo_Product_CT表中的_$operation字段的值是第一条是3,第二条是4,_$update_mask字段的值两条都是0x04。
    在这次操作中我们更新的是第三列,所以_$update_mask字段就应该是0x4了。(如果我们更新的是ProductID会发现_$update_mask并非是0x1,而同样是0x7,这估计是因为ProductID是主键,更新主键应该视同一条新的记录。)
  8. 再来一次UPDATE
    UPDATE dbo.Product SET Category = N’A’ WHERE ProductID = 1;
    提交完这条命令后,在dbo_Product_CT中又看到两条新记录了。其中dbo_Product_CT表中的_$operation字段的值是第一条是3,第二条是4,_$update_mask字段的值两条都是0x04。(看来CDC确实会记录下数据的每次修改。)
  9. 继续在Product表上提交DML语句
    DELETE dbo.Product WHERE ProductID = 1;
    提交完了这条命令后,就会在lsn_time_mappingdbo_Product_CT中分别看到一条新记录。
    其中dbo_Product_CT表中的_$operation字段的值是1,_$update_mask字段的值是0x07。
  10. 提交一个DDL试试看
    ALTER TABLE dbo.Product ADD Description nvarchar(100);
    提交完这句命令后,只会在ddl_history表中看到一条新的记录。
  11. 然后再试试DML
    UPDATE dbo.Product SET Description = N’NA’;
    提交完这句语句后,所有cdc架构下的表中都没有看到新记录。说明新增的列Description不跟踪更新了……估计有人会说(细心的人哦!):“这次当然看不到新记录了,因为在前面第7步我们已经删除了所有的记录,因此这次的UPDATE语句没有影响到任何记录,当然CDC的表中不会有任何记录了。”那么到底对Description更新会不会记录呢,经过测试确实是不记录的。
    那么如果我们想对Description也进行更新跟踪应该怎么办呢?很简单的,由另外一个存储过程叫做sp_cdc_disable_table_change_data_capture可以禁用对某张表的更新跟踪,可以使用这个存储过程先对Product表禁用更新跟踪,然后再重新启用对Product表的更新跟踪就可以了。
  12. 最后试一下DROP命令
    DROP TABLE dbo.Product;
    dbo.Product表消失了,同时cdc.dbo_Product_CT表也消失了。
  13. 尝试结束,哦哦,对了。一定有人问,捕获到的更新怎么用呢,还有一堆系统函数和存储过程可以帮助用户,但是那段测试的过程就不写了。
    其中最重要的应该就是cdc.fn_cdc_get_all_changes_<capture_instance>cdc.fn_cdc_get_net_changes_<capture_instance>两个函数了,这两个函数可以帮助我们获取dbo_Product_CT表中数据,其中cdc.fn_cdc_get_all_changes_<capture_instance>是用于获取所有更新,而cdc.fn_cdc_get_net_changes_<capture_instance>则是用于获取精简后的更新,在精简的更新中有一些重复的更新就会被合并成一条记录,比如说我们把产品类型由A改为B,然后又改回A,在cdc.fn_cdc_get_all_changes_<capture_instance>中应该有3条记录,而在cdc.fn_cdc_get_net_changes_<capture_instance>中则只有1条记录。两个函数的范例如下(你会发现精简结果集的函数运算相当慢,至少在CTP4中是这样的,不知道以后的版本会不回有改进):
    SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Product(0x00000048000001760004, 0x00000048000001F70004, ‘all’);
    SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_Product(0x00000048000001760004, 0x00000048000001F70004, ‘all’);
Advertisements
Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: