SQLServer 2008 CDC实现数据变更捕获使用图文详解

原作者: [db:作者] 来自: [db:来源] 收藏邀请

适用环境：

仅在SQLServer2008（含）以后的企业版、开发版和评估版中可用。

详解：

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

步骤：本文中以：AdventureWorks为例

第一步、对目标库显式启用CDC：

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

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

使用以下代码启用：

复制代码

代码如下:

USE AdventureWorks GO EXECUTE sys.sp_cdc_enable_db;
GO

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

消息22830，级别16，状态1，过程sp_cdc_enable_db_internal，第193 行

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

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

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

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

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

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

现在重新执行：

复制代码

代码如下:

USE AdventureWorks GO EXECUTE sys.sp_cdc_enable_db; GO

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

复制代码

代码如下:

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

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

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

第二步、对目标表启用CDC：

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

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

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

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

复制代码

代码如下:

sys.sp_cdc_enable_table [ @source_schema = ] 'source_schema', [ @source_name = ] 'source_name' , [ @role_name = ] 'role_name' [,[ @capture_instance = ] 'capture_instance' ] [,[ @supports_net_changes = ] supports_net_changes ] [,[ @index_name = ] 'index_name' ] [,[ @captured_column_list = ] 'captured_column_list' ] [,[ @filegroup_name = ] 'filegroup_name' ] [,[ @partition_switch = ] 'partition_switch' ]

例子：

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

复制代码

代码如下:

USE AdventureWorks GO EXEC <a href="mailto:sys.sp_cdc_enable_table@source_schema='HumanResources'">sys.sp_cdc_enable_table@source_schema='HumanResources'</a>, @source_name = 'Department',@role_name= NULL

然后查询是否成功：

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

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

案例：

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

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

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

步骤二：对数据库启用CDC数据库级别功能。必须显式启用：

Step1、执行以下语句：

复制代码

代码如下:

USE AdventureWorks GO EXEC sys.sp_cdc_enable_db GO

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

文字描述：

消息22830，级别16，状态1，过程sp_cdc_enable_db_internal，第186 行

消息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 行

在批处理结束时检测到不可提交的事务。该事务将回滚。

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

ALTER AUTHORIZATIONON DATABASE::[AdventureWorks]TO [sa]

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

复制代码

代码如下:

USE AdventureWorks GO EXEC sys.sp_cdc_enable_db GO

现在检查是否成功：

复制代码

代码如下:

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

现在检查表和作业：

作业没有改变

角色权限中多出了：

步骤三：对某些表开启捕获：

这里选择HumanResources.Department、Person.ADDRESS、Person.Contact开启：

先来看开启之前的样子：

然后对该表开启：

复制代码

代码如下:

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 GO

结果如图:

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

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

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

复制代码

代码如下:

</div> <div> USE AdventureWorks; GO EXECUTE sys.sp_cdc_enable_table @source_schema= N'Person' , @source_name= N'ADDRESS' , @role_name= N'cdc_Admin'--可以自动创建 , @capture_instance=DEFAULT GO EXECUTE sys.sp_cdc_enable_table @source_schema= N'Person' , @source_name= N'Contact' , @role_name= N'cdc_Admin'--可以自动创建 , @capture_instance=DEFAULT GO

可以从系统表中看到：

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

复制代码

代码如下:

</div> SELECT name , is_tracked_by_cdc , 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'))

结果如下：

步骤三：检验：

下面来改动数据：

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

复制代码

代码如下:

INSERT INTO HumanResources.Department (Name , GroupName , ModifiedDate ) SELECT Name + '1' , GroupName + '1' , GETDATE()ModifiedDate FROM HumanResources.Department

结果如下：

然后从cdc.HumanResources_Department_CT 表查询：

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

DELETE FROM HumanResources.DepartmentWHERE DepartmentID>17

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

现在来做下update的实验：

UPDATE HumanResources.DepartmentSET ModifiedDate=GETDATE()

再查看：

现在来分析一下这个表：

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

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

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

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

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

日常使用情景：1、查询已经开启的捕获实例：

由于可能不记得或者不知道开启了什么表的捕获，所以可以使用以下语句来查找：

--返回所有表的变更捕获配置信息

复制代码

代码如下:

EXECUTE sys.sp_cdc_help_change_data_capture; GO

可以看到以下截图：

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

复制代码

代码如下:

</div> <div> EXEC sys.sp_cdc_get_captured_columns @capture_instance ='HumanResources_Department' -- sysname

得到下图:

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

SELECT * FROM msdb.dbo.cdc_jobs

如图：

2、查看当前配置使用sp_cdc_help_jobs：

从上文可以看到，启用cdc之后会自动创建了两个作业，可以先使用以下语句来查看：

sp_cdc_help_jobs

得到的结果：

对于一个大型的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 GO

得到以下结果：

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

4、停止/启用、删除/创建作业：

停止/开始作业，可以使用以下语句：

复制代码

代码如下:

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

删除作业：

复制代码

代码如下:

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

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

创建作业：

复制代码

代码如下:

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

下面看到已经创建成功:

5、DDL变更捕获：

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

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

复制代码

代码如下:

ALTER TABLEHumanResources.DepartmentALTER COLUMN Name NVARCHAR(120); GO

然后查询ddl记录表：

复制代码

代码如下:

SELECT * FROM cdc.ddl_history

可以看到：

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

6、使用CDC的函数来获取更改：

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

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

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

Sys.fn_cdc_map_time_to_lsn获取变更范围内的最大、最小LSN值。可以使用：

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

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

--插入数据

复制代码

代码如下:

INSERT INTOHumanResources.Department(name,GroupName,ModifiedDate) VALUES('test','abc',GETDATE()) INSERT INTOHumanResources.Department(name,GroupName,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,GroupName,Name FROM cdc.HumanResources_Department_CT WHERE [__$operation]=2AND [__$start_lsn]BETWEEN @bglsn AND @edlsn

得到以下结果：

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

结果:

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

D、获取LSN边界：

复制代码

代码如下:

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

结果如下：

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

鲜花

握手

雷人

路过

鸡蛋

该文章已有0人参与评论

请发表评论

全部评论

专题导读

More+

10-27 六六分期app的软件客服如何联系？(六六分期

11-06 可心卡盟:win10系统火狐flash插件崩溃怎么

11-06 亲亲特价:怎么删除回收站图标

11-06 济南大学虚拟社区:鲁大师节能降温的具体办

11-06 xlueops.exe:无线网络安装向导

11-06 女斗合众国:win7系统cf与主机连接不稳定怎

11-06 0xc000022-[cf烟雾头]cf怎么调烟雾头

11-06 qizideyouhuo:应用程序无法正常启动0xc0000

11-06 ipz-185:win7系统vcf文件怎么打开

11-06 傻哥蹦迪:win10系统s4怎么打开usb调试

11-06 八神浩树gtaste:回收站清空了怎么恢复

11-06 妖尾之黑色守护:win10系统电脑没有1440x900

11-06 校园至尊魔王小说:win7系统浏览网页时字体

11-06 女斗合众国:win10系统访问共享文件夹提示请

11-06 tokyo hot n0654:恢复win7系统默认字体一招

11-06 雨酷仙境:设置win7系统转移临时文件夹腾出

11-06 阿穆纳伊之杖:win7系统开始菜单在右边还原

11-06 tunespotting:win10系统火狐flash插件总是

11-06 甘尔葛分析师：计谋网站seo关键词暴涨有什

11-06 蔡贵霖: 计谋网站seo关键词暴涨有什么秘密

11-06 博益网首页:ao3网页版进入不了解决方法

11-06 漏斗子专栏: 网站数据分析小白易懂精华篇

11-06 见证双虹怎么做:win7系统开启telnet命令的

11-06 颾狐蝶蜋:系统资源不足无法完成请求的服务

11-06 国光中学校歌:提交网站到alexa查询详细步骤

11-06 西安有情天:静态网页和动态网页的区别

11-06 红木雅尚斋:外部链接构造对网站的好处

11-06 前官礼遇：防止域名劫持–增强域安全性的10

11-06 密传二转答案: 中文分词算法有哪些

11-06 金泉家园邮编:百度快照劫持的表现及应对方

VS2008导出dll库以及程序调试的图文步骤发布时间：2022-06-18

LiteIDE开发工具简明使用指南(Go语言基础学习)发布时间：2022-06-18

剪的笔顺,诠释剪的笔画,认识剪的部首

1 六六分期app的软件客服如何联系？(六六分期

六六分期app的软件客服如何联系？不知道吗？加qq群【895510560】即可！标题：六六分期

阅读：19081|2023-10-27

2 可心卡盟:win10系统火狐flash插件崩溃怎么

今天小编告诉大家如何处理win10系统火狐flash插件总是崩溃的问题，可能很多用户都不知

阅读：9959|2022-11-06

3 亲亲特价:怎么删除回收站图标

今天小编告诉大家如何对win10系统删除桌面回收站图标进行设置，可能很多用户都不知道

阅读：8312|2022-11-06

4 济南大学虚拟社区:鲁大师节能降温的具体办

今天小编告诉大家如何对win10系统电脑设置节能降温的设置方法，想必大家都遇到过需要

阅读：8682|2022-11-06

5 xlueops.exe:无线网络安装向导

我们在使用xp系统的过程中,经常需要对xp系统无线网络安装向导设置进行设置，可能很多

阅读：8620|2022-11-06

6 女斗合众国:win7系统cf与主机连接不稳定怎

今天小编告诉大家如何处理win7系统玩cf老是与主机连接不稳定的问题，可能很多用户都不

阅读：9634|2022-11-06

7 0xc000022-[cf烟雾头]cf怎么调烟雾头

电脑对日常生活的重要性小编就不多说了，可是一旦碰到win7系统设置cf烟雾头的问题，很

阅读：8605|2022-11-06

8 qizideyouhuo:应用程序无法正常启动0xc0000

我们在日常使用电脑的时候，有的小伙伴们可能在打开应用的时候会遇见提示应用程序无法

阅读：7985|2022-11-06

9 ipz-185:win7系统vcf文件怎么打开

今天小编告诉大家如何对win7系统打开vcf文件进行设置，可能很多用户都不知道怎么对win

阅读：8631|2022-11-06

10 傻哥蹦迪:win10系统s4怎么打开usb调试

今天小编告诉大家如何对win10系统s4开启USB调试模式进行设置，可能很多用户都不知道怎

阅读：7524|2022-11-06

客服电话

电子邮件

SQLServer 2008 CDC实现数据变更捕获使用图文详解

请发表评论

全部评论

上一篇：

下一篇：

qiangxi/CheckUpdateLibrary: Android检查

librespeed/speedtest: Self-hosted Speedt

avehtari/BDA_m_demos: Bayesian Data Anal

四维彩超怎么看性别？四维看男孩女孩诀窍

medfreeman/markdown-it-toc-and-anchor: m

剪的笔顺,诠释剪的笔画,认识剪的部首

六六分期app的软件客服如何联系？(六六分期

florent37/ViewAnimator: A fluent Android

florent37/Shrine-MaterialDesign2: implem

CVE-2020-36276

SimpleSoftwareIO/simple-sms: Send and re

关于我们

产品与服务

解决方案

139-2527-9053