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

13432878738

一.本文所涉及的内容（Contents）

• 本文所涉及的内容（Contents）
  
• 背景（Contexts）
  
• 实现过程（Realization）
  
• 补充说明（Addon）
  
• 参考文献（References）
  

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

三.实现过程（Realization）
　　(一) 创建一个测试数据库；



/******* 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服务，不然会报错。每对一个表启用捕获就会生成一个向对应的记录表。



/******* Step3:对表启用变更捕获*******/
--创建测试表
USE CDC_DB
GO
CREATE TABLE [dbo].[Department](
[DepartmentID] [smallint] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](200) NULL,
[GroupName] [nvarchar](50) NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
[AddName] [nvarchar](120) NULL,
CONSTRAINT [PK_Department_DepartmentID] PRIMARY KEY CLUSTERED
(
[DepartmentID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
--对表启用捕获
EXEC sys.sp_cdc_enable_table
@source_schema= 'dbo',
@source_name = 'Department',
@role_name = N'cdc_Admin',
@capture_instance = DEFAULT,
@supports_net_changes = 1,
@index_name = NULL,
@captured_column_list = NULL,
@filegroup_name = DEFAULT
--检查是否成功
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('dbo.Department')
--返回某个表的变更捕获配置信息
EXEC sys.sp_cdc_help_change_data_capture 'dbo', 'Department'

　　（Figure5：提示信息）

　　（Figure6：新增加的系统表）

　　（Figure7：生成的捕获和清理作业）

　　（Figure8：表的CDC状态）

　　（Figure9：多了个数据库角色）


　　（Figure10：sys.sp_cdc_enable_table配置选项）
　　上图深色部分的字段值是在执行sys.sp_cdc_enable_table的时候设置的。
　　
　　(四) 测试插入数据、更新数据、删除数据，执行完这些DML，我们来观察下cdc.dbo_Department_CT帮我们记录些什么？



/******* Step4:测试DML变更捕获*******/
--测试插入数据
INSERT  INTO dbo.Department(
Name ,
GroupName ,
ModifiedDate
)VALUES('Marketing','Sales and Marketing',GETDATE())
--测试更新数据
UPDATE dbo.Department SET Name = 'Marketing Group',ModifiedDate = GETDATE()
WHERE Name = 'Marketing'
--测试删除数据
DELETE FROM dbo.Department WHERE Name='Marketing Group'
--查询捕获数据
SELECT * FROM cdc.dbo_Department_CT

　　（Figure11：变更记录表）
　　对于insert/delete操作，会有对应的一行记录，而对于update，会有两行记录。__$operation列：1 = 删除、2= 插入、3= 更新（旧值）、4= 更新（新值）；
　　
　　(五) 启用CDC之后，你怎么从中获取到数据呢？通过数据我们可以对数据进行恢复；



/******* Step6:使用LSN 查看CDC记录*******/
--http://msdn.microsoft.com/zh-cn/library/bb500137%28v=sql.100%29.aspx
SELECT sys.fn_cdc_map_time_to_lsn
('smallest greater than or equal', '2013-07-24 09:00:30') AS BeginLSN
SELECT sys.fn_cdc_map_time_to_lsn
('largest less than or equal', '2013-07-24 23:59:59') AS EndLSN

/******* 查看某时间段所有CDC记录*******/
DECLARE @FromLSN binary(10) =
sys.fn_cdc_map_time_to_lsn
('smallest greater than or equal' , '2013-06-23 09:00:30')
DECLARE @ToLSN binary(10) =
sys.fn_cdc_map_time_to_lsn
('largest less than or equal' , '2013-07-26 23:59:59')
SELECT CASE [__$operation]
WHEN 1 THEN 'DELETE'
WHEN 2 THEN 'INSERT'
WHEN 3 THEN 'Before UPDATE'
WHEN 4 THEN 'After UPDATE'
END Operation,[__$operation],[__$update_mask],DepartmentId,Name,GroupName,ModifiedDate,AddName
FROM [cdc].[fn_cdc_get_all_changes_dbo_Department]
(@FromLSN, @ToLSN,  N'all update old')
/*
all 其中的update，只包含新值
all update old 包含新值和旧值
*/

　　（Figure15：通过时间获取LSN更新）
　　
　　(六) CDC的维护



/******* Step5:维护CDC *******/
--返回所有表的变更捕获配置信息
EXECUTE sys.sp_cdc_help_change_data_capture;
--返回某个表的变更捕获配置信息
EXEC sys.sp_cdc_help_change_data_capture 'dbo', 'Department'
--查看对某个表的哪些列做了捕获监控,使用上面返回的capture_instance列值
EXEC sys.sp_cdc_get_captured_columns
@capture_instance = 'dbo_Department'

　　（Figure12：监控表字段信息）
　　由于sys.sp_cdc_enable_table 的参数：@captured_column_list = NULL，所以dbo.Department表的所有字段都进行监控了，如果你只关心某些字段，强烈建议在创建捕获的时候设置这个属性；



--所有数据库CDC Job信息
SELECT B.name,A.* FROM msdb.dbo.cdc_jobs AS A
LEFT JOIN sys.databases AS B
ON A.database_id = B.database_id
--当前数据库CDC Job信息
EXEC sp_cdc_help_jobs

　　（Figure13：数据库作业信息）

四.补充说明（Addon）
　　SQL Server记录数据变更有四种方法：触发器、Output子句、变更数据捕获（Change Data Capture 即CDC）功能、同步更改跟踪。其中后两个为SQL Server 2008所新增。
　　CDC功能主要捕获SQLServer指定表的增删改操作；
　　CDC除了捕获数据变更之外，还能捕获DDL操作的变化；
　　无法对系统数据库和分发数据库启用该功能。且执行者需要用sysadmin角色权限；
　　cdc._CT   可以看到，这样命名的表，是用于记录源表更改的表。对于insert/delete操作，会有对应的一行记录，而对于update，会有两行记录；
　　对于__$start_lsn列：由于更改是来源与数据库的事务日志，所以这里会保存其事务日志的开始序列号（LSN）；
　　对于__$end_lsn列：
　　对于__$seqval列：
　　对于__$operation列：1 = 删除、2= 插入、3= 更新（旧值）、4= 更新（新值）；
　　对于__$update_mask列：
　　恢复模式为简单模式一样可以进行CDC；
　　虽然能捕获到数据变更，但是没有办法找到是谁更新的？
　　能使用这个做回滚嘛？备份的另外一种路径？对表更新不频繁的情况下？
　　如果是添加或者删除了某些字段DDL，那么创建的CDC表并没有做更改，那新字段的数据怎么捕获呢？修改字段长度等这些操作同样会一起修改CDC对应的表字段；
　　sys.sp_cdc_enable_table 的@role_name参数，是指角色-数据库角色，这个有什么用呢？应用程序角色又有什么用呢？
　　cdc.Person_Contact_CT这名字中CT代表什么意思呢？Capture Table？（用户.架构_表_CT）
　　SQL Server 自启动了两个job,一个捕获，一个清除，注意清除是默认凌晨２点，清除72小时以上的数据。如果同一数据库的表中CDC已经启用，不会重建job。
　　all
　　返回指定 LSN 范围内的所有更改。 对于由更新操作导致的更改，此选项只返回在应用更新之后包含新值的行。
　　all update old
　　返回指定 LSN 范围内的所有更改。 对于由更新操作导致的更改，此选项将返回在更新之前包含列值的行和更新之后包含列值的行。
　　更改数据捕获存储过程 (Transact-SQL)
　　关于变更数据捕获 (SQL Server)
　　变更数据捕获表 (Transact-SQL)

五.参考文献（References）
　　SQL Server 2008中新增的变更数据捕获（CDC）和更改跟踪
　　变更数据捕获
　　变更数据捕获基本知识
　　跟踪数据更改 (SQL Server)
　　关于变更数据捕获 (SQL Server)
　　使用SQLServer 2008的CDC功能实现数据变更捕获（原地址）
　　SQL Server 2008中的CDC(Change Data Capture)功能使用及释疑
　　SQL Server 2008 的CDC功能
　　关于CDC功能的答疑
　　cdc.fn_cdc_get_all_changes_ (Transact-SQL)
　　ALTER AUTHORIZATION (Transact-SQL)
　　sys.sp_cdc_change_job（中英文对译）