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

时间：2024-11-07 17:23:22

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为每个需要跟踪的表创建捕获实例。然后通过目录视图中的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('')

对表开启以后，可以在下图中看到多了很多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

说明及例子

案例：

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



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



然后看看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(''),

                       OBJECT_ID(''),

                       OBJECT_ID('') )

结果如下：



步骤三：检验：

相关文章