SQL Server2000教程 SQL Server2005教程SQL Server2008教程 SQL SERVER2012教程
返回首页

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

时间:2012-10-31 10:28来源:未知 作者:黄钊吉 点击:我要投稿  高质量的ASP.NET空间,完美支持1.0/2.0/3.5/4.0/MVC等

在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

本站推荐文章:
本站热点文章:
顶一下
(3)
100%
踩一下
(0)
0%
------分隔线----------------------------
发表评论
请自觉遵守互联网相关的政策法规,严禁发布色 情、暴力、反动的言论。
评价:
表情:
用户名:密码: 验证码:点击我更换图片