SQL Server审计功能入门：CDC（Change Data Capture）

SQL Server 2008引入了CDC(Change Data Capture),它能记录：

1. 哪些数据行发生了改变

2. 数据行变更的历史记录，而不仅仅是最终值。

跟CT(Change Tracking)相比，它通过作业实现异步变更跟踪(像事务复制)，而CT是同步实现的。因此它对性能的影响较轻并且不会影响事务。

典型应用是在提取、传输和加载数据到其它数据源，就像图中的数据仓库。

实现

微软建议CDC结合快照快照隔离级别使用，可以避免读取变更数据与变更数据写入时的读写阻塞。

需要注意：快照隔离级别会有额外的开销，特别是Tempdb(所有的数据更改都会被版本化存到tempdb)。

use mastergocreate database CDCTestgoalter database CDCTest set allow_snapshot_isolation ongo--enable CDC on database CDCTestuse CDCTestgoexec sys.sp_cdc_enable_dbgo

启用CDC之后会新增一个叫CDC的Schema和一系列的系统表、SP和View。官方建议不要直接查询系统表而是使用对应的系统SP/FN来获取CDC数据。

创建测试表并对期启用CDC。使用sys.sp_cdc_enable_table 对表启用CDC。

--Create a test table for CDCuse CDCTestGOcreate table tb(ID int PRimary key ,name varchar(20),weight decimal(10,2));goEXECUTE sys.sp_cdc_enable_table    @source_schema = N'dbo'  , @source_name = N'tb'  , @role_name = null;GO

如果源表是数据库中第一个要启用变更数据捕获的表，并且数据库不存在事务发布，则 sys.sp_cdc_enable_table 还将为数据库创建捕获和清理作业。 它将 sys.tables 目录视图中的 is_tracked_by_cdc 列设置为 1。

对应的跟踪表cdc.dbo_tb_CT包含了源表所有的变更数据。它包含原来所有的列和5个新的列，结构如图：

验证

当在源表中操行数据更改操作，表cdc.dbo_tb_CT会记录下来。试一下：

为什么没有数据呢？因为之前介绍过了，CDC是靠作业来捕获变更数据的，我的Agent还没有运行。

手动启用后，就有数据了。

结果列的含义：

现在再插入一行，并更新它，然后再删除ID=1的行。再查看结果：

简单说明一下跟踪的查询结果：总共5行，第一行和第二行是插入数据，第三行和第四行是更新前后的数据，第五行是删除数据。操作类型由_$operation值可得知。 简单应用

前文中创建的tb表，记录了每个人的姓名和体重变化信息。另外某一个数据库(表tb_rs)，它是体重变化趋势报表的数据源。它每天同步一次数据，更新自己的数据。怎么用CDC来实现这个需求呢？

CDC中记录了start_lsn,如果能知道tb_rs上次同步完成时，tb中被同步的最大LSN。那下次同步时，只需要同步tb表中大于此LSN的变更记录即可。

问题就简单：获取上次同步完成tb的最大LSN,获取大于此LSN的所有变更记录，更新tb_rs。

• 由sys.fn_cdc_map_time_to_lsn可以将时间映射到对应的LSN，时间就是前一天。
• 由cdc.fn_cdc_get_net_changes_<capture_instance>能得到一天内的所有的净变更记录。
• 由变更记录自定义同步逻辑和语句。

insert into tbvalues(1,'Ken',70.2),(3,'Joe',66),(4,'Rose',50)update tbset weight=70where ID=3;delete from tb where name='Rose';goDECLARE @begin_time datetime, @end_time datetime, @begin_lsn binary(10), @end_lsn binary(10); --get the intervalselect @begin_time=GETDATE()-1,@end_time=GETDATE();--map the time to LSN of the CDC table tbselect  @begin_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time),  @end_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time);--get the net changes within the specified LSNsSELECT * FROM cdc.fn_cdc_get_net_changes_dbo_tb(@begin_lsn, @end_lsn, 'all');