使用SQLServer 2008的CDC功能实现数据变更捕获

概述最近由于工作需要，研究了一下2008 CDC功能，觉得还不错，下面整理了一下研究过程，虽然比较粗略，但是基本上能用了，如果有补充请大家回复，我视情况修改原帖，谢谢。 背景： 在SQLServer2008之前，对数据变更的捕获通常使用触发器、时间戳等低效高成本的功能来实现，所以很多系统都没有做数据变更或者仅仅对核心表做监控。 自从SQLServer2008引入了数据变更捕获功能（后面简称为CDC）后 @H_301_6@最近由于工作需要，研究了一下2008 CDC功能，觉得还不错，下面整理了一下研究过程，虽然比较粗略，但是基本上能用了，如果有补充请大家回复，我视情况修改原帖，谢谢。

背景： @H_301_6@在sqlServer2008之前，对数据变更的捕获通常使用触发器、时间戳等低效高成本的功能来实现，所以很多系统都没有做数据变更或者仅仅对核心表做监控。

自从sqlServer2008引入了数据变更捕获功能（后面简称为CDC）后，解决了很多烦恼问题，如客户2个月前更改了一个数据，但是由于服务器空间不足，经常只能保留一个月的备份。此时客户为了逃避因为更改数据带来的较大负面影响，往往会把责任推给软件开发商或者运维商，如果此时有变更捕获的话，就有证据了，当然分析日志也能实现，但是比较痛苦。
适用环境： @H_301_6@仅在sqlServer2008（含）以后的企业版、开发版和评估版中可用。

@H_301_6@

详解： @H_301_6@CDC功能主要捕获sqlServer指定表的增删改 *** 作，由于任何 *** 作都会写日志（哪怕truncate），所以CDC的捕获来源于日志文件。日志文件会把更改应用到数据文件中，同时也会标记符合要求的数据标记为需要添加跟踪的项。然后通过一些配套函数，最后写入到数据仓库中。大概流程：

@H_301_6@

@H_301_6@

步骤：本文中以：AdventureWorks为例 @H_301_6@

@H_301_6@

@H_301_6@

第一步、对目标库显式启用CDC：
@H_301_6@在当前库使用sys.sp_cdc_enable_db。返回0（成功）或1（失败）。注意，无法对系统数据库和分发数据库启用该功能。且执行者需要用sysadmin角色权限。

@H_301_6@该存储过程的作用域是整个目标库。包含元数据、DDL触发器、cdc架构和cdc用户。

@H_301_6@使用以下代码启用：

USE AdventureWorks GO EXECUTE sys.sp_cdc_enable_db; @H_301_6@GO

@H_301_6@在一开始直接执行时，出现了报错信息：

消息22830，级别16，状态1，过程sp_cdc_enable_db_internal，第193 行 @H_301_6@无法更新元数据来指示已对数据库AdventureWorks 启用了变更数据捕获。执行命令'SetCDCTracked(Value = 1)' 时失败。返回的错误为15517: '无法作为数据库主体执行，因为主体"dbo" 不存在、无法模拟这种类型的主体，或您没有所需的权限。'。请使用此 *** 作和错误来确定失败的原因并重新提交请求。

@H_301_6@这里引出了另外一个知识点：错误号 15517 的错误

@H_301_6@这种错误会在很多地方出现，如还原数据库的时候也会有可能出现。共同点是：某个/些存储过程使用了具有WITHEXECUTE AS 的选项。使其在当前库具有了某个架构，但是当在别的地方执行时，由于没有这个架构，所以就报错，解决方法：

@H_301_6@ALTER AUTHORIZATION ON DATABASE::[AdventureWorks] TO [sa]

@H_301_6@经过检查，uspUpdateEmployeeHireInfo这个存储过程的确有：WITH EXECUTE AS CALLER

@H_301_6@使用sa的原因是即使sa被禁用，sa还是存在的。所以不会报错。

@H_301_6@现在重新执行：

@H_301_6@USE AdventureWorks @H_301_6@GO @H_301_6@EXECUTE sys.sp_cdc_enable_db; @H_301_6@GO

@H_301_6@启用成功，然后通过以下语句检查是否成功：

SELECT  is_cdc_enabled,CASEWHEN is_cdc_enabled=0THEN 'CDC功能禁用'ELSE 'CDC功能启用'END 描述 FROM    sys.databases @H_301_6@WHERE   name = 'AdventureWorks'

@H_301_6@创建成功后，将自动添加CDC用户和CDC架构。

@H_301_6@创建这两个用户、架构的原因是因为CDC要求独占方式使用这两个架构，所以要单独创建。如果存在了非CDC功能创建的CDC用户、架构的话，则需要先删除该cdc命名的架构，才能开启。

第二步、对目标表启用CDC： @H_301_6@使用db_owner角色的成员执行sys.sp_cdc_enable_table为每个需要跟踪的表创建捕获实例。然后通过sys.tables目录视图中的is_tracked_by_cdc列来判断是否创建成功。

@H_301_6@默认情况下会对表的全部列做捕获。如果只需要对某些列做捕获，可以使用@captured_column_List参数指定这些列。

@H_301_6@如果要把更改表放到文件组里的话，最好创建单独的文件组（最起码与源表独立）。

@H_301_6@ 

@H_301_6@如果不想控制访问角色，则@role_name必须显式设置为null。

@[email protected]_cdc_enable_table @H_301_6@    [ @source_schema = ] 'source_schema@H_164_301@', @H_301_6@    [ @source_name = ] 'source_name@H_164_301@', @H_301_6@    [ @role_name = ] 'role_name@H_164_301@' @H_301_6@    [,[ @capture_instance = ] 'capture_instance@H_164_301@' ] @H_301_6@    [,[ @supports_net_changes = ] supports_net_changes@H_164_301@ ] @H_301_6@    [,[ @index_name = ] 'index_name@H_164_301@' ] @H_301_6@    [,[ @captured_column_List = ] 'captured_column_List@H_164_301@' ] @H_301_6@    [,[ @filegroup_name = ] 'filegroup_name@H_164_301@' ] @H_301_6@  [,[ @partition_switch = ] 'partition_switch@H_164_301@' ]

@H_301_6@ 

@H_301_6@例子：

@H_301_6@把HumanResources.Department 这个表开启变更捕获。

USE AdventureWorks GO EXEC sys.sp_cdc_enable_table@source_schema= 'HumanResources',     @source_name = 'Department',@role_name = NulL

@H_301_6@然后查询是否成功：

SELECT  name ,         is_tracked_by_cdc ,         CASE WHEN is_tracked_by_cdc = 0 THEN 'CDC功能禁用'              ELSE 'CDC功能启用'         END 描述 FROM    sys.tables @H_301_6@WHERE   OBJECT_ID= OBJECT_ID('HumanResources.Department')

@H_301_6@对表开启以后，可以在下图中看到多了很多cdc架构开头的表：

启动之后，可以看到sqlServer代理里面的作业，也出现了这两个作业：

@H_301_6@下面列出相关的存储过程：

@[email protected]_cdc_add_job	@H_301_6@说明及例子
@[email protected]_cdc_generate_wrapper_function	@H_301_6@说明及例子
@[email protected]_cdc_change_job	@H_301_6@说明及例子
@[email protected]_cdc_get_captured_columns	@H_301_6@说明及例子
@[email protected]_cdc_cleanup_change_table	@H_301_6@说明及例子
@[email protected]_cdc_get_ddl_history	@H_301_6@说明及例子
@[email protected]_cdc_disable_db	@H_301_6@说明及例子  建议先禁用表，再禁用库
@[email protected]_cdc_help_change_data_capture	@H_301_6@说明及例子
@[email protected]_cdc_disable_table	@H_301_6@说明及例子
@[email protected]_cdc_help_jobs	@H_301_6@说明及例子
@[email protected]_cdc_drop_job	@H_301_6@说明及例子
@[email protected]_cdc_scan	@H_301_6@说明及例子
@[email protected]_cdc_enable_db	@H_301_6@说明及例子
@[email protected]_cdc_start_job	@H_301_6@说明及例子
@[email protected]_cdc_enable_table	@H_301_6@说明及例子
@[email protected]_cdc_stop_job	@H_301_6@说明及例子

@H_301_6@ 

@H_301_6@函数：

@[email protected]_cdc_get_all_changes_<capture_instance>	@H_301_6@说明及例子
@[email protected]_cdc_has_column_changed	@H_301_6@说明及例子
@[email protected]_cdc_get_net_changes_<capture_instance>	@H_301_6@说明及例子
@[email protected]_cdc_increment_lsn	@H_301_6@说明及例子
@[email protected]_cdc_decrement_lsn	@H_301_6@说明及例子
@[email protected]_cdc_is_bit_set	@H_301_6@说明及例子
@[email protected]_cdc_get_column_ordinal	@H_301_6@说明及例子
@[email protected]_cdc_map_lsn_to_time	@H_301_6@说明及例子
@[email protected]_cdc_get_max_lsn	@H_301_6@说明及例子
@[email protected]_cdc_map_time_to_lsn	@H_301_6@说明及例子
@[email protected]_cdc_get_min_lsn	@H_301_6@说明及例子

案例： @H_301_6@下面开始从头到尾做一个实际案例：

@H_301_6@先检查原库的内容：可以看到系统表里面只有一个dbo.sysdiagrams表

@H_301_6@然后看看sqlServer代理：可以看到也没有相关的作业

步骤二：对数据库启用CDC数据库级别功能。必须显式启用： @H_301_6@Step1、执行以下语句：

USE AdventureWorks GO EXEC sys.sp_cdc_enable_db @H_301_6@GO

@H_301_6@某些数据库可能存在一些存储过程包含有：execute as 等语句，此时会报错：

@H_301_6@文字描述：

消息22830，级别16，状态1，过程sp_cdc_enable_db_internal，第186 行 无法更新元数据来指示已对数据库AdventureWorks 启用了变更数据捕获。执行命令'SetCDCTracked(Value = 1)' 时失败。返回的错误为15517: '无法作为数据库主体执行，因为主体"dbo" 不存在、无法模拟这种类型的主体，或您没有所需的权限。'。请使用此 *** 作和错误来确定失败的原因并重新提交请求。 消息266，级别16，状态2，过程sp_cdc_enable_db_internal，第0 行 EXECUTE 后的事务计数指示BEGIN 和COMMIT 语句的数目不匹配。上一计数= 0，当前计数= 1。 消息266，级别16，状态2，过程sp_cdc_enable_db，第0 行 EXECUTE 后的事务计数指示BEGIN 和COMMIT 语句的数目不匹配。上一计数= 0，当前计数= 1。 消息3998，级别16，状态1，第1 行 在批处理结束时检测到不可提交的事务。该事务将回滚。

@H_301_6@如果出现这个错误，目前的解决方法是执行下面语句，原因已在开头说明，对于没有使用EXECUTE AS的库，一般不会有这样的问题：

@H_301_6@ALTER AUTHORIZATION ON DATABASE::[AdventureWorks] TO [sa]

@H_301_6@然后再次执行，就成功开启了：

USE AdventureWorks GO EXEC sys.sp_cdc_enable_db @H_301_6@GO

@H_301_6@现在检查是否成功：

SELECT  is_cdc_enabled ,         CASE WHEN is_cdc_enabled = 0 THEN 'CDC功能禁用'              ELSE 'CDC功能启用'         END 描述 FROM    sys.databases WHERE   name = 'AdventureWorks'

@H_301_6@现在检查表和作业：

@H_301_6@作业没有改变

角色权限中多出了：

步骤三：对某些表开启捕获： @H_301_6@这里选择HumanResources.Department、Person.ADDRESS、Person.Contact 开启：

@H_301_6@先来看开启之前的样子：

@H_301_6@然后对该表开启：

USE AdventureWorks; GO EXECUTE sys.sp_cdc_enable_table     @source_schema = N'HumanResources'   , @source_name = N'Department'   , @role_name = N'cdc_admin'--可以自动创建   , @capture_instance=DEFAulT @H_301_6@GO

@H_301_6@结果如图:

可以看到在创建的同时,也创建了两个作业: cdc.AdventureWorks_cleanup和cdc.AdventureWorks_capture

@H_301_6@也可以看到多了一个角色CDC_admin，是在上面语句中动态创建的：

@H_301_6@按照上面步骤把另外两个表也开启了：

USE AdventureWorks; GO EXECUTE sys.sp_cdc_enable_table     @source_schema = N'Person'   , @source_name = N'ADDRESS'   , @capture_instance=DEFAulT GO EXECUTE sys.sp_cdc_enable_table     @source_schema = N'Person'   , @source_name = N'Contact'   , @capture_instance=DEFAulT GO

@H_301_6@可以从系统表中看到：

@H_301_6@

@H_301_6@

@H_301_6@了3个表，并且是刚才开启CDC功能的表。现在来检查是否开启成功：

SELECT  name ,         CASE WHEN is_tracked_by_cdc = 0 THEN 'CDC功能禁用'              ELSE 'CDC功能启用'         END 描述 FROM    sys.tables WHERE   OBJECT_ID IN( OBJECT_ID('HumanResources.Department'),                        OBJECT_ID('Person.ADDRESS'),                        OBJECT_ID('Person.Contact') )

@H_301_6@结果如下：

@H_301_6@

步骤三：检验： 下面来改动数据：

@H_301_6@然后把表中的数据复制一份：

INSERT  INTO HumanResources.Department         ( name ,           Groupname ,           ModifIEdDate         )         SELECT  name + '1' ,                 Groupname + '1' ,                 GETDATE() ModifIEdDate @H_301_6@        FROM    HumanResources.Department

@H_301_6@结果如下：

然后从 cdc . HumanResources_Department_CT 表查询：

@H_301_6@可以看到的确多了16条记录。这部分记录证明了，有16条数据从监控到目前为止做了改动，现在再来改动一下：

@H_301_6@DELETE  FROM HumanResources.Department WHERE DepartmentID>17

@H_301_6@再查询cdc表，可以看到又多了16条记录：

@H_301_6@现在来做下update的实验：

@H_301_6@UPDATE  HumanResources.Department SET ModifIEdDate=GETDATE()

@H_301_6@再查看：

@H_301_6@现在来分析一下这个表：

@H_301_6@可以在联机丛书上查看：cdc.<capture_instance>_CT   可以看到，这样命名的表，是用于记录源表更改的表。对于insert/delete *** 作，会有对应的一行记录，而对于update，会有两行记录。

@H_301_6@       对于__$operation列：1 = 删除、2= 插入、3= 更新（旧值）、4= 更新（新值）

@H_301_6@    对于__$start_lsn列：由于更改是来源与数据库的事务日志，所以这里会保存其事务日志的开始序列号（LSN）

@H_301_6@       但是微软不检查直接查询这类表，建议使用cdc.fn_cdc_get_all_changes_<捕获实例> 和cdc.fn_cdc_get_net_changes_<capture_instance>  来查询

@H_301_6@ 

下文开始，来熟悉各种函数、存储过程的使用，并尝试一些不正常的 *** 作。

日常使用情景： 1、查询已经开启的捕获实例： @H_301_6@由于可能不记得或者不知道开启了什么表的捕获，所以可以使用以下语句来查找：

--返回所有表的变更捕获配置信息 EXECUTE sys.sp_cdc_help_change_data_capture; @H_301_6@GO

@H_301_6@可以看到以下截图：

@H_301_6@

@H_301_6@查看对某个实例(即表)的哪些列做了捕获监控:

EXEC sys.sp_cdc_get_captured_columns @H_301_6@@capture_instance = 'HumanResources_Department' -- sysname

@H_301_6@得到下图:

@H_301_6@也可以从下面中查找配置信息：

@H_301_6@SELECT * FROM msdb.dbo.cdc_jobs

@H_301_6@如图：

2、查看当前配置使用sp_cdc_help_jobs： @H_301_6@从上文可以看到，启用cdc之后会自动创建了两个作业，可以先使用以下语句来查看：

@H_301_6@sp_cdc_help_jobs

得到的结果：

@H_301_6@对于一个大型的olTP系统，由于数据更改会非常频繁，变更表中的数据会非常多，如果存放过久（最久可以存放100年），那对数据库空间是非常大的挑战。此时可以调整上图中cdc.AdventureWorks_cleanup 中retention（单位：分钟）。

3、修改配置：sp_cdc_change_job：

--显示原有配置： EXEC sp_cdc_help_jobs GO --更改数据保留时间为分钟 EXECUTE sys.sp_cdc_change_job     @job_type = N'cleanup',     @retention=100 GO --停用作业 EXEC sys.sp_cdc_stop_jobN'cleanup' GO --启用作业 EXEC sys.sp_cdc_start_jobN'cleanup' GO --再次查看 EXEC sp_cdc_help_jobs @H_301_6@GO

@H_301_6@得到以下结果：

证明修改成功，此处注意，修改后要先停用（如果已经启用），再启用，才能生效。

4、停止/启用、删除/创建作业： @H_301_6@停止/开始作业，可以使用以下语句：

--停用作业 EXEC sys.sp_cdc_stop_jobN'cleanup' GO --启用作业 EXEC sys.sp_cdc_start_jobN'cleanup' GO

@H_301_6@ 

@H_301_6@删除作业：

EXEC sys.sp_cdc_drop_job@job_type = N'cleanup' -- nvarchar(20) GO --查看作业 EXEC sys.sp_cdc_help_jobs @H_301_6@GO

@H_301_6@可以看到现在只剩下一个作业了:

@H_301_6@创建作业：

EXEC sys.sp_cdc_add_job     @job_type = N'cleanup',     @start_job = 0,     @retention = 5760 --查看作业 EXEC sys.sp_cdc_help_jobs @H_301_6@GO

@H_301_6@下面看到已经创建成功:

5、DDL变更捕获： @H_301_6@CDC除了捕获数据变更之外，还能捕获DDL *** 作的变化。前提是先要确保sqlServer 代理的启用，其实CDC功能都需要确保sql 代理正常运行，因为所有 *** 作都通过代理中的两个作业来实现的。

@H_301_6@现在先来对HumanResources.Department 表修改一下，把name的长度加长：

ALTER table HumanResources.Department ALTER ColUMN name NVARCHAR(120) ; @H_301_6@GO

@H_301_6@然后查询ddl记录表：

SELECT  * @H_301_6@FROM    cdc.ddl_history

@H_301_6@可以看到：

@H_438_2404@

由于在截图之前已经试了几次，所以里面有3条数据，之所以试了几次，就是因为上面所说的，忘了开sql代理，所以查不出数据，所以切记要开启sql代理。

6、使用CDC的函数来获取更改： @H_301_6@A、使用cdc.fn_cdc_get_all_changes_HumanResources_Department 函数报告捕获实例HumanResources_Department 的当前所有可用更改：

DECLARE @from_lsn binary(10), @to_lsn binary(10) SET @from_lsn =    sys.fn_cdc_get_min_lsn('HumanResources_Department') SET @to_lsn   = sys.fn_cdc_get_max_lsn() SELECT * FROM cdc.fn_cdc_get_all_changes_HumanResources_Department   (@from_lsn, @to_lsn, N'all update old'); GO

@H_301_6@B、获取某个时间段的更改信息：

@H_301_6@先根据日志序列号（logsequence number,LSN）来获取跟踪变更数据：

@[email protected]_cdc_map_time_to_lsn获取变更范围内的最大、最小LSN值。可以使用：

@H_301_6@Smallest greater than;smallest greater than orequal;largest less than;largest less than or equal.

@H_301_6@如查询某个时间段插入的数据：

--插入数据 INSERT INTO HumanResources.Department(name,Groupname,ModifIEdDate) VALUES('test','abc',GETDATE()) INSERT INTO HumanResources.Department(name,ModifIEdDate) VALUES('test1','abc1',GETDATE()) go --检查数据 DECLARE @bglsn VARBINARY(10)=sys.fn_cdc_map_time_to_lsn('smallest greater than or equal','2012-10-12 12:00:00.997') DECLARE @edlsn VARBINARY(10)=sys.fn_cdc_map_time_to_lsn('largest less than or equal',GETDATE()) SELECT DepartmentID,name FROM cdc.HumanResources_Department_CT @H_301_6@WHERE [__$operation]=2 AND [__$start_lsn] BETWEEN @bglsn AND @edlsn

@H_301_6@得到以下结果：

@H_301_6@C、sys.fn_cdc_map_lsn_to_time 查询变更时间：

SELECT  [__$operation] ,        CASE [__$operation] WHEN 1 THEN '删除' WHEN 2 THEN '插入' WHEN 3 THEN '更新（捕获的列值是执行更新 *** 作前的值）'        WHEN 4 THEN '更新（捕获的列值是执行更新 *** 作后的值）' END [类型],         sys.fn_cdc_map_lsn_to_time([__$start_lsn]) [更改时间] ,         name ,         DepartmentID ,         Groupname ,         ModifIEdDate FROM    cdc.HumanResources_Department_CT

@H_301_6@结果:

@H_301_6@注意,由于该表刚好有一个modfIEddate字段,所以和更改时间相同.

@H_301_6@ 

@H_301_6@D、获取LSN边界：

SELECT sys.fn_cdc_get_max_lsn()[数据库级别的最大LSN], @H_301_6@sys.fn_cdc_get_min_lsn('cdc.HumanResources_Department_CT')[捕获实例的lsn]

结果如下：

这两个值可以用于上面提到的函数里面用于筛选数据之用。


以上是一边测试一边记录的结果，可能存在很多片面的地方，欢迎拍砖 总结

以上是内存溢出为你收集整理的使用SQLServer 2008的CDC功能实现数据变更捕获全部内容，希望文章能够帮你解决使用SQLServer 2008的CDC功能实现数据变更捕获所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。