医站点医维基

医疗之家-医疗器械-医疗设备-医疗技术

 找回密码
 立即注册

QQ登录

只需一步,快速开始

扫一扫,访问微社区

查看: 1050|回复: 26

[信息系统] 使用SQLServer 2008的CDC功能实现数据变更捕获

[复制链接]

283

主题

681

帖子

2465

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
2465

热心会员推广达人优秀版主荣誉管理论坛元老

发表于 2019-1-1 10:49:25 | 显示全部楼层 |阅读模式
最近由于工作需要,研究了一下2008 CDC功能,觉得还不错,下面整理了一下研究过程,虽然比较粗略,但是基本上能用了,如果有补充请大家回复,我视情况修改原帖,谢谢。
背景:
在SQLServer2008之前,对数据变更的捕获通常使用触发器、时间戳等低效高成本的功能来实现,所以很多系统都没有做数据变更或者仅仅对核心表做监控。
自从SQLServer2008引入了数据变更捕获功能(后面简称为CDC)后,解决了很多烦恼问题,如客户2个月前更改了一个数据,但是由于服务器空间不足,经常只能保留一个月的备份。此时客户为了逃避因为更改数据带来的较大负面影响,往往会把责任推给软件开发商或者运维商,如果此时有变更捕获的话,就有证据了,当然分析日志也能实现,但是比较痛苦。
适用环境:
仅在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,CASEWHEN
is_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 sys.sp_cdc_enable_table@source_schema=
'HumanResources',
    @source_name
= 'Department',@role_name
= NULL
然后查询是否成功:
SELECT  
name ,
        is_tracked_by_cdc
,
        CASE
WHEN is_tracked_by_cdc
= 0 THEN 'CDC功能禁用'
             ELSE
'CDC功能启用'
        END
描述
FROM   
sys.tables
WHERE   OBJECT_ID=
OBJECT_ID('HumanResources.Department')
对表开启以后,可以在下图中看到多了很多cdc架构开头的表:

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

下面列出相关的存储过程:
Sys.sp_cdc_add_job

说明及例子

Sys.sp_cdc_generate_wrapper_function

说明及例子

Sys.sp_cdc_change_job

说明及例子

Sys.sp_cdc_get_captured_columns

说明及例子

Sys.sp_cdc_cleanup_change_table

说明及例子

Sys.sp_cdc_get_ddl_history

说明及例子

Sys.sp_cdc_disable_db

说明及例子  建议先禁用表,再禁用库

Sys.sp_cdc_help_change_data_capture

说明及例子

Sys.sp_cdc_disable_table

说明及例子

Sys.sp_cdc_help_jobs

说明及例子

Sys.sp_cdc_drop_job

说明及例子

Sys.sp_cdc_scan

说明及例子

Sys.sp_cdc_enable_db

说明及例子

Sys.sp_cdc_start_job

说明及例子

Sys.sp_cdc_enable_table

说明及例子

Sys.sp_cdc_stop_job

说明及例子

函数:
Cdc.fn_cdc_get_all_changes_<capture_instance>

说明及例子

Sys.fn_cdc_has_column_changed

说明及例子

Cdc.fn_cdc_get_net_changes_<capture_instance>

说明及例子

Sys.fn_cdc_increment_lsn

说明及例子

Sys.fn_cdc_decrement_lsn

说明及例子

Sys.fn_cdc_is_bit_set

说明及例子

Sys.fn_cdc_get_column_ordinal

说明及例子

Sys.fn_cdc_map_lsn_to_time

说明及例子

Sys.fn_cdc_get_max_lsn

说明及例子

Sys.fn_cdc_map_time_to_lsn

说明及例子

Sys.fn_cdc_get_min_lsn

说明及例子

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

先检查原库的内容:可以看到系统表里面只有一个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 行
无法更新元数据来指示已对数据库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 行
在批处理结束时检测到不可提交的事务。该事务将回滚。
如果出现这个错误,目前的解决方法是执行下面语句,原因已在开头说明,对于没有使用EXECUTE AS的库,一般不会有这样的问题:
ALTER AUTHORIZATION
ON 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,是在上面语句中动态创建的:


按照上面步骤把另外两个表也开启了:
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功能的表。现在来检查是否开启成功:
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.Department
WHERE DepartmentID>17
再查询cdc表,可以看到又多了16条记录:


现在来做下update的实验:
UPDATE  HumanResources.Department
SET 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
可以看到以下截图:


查看对某个实例(即表)的哪些列做了捕获监控:
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 TABLE
HumanResources.Department
ALTER 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 INTO
HumanResources.Department(name,GroupName,ModifiedDate)
VALUES('test','abc',GETDATE())
INSERT INTO
HumanResources.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]=2
AND [__$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]

结果如下:

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

以上是一边测试一边记录的结果,可能存在很多片面的地方,欢迎拍砖
---------------------
作者:發糞塗牆
来源:CSDN
原文:https://blog.csdn.net/dba_huangzj/article/details/8130448
版权声明:本文为博主原创文章,转载请附上博文链接!

回复

使用道具 举报

110

主题

530

帖子

1410

积分

金牌会员

Rank: 6Rank: 6

积分
1410

热心会员推广达人优秀版主荣誉管理论坛元老

发表于 2019-2-28 00:38:37 | 显示全部楼层
好贴
回复 支持 反对

使用道具 举报

24

主题

472

帖子

1203

积分

版主

Rank: 7Rank: 7Rank: 7

积分
1203

热心会员推广达人优秀版主荣誉管理论坛元老

发表于 2019-3-17 09:33:36 | 显示全部楼层
感谢分享感谢分享感谢分享
回复 支持 反对

使用道具 举报

135

主题

535

帖子

1447

积分

版主

Rank: 7Rank: 7Rank: 7

积分
1447

热心会员推广达人优秀版主荣誉管理论坛元老

发表于 2019-3-18 13:21:03 | 显示全部楼层
厉害
回复 支持 反对

使用道具 举报

60

主题

463

帖子

1208

积分

版主

Rank: 7Rank: 7Rank: 7

积分
1208

热心会员推广达人优秀版主荣誉管理论坛元老

发表于 2019-3-18 13:21:03 | 显示全部楼层
先看看先。。。
回复 支持 反对

使用道具 举报

13

主题

399

帖子

1027

积分

版主

Rank: 7Rank: 7Rank: 7

积分
1027

热心会员推广达人优秀版主荣誉管理论坛元老

发表于 2019-4-16 11:55:30 | 显示全部楼层
谢谢楼主分享,收藏
回复 支持 反对

使用道具 举报

149

主题

600

帖子

1589

积分

版主

Rank: 7Rank: 7Rank: 7

积分
1589

热心会员推广达人优秀版主荣誉管理论坛元老

发表于 2019-4-17 15:42:12 | 显示全部楼层
回复 支持 反对

使用道具 举报

149

主题

600

帖子

1589

积分

版主

Rank: 7Rank: 7Rank: 7

积分
1589

热心会员推广达人优秀版主荣誉管理论坛元老

发表于 2019-4-30 09:19:00 | 显示全部楼层
回复 支持 反对

使用道具 举报

0

主题

399

帖子

797

积分

高级会员

Rank: 4

积分
797

最佳新人

发表于 2019-5-5 00:26:13 | 显示全部楼层
有谁试过啊!
回复 支持 反对

使用道具 举报

4

主题

406

帖子

1058

积分

版主

Rank: 7Rank: 7Rank: 7

积分
1058

热心会员推广达人优秀版主荣誉管理论坛元老

发表于 2019-5-27 00:17:45 | 显示全部楼层
留个脚印。。。。。。
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|关于我们|医维基|网站地图|Archiver|手机版|医疗之家 ( 沪ICP备15050839号|申请友情链接  

GMT+8, 2019-9-16 14:12 , Processed in 0.281783 second(s), 29 queries .

Powered by Discuz! X3.2 Licensed

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表