使用SQLServer2008的CDC功能实现数据变更捕获
CDC(Change Data Capture)是SQL Server 2008中的一项功能,用于捕获数据库中的数据变更操作。它可以记录数据库中的所有增删改操作,并将其存储在一个专门的表中,以便后续的数据分析和审核。下面是使用SQL Server 2008的CDC功能实现数据变更捕获的详细介绍。
背景
在SQL Server 2008之前,对数据变更的捕获通常使用触发器、时间戳等低效高成本的功能来实现。这使得许多系统都没有做数据变更或者仅仅对核心表做监控。但是自从SQL Server 2008引入了CDC功能后,解决了很多烦恼问题。
适用环境
CDC功能仅在SQL Server 2008(含)以后的企业版、开发版和评估版中可用。
详解
CDC功能主要捕获SQL Server指定表的增删改操作,由于任何操作都会写日志(哪怕truncate),所以CDC的捕获来源于日志文件。日志文件会把更改应用到数据文件中,同时也会标记符合要求的数据标记为需要添加跟踪的项。然后通过一些配套函数,最后写入到数据仓库中。
大概流程
步骤1:对目标库显式启用CDC
在当前库使用sys.sp_cdc_enable_db。返回0(成功)或1(失败)。注意,无法对系统数据库和分发数据库启用该功能。且执行者需要用sysadmin角色权限。
该存储过程的作用域是整个目标库。包含元数据、DDL触发器、cdc架构和cdc用户。
使用以下代码启用:
USE AdventureWorks
GO
EXECUTE sys.sp_cdc_enable_db;
GO
在一开始直接执行时,出现了报错信息:
1、消息22830,级别16,状态1,过程sp_cdc_enable_db_internal,第193行
2、无法更新元数据来指示已对数据库AdventureWorks启用了变更数据捕获。执行命令'SetCDCTracked(Value=1)'时失败。
返回的错误为15517:'无法作为数据库主体执行,因为主体"dbo"不存在、无法模拟这种类型的主体,或您没有所需的权限。'
步骤2:对目标表显式启用CDC
在当前库使用sys.sp_cdc_enable_table。返回0(成功)或1(失败)。
该存储过程的作用域是整个目标表。包含元数据、DDL触发器、cdc架构和cdc用户。
使用以下代码启用:
USE AdventureWorks
GO
EXECUTE sys.sp_cdc_enable_table @source_schema = 'HumanResources', @source_name = 'Department', @role_name = 'cdc_Admin';
GO
步骤3:捕获数据变更
使用sys.sp_cdc_get_all_changes存储过程可以捕获所有的数据变更操作。
使用以下代码捕获:
USE AdventureWorks
GO
DECLARE @begin_lsn binary(10), @commit_lsn binary(10)
EXEC sys.sp_cdc_get_all_changes @source_schema = 'HumanResources', @source_name = 'Department', @begin_lsn = @begin_lsn OUTPUT, @commit_lsn = @commit_lsn OUTPUT, @all_changes = 1;
GO
步骤4:清除CDC日志
使用sys.sp_cdc_purge存储过程可以清除CDC日志。
使用以下代码清除:
USE AdventureWorks
GO
EXEC sys.sp_cdc_purge;
GO
优点
使用CDC功能可以实现数据变更的捕获,可以记录数据库中的所有增删改操作,并将其存储在一个专门的表中,以便后续的数据分析和审核。同时,CDC功能还可以解决许多烦恼问题,如客户2个月前更改了一个数据,但是由于服务器空间不足,经常只能保留一个月的备份。此时客户为了逃避因为更改数据带来的较大负面影响,往往会把责任推给软件开发商或者运维商,如果此时有变更捕获的话,就有证据了。
使用SQL Server 2008的CDC功能可以实现数据变更的捕获,记录数据库中的所有增删改操作,并将其存储在一个专门的表中,以便后续的数据分析和审核。