文/DrillChina  出处/IT168

    在常见的企业数据平台管理中有一项任务是一直困扰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的工作原理到底是怎么样的呢？下图可以非常贴切地说明这个功能的原理：


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

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

友情提示：此文并不表示本站肯定持有相同观点，转载请注明出处。

您可能对 [SQL Server] 的这些文章也感兴趣： .Net下调用SqlServer2k存储过程  在SQL Server 2005中构造模糊查找包 MicroSoft SQLServer 2005 数据库镜像  保护 SQL Server 2005 Express Edition Server(1) SQL Server COALESCE()函数的创新应用  SQLSERVER中数据行所占用的最小空间 微软SQL Server 2008速成版本系列发布  微软推预览版SQL Server 2005 SQL Server 7.0数据库的六种数据移动方法  SQL Server 补丁版本的检查与常见问题 在Microsoft SQL Server 2000数据仓库中使用分区  SQL Server数据库的集群设计 SQL Server静态页面导出技术1  Sql数据库MDF数据文件数据库恢复 使用SQL Server 将现有代码作为Web 服务提供  轻松掌握设计 Sql Server触发器的原理 在Linux下访问MS SQL Server数据库  关于MSSQL Server中DATETIME类型数据的处理 SQL Server静态页面导出技术2  用SQLServer2000索引视图提高性能(下)

一段CDC的评估脚本
    为了评估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_history，change_tables，captured_columns，index_columns和lsn_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_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五个新的字段。
其实在存储过程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_<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');


CDC功能的IO开销

    很明显，CDC功能是会产生一定的IO和存储开销的，为了评估CDC功能产生的这些开销。我又进行了一段评测。
整个评估的思路是这样的：
1. 创建两个数据库
2. 在两个数据库中分别创建一张结构完全相同的表，一个数据库启用CDC功能，而另外一个禁用CDC功能
3. 向两张表中写入相同行数的数据
4. 利用动态管理视图sys.dm_io_virtual_file_stats来获得两个数据库文件上的IO
5. 利用sysindexes来获得两个数据库中数据表的存储消耗情况
    因为是在虚拟机中进行的测试，所以选取了比较小的数据表(AdventureWorks数据库中的SalesOrderDetails)，大约有12万行数据。

    评估的结果如下：


    从上面两张表中可以看到，CDC激活后日志文件的读会显著增加。原因是CDC在写更新跟踪表时，需要读取日志。

从上面两张表中可以看到激活CDC后数据文件的写入和日志文件写入都会显著增加，不过需要考虑到CDC激活后会需要多写一张表，在本例中就是dbo_SalesOrderDetails_CT，所以这种增加是可以理解的。当然在生产环境中并不会对数据表的所有列进行CDC监控，所以激活CDC对IO写入的影响还需要针对不同情况进行分析。



    从上面这张图可以看出，CDC激活后会生产数据表不会消耗更多的存储空间，但是更新跟踪表会需要俄外的存储空间。另外可以发现的一点是，在本例中dbo_SalesOrderDetail_CT表消耗的空间比SalesOrderDetail表多，这是因为在dbo_SalesOrderDetail_CT表中加入了一些额外的字段，例如_$start_lsn和_$end_lsn，同时注意观察dbo_SalesOrderDetail_CT表会发现，SQL Server在这张表上使用_$start_lsn、_$end_lsn和_$seqval三个字段作为聚簇索引，而SalesOrderDetail表上原来的聚簇索引(SalesOrderID，SalesOrderDetailID)再加上_$start_lsn、_$end_lsn和_$seqval三个字段则被创建为一个非聚簇索引，所以这就导致了dbo_SalesOrderDetail_CT表需要消耗比原始表更多的空间，不过原始数据表上的非聚簇索引不会在CDC跟踪表上被创建，这也就说明了原始数据表聚簇索引的大小也会对CDC引发的IO产生影响。

CDC对存储的消耗

    为了进一步理解CDC功能对存储的消耗，特别整理了一下CDC的数据开销。首先CDC功能对数据库存储空间产生显著影响的两张表是cdc.<capture_instance>_CT表和cdc.lsn_time_mapping表，这里简称为表1和表2。

下面是对表1和表2作的一些较为深入地剖析：

  1. 表1和表2的数据
  • 表1主要由3个binary(10)字段、1个int字段、1个varbinary(128)字段以及所有被选定更新跟踪的原始表字段构成。因此表1每行数据的尺寸大概是在30 + 4 + 5 (因为通常一张表需要监控的字段会在16个以内，所以暂定为2bytes的binary然后加上varbinary数据2个bytes的固定开销)，也就是 39 + x(假定原始表需要监控的字段键总尺寸为x个字节)个字节。

  • 表2则有1个binary(10)字段、2个datetime字段和1个varbinary(10)字段构成。因此表2每行数据应该是20 + 16 + 12 = 48个字节。

    2. 表1和表2的索引(这个不太好估算，因为不同的表聚簇索引的键值密度是不一样的，一般按照1/4的数据尺寸估算，只有多没有少啦)

    • 表1的3个binary(10)字段构成了聚簇索引，同时3个binary(10)字段加上原始数据表的聚簇索引构成一个非聚簇索引，同上面一样，我们假定原始表聚簇索引键是x个字节，那么表1的非聚簇索引每行是(30 + y(假定原始表聚簇索引键尺寸为y个字节) + 4(指向聚簇索引的内部指针))个字节。

    • 而表2中的binary(10)字段构成了聚簇索引，其中1个datetime字段构成了非聚簇索引。因此表2的非聚簇索引每行是8 + 4 = 12个字节。

    3. 对原始数据表的一行数据进行UPDATE操作，会在表1中添加2行数据，而DELETE操作和INSERT操作则会增加1行数据；而对于表2则是每笔事务增加1行数据。

  因此我们作如下假定，典型的OLTP环境：

    • 原始数据表的聚簇索引为1个整型字段，同时需要监控的字段总尺寸为50字节(约为5个decimal(19)或5个char(10))
    • 对原始表提交100,000个事务
    • 产生1,000,000行次数据操作，其中UPDATE占60%，INSERT和DELETE占40%
    • 那么最终CDC产生的额外数据存储空间应该为(39 + 4 + 50) * (1000000 * 1.2 + 1000000 * 0.4) + 48 * 100000 = 153,600,000个字节，约为164MB(假定数据页填充率为90%)。
    • CDC产生的额外的非聚簇索引存储空间按照数据尺寸的1/4估算，大概是40MB左右。
    • 因此约合200MB左右。

    经过这样的对比我们可以知道，CDC在生产环境特别是OLTP环境对存储空间的影响不算太明显的，当然这个还要取决于DBA在原始数据表上选取多少字段进行监控，以及这些字段的数据尺寸，同时还有原始数据表的聚簇索引键值密度。另外需要说明的是表1和表2都是由一个异步的进程通过读取日志来完成的，因此表1和表2的数据刷新和原始数据表的刷新会有一定的延时。

对部署CDC的建议

  经过以上测试，我们可以发现以下情况：
  • CDC激活会显著增加日志文件的读操作。
  • CDC激活后更新跟踪表会产生额外的写入，并消耗存储空间。
  • CDC激活后，原数据表的聚簇索引尺寸会影响到CDC产生的IO数据量，而原始数据表上的非聚簇索引则不会。
  • CDC激活后，被选定进行更新跟踪的列键值属性同样会影响到CDC产生的IO数据量和存储空间。
  因此如同微软建议的一样，在CDC激活的环境下，应该将更新跟踪表写入与原始表不同的文件组并存放在不同的存储设备上，注意控制需要监控的数据列尺寸，同时应该注意为日志文件选取可提高读取性能的存储硬件上，比如RAID10。