知方可补不足~用CDC功能来对数据库变更进行捕捉

回到目录

如果我们希望监视一个数据表的变化,在sql2008之前的版本里,在数据库端可能想到的只有触发器,或者在程序端通过监视自己的insert,update,delete来实现相应的功能,这种实现无疑是让我们感到恐惧的,不够灵活的,而当进行sql2008后,这种情况得到了本质的改变,sql2008为我们提供了CDC功能,它可以实时对指定的数据表进行监控,当前它同时对产生SQL的一些负载。

CDC工作流程

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

CDC实现步骤

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

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

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

使用以下代码启用:

USE tableName

Go

EXECUTE sys.sp_cdc_enable_db

GO
当设置好数据库的CDC功能后,我们可以查看一下是否已经设置成功
SELECT  IS_CDC_ENABLED ,
        CASE WHEN IS_CDC_ENABLED = 0 THEN CDC功能禁用
             ELSE CDC功能启用
        END 描述
FROM    SYS.DATABASES
WHERE   NAME = tableName
 

同时,数据库的用户将会多了一个CDC用户

技术分享

第二步、对目标表启用CDC

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

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

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

 
EXEC sys.sp_cdc_enable_table @source_schema = DBO,
    @source_name = WebManageUsers, @role_name = NULL 

注意,source_schema说的是数据库架构者,如dbo,cdc等

技术分享

 

@source_name说的就是数据表名,@role_name是角色名,为null表示对角色没有特别限制

技术分享

当设置完成数据表的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(DBO.WebManageUsers) )

技术分享

当我们对WebManageUsers表修改数据后,可以在DBO_WebManageUsers_CT表中查到相应的结果,从表名中可以看到,CDC表的命名规则是架构名_表名_CT,呵呵。

SELECT * FROM cdc.DBO_WebManageUsers_CT

技术分享

从结果中我们可以看到,CDC对于update操作是进行了修改前和修改后的两次捕捉,非常清晰!
 
好了,对于数据库的CDC功能今天就说到这里,希望对各位有所帮助!

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。