--Create a test table for CDC
use CDCTest
create table tb(ID int primary key ,name varchar(20),weight decimal(10,2));
EXECUTE sys.sp_cdc_enable_table
@source_schema = N'dbo'
, @source_name = N'tb'
, @role_name = null;
如果源表是数据库中第一个要启用变更数据捕获的表，并且数据库不存在事务发布，则 sys.sp_cdc_enable_table 还将为数据库创建捕获和清理作业。 它将 sys.tables 目录视图中的 is_tracked_by_cdc 列设置为 1。
对应的跟踪表cdc.dbo_tb_CT包含了源表所有的变更数据。它包含原来所有的列和5个新的列，结构如图：
当在源表中操行数据更改操作，表cdc.dbo_tb_CT会记录下来。试一下：
为什么没有数据呢？因为之前介绍过了，CDC是靠作业来捕获变更数据的，我的Agent还没有运行。
手动启用后，就有数据了。
结果列的含义：
varbinary(128)
位掩码，源表中被CDC跟踪的每一列对应一个位。如果 __$operation = 1 或 2，该值将所有已定义的位设置为 1。如果 __$operation = 3 或 4，则只有那些对应已更改列的位设置为 1。
现在再插入一行，并更新它，然后再删除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 tb
values(1,'Ken',70.2),(3,'Joe',66),(4,'Rose',50)
update tb
set weight=70
where ID=3;
delete from tb where name='Rose';
DECLARE @begin_time datetime, @end_time datetime, @begin_lsn binary(10), @end_lsn binary(10);
--get the interval
select @begin_time=GETDATE()-1,@end_time=GETDATE();
--map the time to LSN of the CDC table tb
select @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 LSNs
SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_tb(@begin_lsn, @end_lsn, 'all');
居然没有Rose的记录？Joe的信息被更新过，怎么才一条记录？
这是因为这里得到是净变更行，也就是最终结果的意思。新增然后又删除，不影响最终结果，所以没有。多次更新同一行的某一列数据，只返回最后更新的结果。
得到这个结果，我们就可以根据__$operation和实际数据定义同步数据的逻辑了。比如：
--generate sync statements
SELECT (case __$operation when 2 then 'insert into tb_rs values ('+cast(ID as varchar(2))+', '+Name+', '+cast(weight as varchar(10))+')'
when 4 then 'update tb_rs set name='+name+',weight='+cast(weight as varchar(10))+' where ID='++cast(ID as varchar(2)) END)
FROM cdc.fn_cdc_get_net_changes_dbo_tb(@begin_lsn, @end_lsn, 'all');对于更新过的行，同步数据时，我想要先判断出列是否被更改过和被更改的时间。更改过的列才需要被同步，而不是所有列同步一次。以name为例：
DECLARE @begin_time datetime, @end_time datetime, @begin_lsn binary(10), @end_lsn binary(10);
--get the interval
select @begin_time=GETDATE()-1,@end_time=GETDATE();
--map the time to LSN of the CDC table tb
select @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 all changes within the specified LSNs
SELECT *,
(Case sys.fn_cdc_has_column_changed('dbo_tb','name',__$update_mask) when 1 then 'Yes' when 0 then 'No' End) as isNameUpdated,
sys.fn_cdc_map_lsn_to_time(__$start_lsn) as updateTime
FROM cdc.fn_cdc_get_all_changes_dbo_tb(@begin_lsn, @end_lsn, 'all')
where __$operation in(3,4);
CDC不仅能记录DML操作，还能记录DDL操作。查询cdc.ddl_history。
但有一点要格外注意：新增的列，能被CDC DDL跟踪到，但是新列的数据变更却不能被CDC跟踪到。如果需要跟踪它，先禁用表上的CDC，再启用即可。
CDC Agent Job
在指定的数据库中首次启用CDC，并且不存在事务复制，则会创建capture和cleanup两个作业：
capture作业是用于扫描日志文件，把变更记录写到变更表中。调用sp_MScdc_capture_job来实现，可以根据当前库的实际事务吞吐量来设置扫描参数和扫描间隔，使得在性能开销和跟踪需求间达到合理平衡。
cleanup作业是清理变更变表中的数据，默认三天的数据。
所以合理设定cleanup的间隔是非常重要的。
这两个作业的相关的配置存储在msdb.dbo.cdc_jobs中。当前的默认配置如图：
1. CDC使用方便，易于配置，能与同步抽取等应用结合使用。
2. CDC能满足大多数对数据审计的要求，但不能告诉你“谁”更改了数据。
3. 虽说CDC是异步的，对应性能影响小，但还是会增加开销，特别是IO读写和容量方面的。开启CDC，每次更改，都至少会额外增加一次数据文件写和日志文件写操作。
-------------------------------------
作者：Joe.TJ
Joe's Blog:http://www.cnblogs.com/Joe-T/
