网上的CDC教程好多是SQL1008为测试版时写的,有些语句正式版不能用。
在常见的企业数据平台管理中有一项任务是一直困扰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数据库上的更新捕获功能；
USE TestCDC
GO
EXECUTE sys.sp_cdc_enable_db; --启用数据库对CDC的支持
GO
执行了存储过程e后，就会在数据库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表上激活更新跟踪
EXECUTE sys.sp_cdc_enable_table
@source_schema = N'dbo'
, @source_name = N'Product'
, @role_name = NULL
,@supports_net_changes = 1;
GO
完整参数
EXEC sys.sp_cdc_enable_table
@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
变更捕获表使用的文件组
成功提交上述命令后，就可以在数据表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五个新的字段。为源表Source_Table成功建立捕获实例ST_Instance后，系统自动建立了变更捕获表ST_Instance_CT，变更捕获表的命名规则是实例名+CT。
对源表执行DML命令时，每行受到DML影响的数据都会在变更捕获表中保存数据，不同的DML命令在变更捕获表中保存的数据不同。
dbo_Product_CT表
命令类型
每行数据在变更捕获表的保存情况
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实例名+CT的表，表中记录了源表的数据变更历史。但通常要按照一定的要求查询这些变更。
在数据库的CDC构架中，除了变更捕获表外，还可看到有5个在数据库启用CDC时建立的表：
表名
作用
captured_columns
记录所有CDC实例要保存的列
change_tables
记录所有的CDC实例
ddl_history
记录所有源表由DDL产生的变更
index_columns
记录CDC实例使用的唯一索引
lsn_time_mapping
记录日志序列号的时间，每个DML事务都有一个日志序列号
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可以禁用对某张表的更新跟踪，可以使用这个存储过程先对Product表禁用更新跟踪，然后再重新启用对Product表的更新跟踪就可以了EXECUTE sys.sp_cdc_disable_table
@source_schema = N'dbo',
@source_name = N'Product',
@capture_instance = N'dbo_Product';
GO
11、最后试一下DROP命令
DROP TABLE dbo.Product;
dbo.Product表消失了，同时cdc.dbo_Product_CT表也消失了。
除了关联查询这些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_<捕获实例>函数用于查询实例中满足要求的所有变更记录。cdc.fn_cdc_get_net_changes_<capture_instance> 函数用于查询实例中满足要求的净变更记录，所谓的净变更记录既是最后一次DML操作后源表的记录，比如在对一行数据进行了多次update后，使用cdc.fn_cdc_get_all_changes_<捕获实例>会返回所有更新前和更新后的数据记录，而净变更只返回最后一次更新后的记录。
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中加入每日清除变更数据的作业。
捕获和清除作业都是使用默认参数创建的。将立即启动捕获作业。它连续运行，每个扫描周期最多可处理 1000 个事务，并在两个周期之间停顿 5 秒钟。清除作业在每天凌晨 2 点运行一次。它将更改表项保留三天(4320 分钟)，可使用单个删除语句最多删除 5000 项。
停用CDC
EXECUTE sys.sp_cdc_disable_table
@source_schema = N'dbo',
@source_name = N'Product',
@capture_instance = N'dbo_Product';
GO
1. CDC是一个独立的进程的。它是异步地读取日志文件。如果某部分更改没有被进程读到，那么此时日志截断也是没有效果的，很显然需要这样来保证。
2. net_changes是什么意思呢?说的是针对一行记录，如果有多个更改的话，那么以最后的一条为准。
3. 这个更改是不是会永远保存?不会的，它会定期清除的
捕获和清除作业都是使用默认参数创建的。将立即启动捕获作业。它连续运行，每个扫描周期最多可处理 1000 个事务，并在两个周期之间停顿 5 秒钟。清除作业在每天凌晨 2 点运行一次。它将更改表项保留三天(4320 分钟)，可使用单个删除语句最多删除 5000 项。
4. 如果启用了之后，修改了表的结构，会怎么样?
为适应固定列结构更改表，在为源表启用变更数据捕获后，负责填充更改表的捕获进程将忽略未指定进行捕获的任何新列。如果删除了某个跟踪的列，则会为在后续更改项中为该列提供 Null 值。但是，如果现有列更改了其数据类型，则会将更改传播到更改表，以确保捕获机制没有导致跟踪的列发生数据丢失。捕获进程还会将检测的跟踪表列结构的任何更改发送到 cdc.ddl_history 表。如果使用者希望得到下游应用程序中可能需要进行的调整的通知，请使用 sys.sp_cdc_get_ddl_history 存储过程。
CREATE PROC GetCDCResult
(@begin_time DATETIME,@end_time DATETIME)
AS
DECLARE @from_lsn binary(10), @to_lsn binary(10);
SELECT @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time);
SELECT @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time);
SELECT * FROM cdc.dbo_FactInternetSales_CT WHERE __$start_lsn BETWEEN @from_lsn AND @to_lsn
--调用该存储过程
EXEC GetCDCResult '2009-4-27','2009-4-29'
--撤销CDC
EXEC sys.sp_cdc_disable_table 'dbo',
'FactInternetSales','All'
EXEC sys.sp_cdc_disable_db
以下部分未验证！
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');
CDC功能的IO开销
很明显，CDC功能是会产生一定的IO和存储开销的，为了评估CDC功能产生的这些开销。我又进行了一段评测。
整个评估的思路是这样的：
1、创建两个数据库
2、在两个数据库中分别创建一张结构完全相同的表，一个数据库启用CDC功能，而另外一个禁用CDC功能
3、向两张表中写入相同行数的数据
4、视图sys.dm_io_virtual_file_stats来获得两个数据库文件上的
5、利用sysindexes来获得两个数据库中数据表的存储消耗情况
因为是在虚拟机中进行的测试，所以选取了比较小的数据表(AdventureWorks数据库中的SalesOrderDetails)，大约有12万行数据。
评估的结果如下：
图2
从上面两张表中可以看到，CDC激活后日志文件的读会显著增加。原因是CDC在写更新跟踪表时，需要读取日志。
图3
图4
从上面两张表中可以看到激活CDC后数据文件的写入和日志文件写入都会显著增加，不过需要考虑到CDC激活后会需要多写一张表，在本例中就是dbo_SalesOrderDetails_CT，所以这种增加是可以理解的。当然在生产环境中并不会对数据表的所有列进行CDC监控，所以激活CDC对IO写入的影响还需要针对不同情况进行分析。图5
从上面这张图可以看出，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._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、原始数据表的聚簇索引为1个整型字段，同时需要监控的字段总尺寸为50字节(约为5个decimal(19)或5个char(10))。2、对原始表提交100,000个事务。
3、产生1,000,000行次数据操作，其中UPDATE占60%，INSERT和DELETE占40%。
4、那么最终CDC产生的额外数据存储空间应该为(39 4 50) * (1000000 * 1.2 1000000 * 0.4) 48 * 100000 = 153,600,000个字节，约为164MB(假定数据页填充率为90%)。
5、因此约合200MB左右。
经过这样的对比我们可以知道，CDC在生产环境特别是OLTP环境对存储空间的影响不算太明显的，当然这个还要取决于DBA在原始数据表上选取多少字段进行监控，以及这些字段的数据尺寸，同时还有原始数据表的聚簇索引键值密度。另外需要说明的是表1和表2都是由一个异步的进程通过读取日志来完成的，因此表1和表2的数据刷新和原始数据表的刷新会有一定的延时。
对部署CDC的建议ITPUB个人空间，经过以上测试，我们可以发现以下情况：
◆CDC激活会显著增加日志文件的读操作。◆CDC激活后更新跟踪表会产生额外的写入，并消耗存储空间。
◆CDC激活后，原数据表的聚簇索引尺寸会影响到CDC产生的IO数据量，而原始数据表上的非聚簇索引则不会。
◆CDC激活后，被选定进行更新跟踪的列键值属性同样会影响到CDC产生的IO数据量和存储空间。ITPUB个人空间
因此如同微软建议的一样，在CDC激活的环境下，应该将更新跟踪表写入与原始表不同的文件组并存放在不同的存储设备上，注意控制需要监控的数据列尺寸，同时应该注意为日志文件选取可提高读取性能的存储硬件上，比如RAID10。
扫一扫
4424
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
