SQL Server 2008 CDC 功能实现数据变更捕获解决方案

使用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功能可以实现数据变更的捕获，记录数据库中的所有增删改操作，并将其存储在一个专门的表中，以便后续的数据分析和审核。