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('HumanResources.Department')
对表开启以后，可以在下图中看到多了很多cdc架构开头的表：
启动之后，可以看到SQLServer代理里面的作业，也出现了这两个作业：
下面列出相关的存储过程：
1、Sys.sp_cdc_add_job
2、Sys.sp_cdc_generate_wrapper_function
3、 Sys.sp_cdc_change_job
4、Sys.sp_cdc_get_captured_columns
5、Sys.sp_cdc_cleanup_change_table
6、Sys.sp_cdc_get_ddl_history
7、Sys.sp_cdc_disable_db
8、Sys.sp_cdc_help_change_data_capture
9、Sys.sp_cdc_disable_table
10、Sys.sp_cdc_help_jobs
11、Sys.sp_cdc_drop_job
12、Sys.sp_cdc_scan
13、Sys.sp_cdc_enable_db
14、Sys.sp_cdc_start_job
15、Sys.sp_cdc_enable_table
16、Sys.sp_cdc_stop_job
1、Cdc.fn_cdc_get_all_changes_<capture_instance>
2、Sys.fn_cdc_has_column_changed
3、Cdc.fn_cdc_get_net_changes_<capture_instance>
4、Sys.fn_cdc_increment_lsn
5、Sys.fn_cdc_decrement_lsn
6、Sys.fn_cdc_is_bit_set
7、Sys.fn_cdc_get_column_ordinal
8、Sys.fn_cdc_map_lsn_to_time
9、Sys.fn_cdc_get_max_lsn
10、Sys.fn_cdc_map_time_to_lsn
11、Sys.fn_cdc_get_min_lsn
下面开始从头到尾做一个实际案例：
先检查原库的内容：可以看到系统表里面只有一个dbo.sysdiagrams表
然后看看SQLServer代理：可以看到也没有相关的作业
步骤二：对数据库启用CDC数据库级别功能。必须显式启用：
Step1、执行以下语句：
USE AdventureWorks
EXEC sys.sp_cdc_enable_db
某些数据库可能存在一些存储过程包含有：execute as 等语句，此时会报错：
文字描述：
1、消息22830，级别16，状态1，过程sp_cdc_enable_db_internal，第186
2、无法更新元数据来指示已对数据库AdventureWorks 启用了变更数据捕获。执行命令'SetCDCTracked(Value
= 1)' 时失败。返回的错误为15517: '无法作为数据库主体执行，因为主体"dbo"
不存在、无法模拟这种类型的主体，或您没有所需的权限。'。请使用此操作和错误来确定失败的原因并重新提交请求。
3、消息266，级别16，状态2，过程sp_cdc_enable_db_internal，第0
4、EXECUTE 后的事务计数指示BEGIN 和COMMIT 语句的数目不匹配。上一计数=
0，当前计数= 1。
5、消息266，级别16，状态2，过程sp_cdc_enable_db，第0
6、EXECUTE 后的事务计数指示BEGIN 和COMMIT 语句的数目不匹配。上一计数=
0，当前计数= 1。
7、消息3998，级别16，状态1，第1 行
8、在批处理结束时检测到不可提交的事务。该事务将回滚。
如果出现这个错误，目前的解决方法是执行下面语句，原因已在开头说明，对于没有使用EXECUTE
AS的库，一般不会有这样的问题：
ALTER AUTHORIZATION ON DATABASE::[AdventureWorks] TO [sa]
然后再次执行，就成功开启了：
USE AdventureWorks
EXEC sys.sp_cdc_enable_db
现在检查是否成功：
SELECT is_cdc_enabled ,
CASE WHEN is_cdc_enabled = 0 THEN 'CDC功能禁用'
ELSE 'CDC功能启用'
END 描述
FROM sys.databases
WHERE NAME = 'AdventureWorks'
EXECUTE sys.sp_cdc_enable_table
@source_schema = N'HumanResources'
, @source_name = N'Department'
, @role_name = N'cdc_Admin'--可以自动创建
, @capture_instance=DEFAULT
结果如图:
可以看到在创建的同时,也创建了两个作业: cdc.AdventureWorks_cleanup和cdc.AdventureWorks_capture
也可以看到多了一个角色CDC_ADMIN，是在上面语句中动态创建的：
按照上面步骤把另外两个表也开启了：
USE AdventureWorks;
EXECUTE sys.sp_cdc_enable_table
@source_schema = N'Person'
, @source_name = N'ADDRESS'
, @role_name = N'cdc_Admin'--可以自动创建
, @capture_instance=DEFAULT
EXECUTE sys.sp_cdc_enable_table
@source_schema = N'Person'
, @source_name = N'Contact'
, @role_name = N'cdc_Admin'--可以自动创建
, @capture_instance=DEFAULT
可以从系统表中看到：
了3个表，并且是刚才开启CDC功能的表。现在来检查是否开启成功：
SELECT name ,
is_tracked_by_cdc ,
CASE WHEN is_tracked_by_cdc = 0 THEN 'CDC功能禁用'
ELSE 'CDC功能启用'
END 描述
FROM sys.tables
WHERE OBJECT_ID IN( OBJECT_ID('HumanResources.Department'),
OBJECT_ID('Person.ADDRESS'),
OBJECT_ID('Person.Contact') )
结果如下：
步骤四：检验：
下面来改动数据：
然后把表中的数据复制一份：
INSERT INTO HumanResources.Department
( Name ,
GroupName ,
ModifiedDate
SELECT Name + '1' ,
GroupName + '1' ,
GETDATE() ModifiedDate
FROM HumanResources.Department
结果如下：
然后从cdc.HumanResources_Department_CT
可以看到的确多了16条记录。这部分记录证明了，有16条数据从监控到目前为止做了改动，现在再来改动一下：
DELETE FROM HumanResources.Department WHERE DepartmentID>17
再查询cdc表，可以看到又多了16条记录：
现在来做下update的实验：
UPDATE HumanResources.Department SET ModifiedDate=GETDATE()
现在来分析一下这个表：
可以在联机丛书上查看：cdc.<capture_instance>_CT
可以看到，这样命名的表，是用于记录源表更改的表。对于insert/delete操作，会有对应的一行记录，而对于update，会有两行记录。
对于__$operation列：1 = 删除、2= 插入、3= 更新（旧值）、4=
更新（新值）
对于__$start_lsn列：由于更改是来源与数据库的事务日志，所以这里会保存其事务日志的开始序列号（LSN）
但是微软不检查直接查询这类表，建议使用cdc.fn_cdc_get_all_changes_<捕获实例>
和cdc.fn_cdc_get_net_changes_<capture_instance>
下文开始，来熟悉各种函数、存储过程的使用，并尝试一些不正常的操作。
日常使用情景：
1、查询已经开启的捕获实例：
由于可能不记得或者不知道开启了什么表的捕获，所以可以使用以下语句来查找：
--返回所有表的变更捕获配置信息
EXECUTE sys.sp_cdc_help_change_data_capture;
可以看到以下截图：
查看对某个实例(即表)的哪些列做了捕获监控:
EXEC sys.sp_cdc_get_captured_columns
@capture_instance = 'HumanResources_Department' -- sysname
得到下图:
也可以从下面中查找配置信息：
SELECT * FROM msdb.dbo.cdc_jobs
2、查看当前配置使用sp_cdc_help_jobs：
从上文可以看到，启用cdc之后会自动创建了两个作业，可以先使用以下语句来查看：
sp_cdc_help_jobs
得到的结果：
对于一个大型的OLTP系统，由于数据更改会非常频繁，变更表中的数据会非常多，如果存放过久（最久可以存放100年），那对数据库空间是非常大的挑战。此时可以调整上图中cdc.AdventureWorks_cleanup
中retention（单位：分钟）。
3、修改配置：sp_cdc_change_job：
--显示原有配置：
EXEC sp_cdc_help_jobs
--更改数据保留时间为分钟
EXECUTE sys.sp_cdc_change_job
@job_type = N'cleanup',
@retention=100
--停用作业
EXEC sys.sp_cdc_stop_jobN'cleanup'
--启用作业
EXEC sys.sp_cdc_start_jobN'cleanup'
--再次查看
EXEC sp_cdc_help_jobs
得到以下结果：
证明修改成功，此处注意，修改后要先停用（如果已经启用），再启用，才能生效。
4、停止/启用、删除/创建作业：
停止/开始作业，可以使用以下语句：
--停用作业
EXEC sys.sp_cdc_stop_jobN'cleanup'
--启用作业
EXEC sys.sp_cdc_start_jobN'cleanup'
删除作业：
EXEC sys.sp_cdc_drop_job@job_type = N'cleanup' -- nvarchar(20)
--查看作业
EXEC sys.sp_cdc_help_jobs
可以看到现在只剩下一个作业了:
创建作业：
EXEC sys.sp_cdc_add_job
@job_type = N'cleanup',
@start_job = 0,
@retention = 5760
--查看作业
EXEC sys.sp_cdc_help_jobs
下面看到已经创建成功:
5、DDL变更捕获：
CDC除了捕获数据变更之外，还能捕获DDL操作的变化。前提是先要确保SQLServer
代理的启用，其实CDC功能都需要确保sql 代理正常运行，因为所有操作都通过代理中的两个作业来实现的。
现在先来对HumanResources.Department 表修改一下，把name的长度加长：
ALTER TABLE HumanResources.Department ALTER COLUMN Name NVARCHAR(120) ;
然后查询ddl记录表：
SELECT *
FROM cdc.ddl_history
可以看到：
由于在截图之前已经试了几次，所以里面有3条数据，之所以试了几次，就是因为上面所说的，忘了开SQL代理，所以查不出数据，所以切记要开启SQL代理。
6、使用CDC的函数来获取更改：
A、使用cdc.fn_cdc_get_all_changes_HumanResources_Department
函数报告捕获实例HumanResources_Department 的当前所有可用更改：
DECLARE @from_lsn binary(10), @to_lsn binary(10)
SET @from_lsn =
sys.fn_cdc_get_min_lsn('HumanResources_Department')
SET @to_lsn = sys.fn_cdc_get_max_lsn()
SELECT * FROM cdc.fn_cdc_get_all_changes_HumanResources_Department
(@from_lsn, @to_lsn, N'all update old');
B、获取某个时间段的更改信息：
先根据日志序列号（logsequence number ,LSN）来获取跟踪变更数据：
Sys.fn_cdc_map_time_to_lsn获取变更范围内的最大、最小LSN值。可以使用：
Smallest greater than;smallest greater
than orequal;largest less than;largest less than or
equal.
如查询某个时间段插入的数据：
--插入数据
INSERT INTO HumanResources.Department(name,GroupName,ModifiedDate)
VALUES('test','abc',GETDATE())
INSERT INTO HumanResources.Department(name,GroupName,ModifiedDate)
VALUES('test1','abc1',GETDATE())
--检查数据
DECLARE @bglsn VARBINARY(10)=sys.fn_cdc_map_time_to_lsn('smallest greater than or equal','2012-10-12 12:00:00.997')
DECLARE @edlsn VARBINARY(10)=sys.fn_cdc_map_time_to_lsn('largest less than or equal',GETDATE())
SELECT DepartmentID,GroupName,Name
FROM cdc.HumanResources_Department_CT
WHERE [__$operation]=2 AND [__$start_lsn] BETWEEN @bglsn AND @edlsn
得到以下结果：
C、sys.fn_cdc_map_lsn_to_time 查询变更时间：
SELECT [__$operation] ,
CASE [__$operation] WHEN 1 THEN '删除' WHEN 2 THEN '插入' WHEN 3 THEN '更新（捕获的列值是执行更新操作前的值）'
WHEN 4 THEN '更新（捕获的列值是执行更新操作后的值）' END [类型],
sys.fn_cdc_map_lsn_to_time([__$start_lsn]) [更改时间] ,
name ,
DepartmentID ,
GroupName ,
ModifiedDate
FROM cdc.HumanResources_Department_CT
注意,由于该表刚好有一个modfieddate字段,所以和更改时间相同.
D、获取LSN边界：
SELECT sys.fn_cdc_get_max_lsn()[数据库级别的最大LSN],
sys.fn_cdc_get_min_lsn('cdc.HumanResources_Department_CT')[捕获实例的lsn]
结果如下：
这两个值可以用于上面提到的函数里面用于筛选数据之用。
