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

浏览数：23 / 时间：2015年06月12日

如果我们希望监视一个数据表的变化，在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‘) )