浅析SQL2008的Change Data Capture功能

浅析SQL2008的Change Data Capture功能

　　在常见的企业数据平台管理中有一项任务是一直困扰SQL Server DBA们的，这就是对数据更新的监控。很多数据应用都需要捕获对业务数据表的更新。笔者见过几种解决方案：

　　1、在数据表中加入特殊的标志列；
　　2、 通过在数据表上创建触发器；
　　3、通过第三方产品，例如Lumigent的Log Explorer。

　　其实第1种和第2中方案都不好，因为第1种方法需要在应用程序编码的时候尤为小心，如果有一段数据访问逻辑忘了更新标志位就会导致遗漏某些数据更新，而第2种方法对性能影响过于明显，因为触发器的性能开销是众所周知的。第3种方法其实属于一种叫做Log Audit的方案体系。因为SQL Server同其他关系型数据库一样，所有数据操作都会在日志中记录，因此通过分析日志就可以获得完整的数据操作历史。SQL Server其实早就有内部的API可供ISV开发者中Log Audit的方案，不过微软对这套API控制比较严格，只有签署了一堆协议的核心级合作伙伴才能了解这套API。

　　因此，现对业务数据更新的跟踪在SQL Server平台上一直是一件非常头疼的事情，用户需要在投入大量开发精力和投入额外采购成本之间做出选择。幸运的事，微软终于在SQL Server 2008中提供了一套半公开的Log Audit机制，就是我们所说的Change Data Capture，我们后面简称CDC。

　　CDC的工作原理

　　我们前面说过CDC是通过分析日志获得数据操作历史信息的，那么CDC的工作原理到底是怎么样的呢？下图可以非常贴切地说明这个功能的原理：

图1

　　◆当DML提交到应用数据库时，SQL Server必须写入日志，并在缓存中更新数据，然后在检查点将内存中的数据刷回数据文件。
　　◆CDC的内部进程根据CDC的设置，在日志文件中提取更新历史信息，并将这些个更新信息写入对应的更新跟踪表。
　　◆DBA或开发人员通过调用CDC的函数来访问更新跟踪表，提取感兴趣的更新历史信息，并通过ETL应用程序更新数据仓库。
　　◆理论上面更新跟踪表事会无限制增长的，因此CDC内部有一个清理进程，在默认情况下更新跟踪信息在写入跟踪表三天后会被自动清理。

　　CDC的配置

　　由于CDC是一项比较高端的功能，因此只有在SQL Server 2008的企业版、开发版和评估版中才能找到CDC功能。

　　启用数据库级别的CDC

　　要启用CDC功能，首先需要一个sysadmin服务器角色的成员用户激活数据库级别的CDC，这个过程可以通过sys.sp_cdc_enable_db_change_data_capture存储过程来完成。如果想知道一个数据库是否启用了CDC功能，可以通过查询sys.databases系统目录的is_cdc_enabled字段。

　　当一个数据库启用CDC功能后，SQL Server会自动在这个数据库中创建cdc架构和cdc用户，所有CDC相关的数据表和用户函数都会存放在cdc架构下。

　　CDC功能启用后，SQL Server会首先在cdc架构下创建五张表用于记录一些CDC的原数据，分别是ddl_history，change_tables，captured_columns，index_columns和lsn_time_mapping。

　　在数据库启用了CDC后，接下来我们就需要在数据表上启用CDC了。属于db_owner角色的用户可以通过存储过程sys.sp_cdc_enable_table_change_data_capture来启用对某张数据表的更新跟踪，一张数据表最多可以设置两个跟踪实例。每个跟踪实例中可以设置对原始数据表的所有列或部分列进行更新跟踪。如果想知道数据表是否进行了更新跟踪，DBA可以查询sys.tables系统目录的is_tracked_by_cdc字段。

　　对一张数据表启用CDC跟踪实例后，SQL Server会在cdc架构下创建一张数据表用于记录从日志中解析出来的更新历史信息。

　　一段CDC的评估脚本

　　为了评估CDC功能，我特地写了一段脚本如下：

　　1、首先创建一个测试数据库；

　　2、然后激活TestCDC数据库上的更新捕获功能；

 

　　执行了存储过程sp_cdc_enable_db_change_data_capture后，就会在数据库TestCDC中看到有一些新的表被创建了，分别是ddl_history，change_tables，captured_columns，index_columns和lsn_time_mapping，并且这5张表都是在cdc架构下。

　　3、然后在TestCDC数据库中创建测试表

 

　　4、在dbo.Product表上激活更新跟踪

 

　　　EX
 
　　　EC sp_cdc_enable_table_change_data_capture 'dbo', 
　　　'Product', @role_name= NULL, @supports_net_changes =1;

 

　　成功提交上述命令后，就可以在数据表change_tables，captured_columns和index_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五个新的字段。ITPUB个人空间-hU:i B%P%B&X其实在存储过程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_mapping和dbo_Product_CT中分别看到一条新记录。

　　其中dbo_Product_CT表中的_$operation字段的值是2，_$update_mask字段的值是0x07。 _$operation字段是代表DML操作类型，1是delete，2是insert，3是update的旧值，4是update的新值。
$update_mask字段是表示一个字段列表的掩码，那些在DML操作中被更新了的字段位为1，而没有更新的字段位为0。在本例中Product表一共有三列被跟踪，所以应该是一个三位的二进制数，右边低位第一位是第一列ProductID，低位第二位是第二列ProductName，第三位就是Category了。因为这是一次INSERT，所以更新涉及到了所有的三列，所以_$update_mask字段就应该是0x7了。

　　6、 继续在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是主键，更新主键应该视同一条新的记录。)

　　7、再来一次UPDATE

 

　　　UPDATE dbo.Product SET Category = N'A' WHERE ProductID = 1;

 

　　提交完这条命令后，在dbo_Product_CT中又看到两条新记录了。其中dbo_Product_CT表中的_$operation字段的值是第一条是3，第二条是4，_$update_mask字段的值两条都是0x04。(看来CDC确实会记录下数据的每次修改。)

　　　8、继续在Product表上提交DML语句

 

　　　DELETE dbo.Product WHERE ProductID = 1;

 

　　提交完了这条命令后，就会在lsn_time_mapping和dbo_Product_CT中分别看到一条新记录。

　　其中dbo_Product_CT表中的_$operation字段的值是1，_$update_mask字段的值是0x07。

　　9、提交一个DDL试试看

 

　　　ALTER TABLE dbo.Product ADD Description nvarchar(100);

 

　　提交完这句命令后，只会在ddl_history表中看到一条新的记录。

　　10、然后再试试DML

 

　　　UPDATE dbo.Product SET Description = N'NA';

 

　　提交完这句语句后，所有cdc架构下的表中都没有看到新记录。说明新增的列Description不跟踪更新了......估计有人会说(细心的人哦！)：“这次当然看不到新记录了，因为在前面第7步我们已经删除了所有的记录，因此这次的UPDATE语句没有影响到任何记录，当然CDC的表中不会有任何记录了。”那么到底对Description更新会不会记录呢，经过测试确实是不记录的。

　　那么如果我们想对Description也进行更新跟踪应该怎么办呢？很简单的，由另外一个存储过程叫做sp_cdc_disable_table_change_data_capture可以禁用对某张表的更新跟踪，可以使用这个存储过程先对Product表禁用更新跟踪，然后再重新启用对Product表的更新跟踪就可以了。

　　11、最后试一下DROP命令

 

　　　DROP TABLE dbo.Product;

 

　　dbo.Product表消失了，同时cdc.dbo_Product_CT表也消失了。

　　12. 评估结束。一定有人问，捕获到的更新怎么用呢，还有一堆系统函数和存储过程可以帮助用户，但是那段测试的过程就不详细写了。

　　其中最重要的应该就是cdc.fn_cdc_get_all_changes_和cdc.fn_cdc_get_net_changes_两个函数了，这两个函数可以帮助我们获取dbo_Product_CT表中数据，其中cdc.fn_cdc_get_all_changes_是用于获取所有更新，而cdc.fn_cdc_get_net_changes_则是用于获取精简后的更新，在精简的更新中有一些重复的更新就会被合并成一条记录，比如说我们把产品类型由A改为B，然后又改回A，在cdc.fn_cdc_get_all_changes_中应该有3条记录，而在cdc.fn_cdc_get_net_changes_中则只有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');

第1页:浅析SQL2008的Change Data Capture功能(1)   第2页:浅析SQL2008的Change Data Capture功能(2)