推荐订阅源

CDC(Change Data Capture) 变化数据捕获 （新特性）

/**********************************************************
第一章: 概述
**********************************************************/

经常会有需求记录用户表中由DML操作(Insert/Updae/Delete)引起的数据变化，在SQL Server 2008 以前的版本中，要实现这样的功能只能通过Trigger或者数据比对(例如SCD处理)，而且必须针对每个用户表开发。SQL Server 2008中新增了两种记录数据变化的功能：

1、Change Data Capture(CDC)；

2、Change Tracking；

本文针对CDC的功能介绍和应用；

Change Tracking功能参照《SQL SERVER 2008 开发系列（四）》；

Change Data Capture

CDC通过对事务日志的异步读取，记录DML操作的发生时间、类型和实际影响的数据变化，然后将这些数据记录到启用CDC时自动创建的表中。通过cdc相关的存储过程，可以获取详细的数据变化情况。由于数据变化是异步读取的，因此对整体性能的影响不大，远小于通过Trigger实现的数据变化记录。

下图来自于SQL Server Books Online，说明了CDC可用于获取不同时间段内的变化。

/**********************************************************
Author="WZ"
Create Date="2008/9/6"
SQL SERVER 2008开发系列（七） CDC(变化数据捕获) 新特性
***********************************************************/

/**********************************************************
第二章: 环境准备，何如开启更改跟踪
**********************************************************/
Use Master
Go
--创建演示数据库

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'ChangeDataCapture')
DROP DATABASE ChangeDataCapture
GO
USE [master]
GO
Create Database ChangeDataCapture
on
(
NAME = ChangeDataCapture_dat,
FileName = 'D:\数据库\SQL2008\ChangeDataCapture_dat.mdf',
Size = 10MB,
Maxsize = 100MB,
FileGrowth = 5MB
)
LOG ON
(
Name = ChangeDataCapture_Log,
FileName = 'D:\数据库\SQL2008\ChangeDataCapture_log.ldf',
Size = 5MB,
MaxSize = 25MB,
FileGrowth = 5MB
)
Go

--当这个数据库成功创建之后，你需要使这个数据库可以使用数据捕获特性，这可以通过执行下面的TSQL查询来实现

Use ChangeDataCapture
Go
--开启数据捕获
EXEC sys.sp_cdc_enable_db
Go 

-- 执行下面的查询来检查这个数据库是否可用于CDC，　is_cdc_enabled 字段的值为1代表这个数据库可用于CDC，而0意味着CDC不可用。
Select [name] as DBName, is_cdc_enabled from sys.databases
GO

如下图所示：

/********************************************************

注意事项

当数据库可用于CDC时，你就可以看到在ChangeDataCapture数据库中创建的新cdc schema、cdc用户、新的元数据表和其它系统对象。当使得CDC可用在一个数据库时，数据库管理员要时刻牢记的最重要的事情是确保这个数据库在配置CDC之前没有cdc schema或cdc用户。如果在这个数据库中有cdc schema或cdc用户，那么CDC的配置将会失败，所以数据库管理员需要在配置CDC之前从数据库中删除或重命名之前存在的cdc schema或用户。

如下图所示：

**********************************************************/

/**********************************************************
第三章: 怎样为一个SQL Server 2008数据库表配置CDC
**********************************************************/

--现在让我们执行下面的TSQL查询在ChangeDataCapture数据库中创建Currency表：　　

Use ChangeDataCapture
Go
Create table Currency
(
CurrencyKey Int Identity(1,1) Primary Key NOT NULL,
CurrencyAlternateKey varchar(5)
)

--当Currency表成功创建之后，你需要确保SQL Server 代理服务正在运行。为了使CDC成功，SQL Server 代理应该运行。

--启动SQL SERVER 代理服务

--通过执行下面提到的TSQL查询使CDC可用于表Currency

Use ChangeDataCapture
Go
EXEC sp_cdc_enable_table 'dbo', 'Currency', @role_name = NULL, @supports_net_changes =1
Go

--如下图所示，执行作业成功

--执行下面的查询来检查这个表是否可以使用CDC：

Use ChangeDataCapture
Go
--is_tracked_by_cdc字段值为1代表CDC可用于这个表，而值为0代表CDC不可用。　
Select [name], is_tracked_by_cdc from sys.tables

GO

如下图所示：

--当你使CDC可用于Currency表后，另一个表被创建以保存变化数据和关于源表中变化的信息。
--新创建的表名称为cdc.dbo_Currency_CT，如下图所示：

/**********************************************************
第四章: SQL Server 2008 CDC捕获DDL改变**********************************************************/

-- 下一步是修改Currency表来添加CurrencyName字段：　

Use ChangeDataCapture
Go
Alter table Currency add CurrencyName varchar(25)
Go

--可以通过执行下面的TSQL查询来查看Currency表所发生的DDL改变：

Use ChangeDataCapture
Go
Select OBJECT_NAME(Source_Object_ID) As [Table Name], 
       OBJECT_NAME(Object_ID) As [CDC Table Name],
       DDL_Command As [DDL Command],
       DDL_LSN As [Log Sequence Number],
       DDL_Time As [DateModified]
From CDC.ddl_history
Go

/**********************************************************
第五章: SQL Server 2008 CDC捕获DML改变**********************************************************/

CDC的原理是每次对源表(Source Tables)执行insert、update、delete时，数据库事务日志会记录DML造成的变更数据，然后捕获处理过程将日志中源表的变更数据写入变更捕获表(Change Tables)，最后ETL工具使用CDC查询函数将变更数据抽取到数据仓库。

--插入数据到Currency表
insert into Currency(CurrencyAlternateKey,CurrencyName) values('RMB1','人民币2')
insert into Currency(CurrencyAlternateKey,CurrencyName) values('RMB2','人民币3')
insert into Currency(CurrencyAlternateKey,CurrencyName) values('RMB3','人民币4')

--__$start_lsn 日志中序列号，在对源表执行 DML时，每个事务有一个日志序列号
--__$end_lsn 空，不使用
--__$seqval 在一个事务中， DML影响行的序列号
--__$operation 识别执行的是何种 DML， 1=delete， 2=insert， 3=update（更新前）， 4=update（更新后）
--__$update_mask 用 2进制标识哪一列发生变更
select * from cdc.dbo_Currency_CT

update Currency set CurrencyAlternateKey='usb' where CurrencyAlternateKey='RMB3'

select * from cdc.dbo_Currency_CT

delete from Currency where CurrencyAlternateKey='usb'

总结

　　变化数据捕获特性帮助数据库管理员使得可以在一个数据库表上使用CDC并跟踪一个特定用户表上所有数据库定义语言的改变。