SQL Server 变更数据捕获（CDC）监控表数据

一、 背景

在SQL Server 2008版本之前，对表数据库的变更监控，我们通常使用DML触发器进行监控，把DML操作中的INSERT/UPDATE/DELETE数据记录下来，但是触发器的维护比较困难；

当SQL Server 2008新功能：变更数据捕获（Change Data Capture，即CDC）出来之后，我发现这正是我想要的，因为我之前使用DML触发器实现的时候也是把UPDATE操作按照两条记录进行记录的，共同的缺点都是在用户修改了表结构后，CDC不会自动同步到记录中，不过CDC也有DDL的监控可以补充这个缺陷；CDC的优点就是以异步进程读取事务日志进行捕获数据变更的。

 

二、 实现过程

(一) 创建一个测试数据库；

/******* Step1:创建示例数据库*******/
USE master
GO
IF EXISTS(SELECT name FROM sys.databases WHERE name = 'CDC_DB')
DROP DATABASE CDC_DB
GO
CREATE DATABASE CDC_DB
GO

 

(二) 在开启数据库的CDC之前先查询一下状态，is_cdc_enabled值为0表示没有开启，1表示开启，当为数据库[CDC_DB]启用了CDC之后，在CDC_DB系统表中会出现下图Figure2所示的6个表；

/******* Step2:开启数据库CDC *******/
--查看数据库是否启用CDC
SELECT name,is_cdc_enabled FROM sys.databases WHERE name = 'CDC_DB'

--启用数据库CDC
USE CDC_DB
GO
EXECUTE 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 = 'CDC_DB'

（Figure1：数据库CDC状态）

（Figure2：启用数据库CDC创建的系统表）

（Figure3：数据库CDC状态）

（Figure4：添加新用户和架构）

开启数据库的CDC之后，分别在用户和架构上创建新的用户cdc，新的架构cdc；

 

(三) 创建一个测试表，对表行变更启用捕获，为表[Department]启用CDC，首先会在系统表中创建[cdc].[dbo_Department_CT]，会在Agent中创建两个作业，cdc.CDC_DB_capture和cdc.CDC_DB_cleanup，启用表变更捕获需要开启SQL Server Agent服务，不然会报错。每对一个表启用捕获就会生成一个向对应的记录表。