合 MSSQL变更数据捕获（CDC）和 更改跟踪（Chang Tracking）介绍
Tags： MSSQLSQL ServerCDC发布订阅变更数据捕获更改跟踪（Chang Tracking）
- CDC简介
- CDC实现过程
- 1. 启用CDC
- 2. 创建测试表，对表变更启用CDC
- 3. DML测试
- 4. 获取更改数据
- CDC的维护
- 1. 获取配置信息
- 2. 获取job信息
- 开启CDC数据同步实验
- 1、 数据库需要开启代理服务。
- 2、 数据库配置
- 3、 测试
- 4、 分析（系统自带数据库）
- CT与CDC的主要区别
- CT配置方法
- 1. 对数据库启用CT
- 2. 对表启用CT
- CT测试
- 1. 测试insert操作
- 2. 测试Update、Delete操作
- CT管理
- 1. 查看列变更说明
- 2. 使用Version关键字查看更改信息
- 3. 判断DML是由哪个应用产生的
- 4. 获取更改跟踪版本2之后的表数据
- 一些异常情况的处理
- (1) 数据库从2005恢复到2008，打开表级别的附加日志报错
- systranschemas (Transact-SQL)
- 参考
CDC简介
在2008版本之前，通常使用DML触发器监控对表数据库的变更，但是触发器的维护比较困难，性能也不高。2008推出了新功能 变更数据捕获（Change Data Capture，CDC）可以用捕获对表的DML操作，常用于ETL，同步至其他（类型）数据库。
当在一个表上启用CDC 时，SQL Server 会创建一个系统更改表，更改表包含元数据列及与被跟踪表相同的列。CDC 的数据源为 SQL Server 事务日志，在将DML应用于跟踪的源表时，捕获进程读取日志，将记录发生的更改记录到更改表中。系统还将提供一些变更数据查询函数，通过指定范围访问更改的数据，并以过滤结果集的形式返回。
对于启用CDC的表DDL操作不会被阻止，但新增列也不会被映射；如果是删除一列，目标库该表对应列将返回null值而不是被删除。可以为表创建另一个捕获实例映射新架构，每个表最多可以有2个跟踪实例。
CDC实现过程
1. 启用CDC
例如我们的测试库名为CDC_DB
1 2 3 4 5 6 7 8 9 10 11 | -- 启用数据库CDC USE CDC_DB GO -- 自建SqlServer使用 EXECUTE sys.sp_cdc_enable_db; GO -- 阿里云rds使用 exec sp_rds_cdc_enable_db; GO -- 检查启用是否成功 SELECT name,is_cdc_enabled FROM sys.databases; |
这个过程会在当前数据库下创建6个系统表
- cdc.captured_columns
- cdc.change_tables
- cdc.ddl_history
- cdc.index_columns
- cdc.lsn_time_mapping
- dbo.systranschemas
2. 创建测试表，对表变更启用CDC
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | /******* 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 EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 't1', @role_name = null; -- 检查是否成功 SELECT name, is_tracked_by_cdc FROM sys.tables WHERE OBJECT_ID= OBJECT_ID('dbo.Department'); |
1 2 | -- 返回某个表的变更捕获配置信息 EXEC sys.sp_cdc_help_change_data_capture 'dbo', 'Department' |
Department表启用CDC后，首先会创建cdc.dbo_Department_CT系统更改表（CT代表Capture Table，表名为：用户.架构_表_CT）。对于每一个表启用CDC的表，都会生成一个对应的更改表。
另外会创建两个作业 cdc.dbname_capture和cdc.dbname_cleanup（捕获和清理作业），清理作业默认凌晨２点执行，清除72小时以上的数据。如果同一数据库的表已经启用CDC，不会重建job。需要开启SQL Server Agent服务，不然会报错。
多了个数据库角色
3. DML测试
测试DML操作，观察cdc.dbo_Department_CT帮我们记录些什么。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | /******* 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 |
对于insert/delete操作，会有对应的一行记录，而对于update，会有两行记录。
__$operation列：1 = 删除、2= 插入、3= 更新（旧值）、4= 更新（新值）；后面几列与表原有列相同。
4. 获取更改数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | /******* 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 包含新值和旧值 */ |
CDC的维护
禁用表(“dbo.t1”)
1 | EXEC sys.sp_cdc_disable_table @source_schema = 'dbo', @source_name = 't1', @capture_instance = 'all'; |
禁用数据库CDC
1 | EXEC sys.sp_cdc_disable_db; |
1. 获取配置信息
1 2 3 4 5 6 7 8 9 | --返回所有表的变更捕获配置信息 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' |
由于前面 sys.sp_cdc_enable_table 的参数 @captured_column_list = NULL，所以dbo.Department表的所有字段都进行监控了，如果你只关心某些字段，可以在创建捕获时指定。
2. 获取job信息
1 2 3 4 5 6 | --所有数据库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 |
alwayson主从切换后，如果业务有配置CDC，目标库需要使用sp_cdc_add_job 创建cdc job（不要导出脚本然后在从库建，job执行会报错）
1 | EXEC sys.sp_cdc_add_job ``@job_type` `= N``'capture'``; ``EXEC sys.sp_cdc_add_job ``@job_type` `= N``'cleanup'``; |
Replication, Change Tracking, Change Data Capture, and AlwaysOn Availability Groups (SQL Server) | Microsoft Docs
对于用户表所做的 DML 更改，2008版本除了CDC之外，其实还有一个新增功能——更改跟踪（Chang Tracking，CT），它跟CDC有什么不同？如何配置和管理？
开启CDC数据同步实验
1、 数据库需要开启代理服务。
开启方式：
a). 点击开始菜单--》SQL Server配置管理工具--》SQLserver服务--》SQLserver代理（右键）启动
b). 打开电脑服务，找到SQLserver 代理，点击左侧状态
2、 数据库配置
a).首先查看数据库是否已经开启CDC服务
1 | SELECT name,is_cdc_enabled FROM sys.databases WHERE is_cdc_enabled = 1; |
返回已经开启CDC的数据库，如果为空，则所有的库都没有开启CDC服务，转到步骤b，对数据库开启CDC。如果有结果，则转到步骤c。
b).对数据库开启CDC服务
1 2 3 | USE test; -- 切换数据库 EXECUTE sys.sp_cdc_enable_db; -- 开启CDC功能 |
检查是否开启成功：
1 2 3 4 5 6 7 8 9 10 11 | SELECT is_cdc_enabled, CASE WHEN is_cdc_enabled=0 THEN 'CDC功能禁用' ELSE 'CDC功能启用' END 描述 FROM sys.databases WHERE NAME = 'test' ; |
创建成功后，将自动添加CDC用户和CDC架构。
在用户和架构下面可以看到cdc用户和cdc架构
c.查看当前已经开启CDC的数据表。
1 | SELECT name,is_tracked_by_cdc FROM sys.tables WHERE is_tracked_by_cdc = 1; |
d.开启表CDC
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | sys.sp_cdc_enable_table [ @source_schema = ] 'source_schema', [ @source_name = ] 'source_name' , [ @role_name = ] 'role_name' [,[ @capture_instance = ] 'capture_instance' ] [,[ @supports_net_changes = ] supports_net_changes ] [,[ @index_name = ] 'index_name' ] [,[ @captured_column_list = ] 'captured_column_list' ] [,[ @filegroup_name = ] 'filegroup_name' ] [,[ @partition_switch = ] 'partition_switch' ] |
示例：
对'USRALMHS'表开启变更捕获
1 2 3 4 5 6 7 8 9 | EXEC sys.sp_cdc_enable_table @source_schema= 'dbo', --源表架构 @source_name = 'USRALMHS', --源表 @role_name = 'CDC_Role' --角色（将自动创建） GO |
如果不想控制访问角色，则@role_name必须显式设置为null。
查询是否成功
1 2 3 4 5 6 7 8 9 | 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. USRALMHS ') |
此时，已经可以看到相关的cdc进程了：
对表开启成功后，可以查看数据库，在数据库系统表下增加了很多表。
在SQLserver 代理中多了两个作业：在可编程性-》函数-》表值函数里，也多了两个函数
3、 测试
a) 向表中插入数据
1 | insert into test.dbo.USRALMHS select top 1000 * from alarm.dbo.USRALMHS_copy3 |
在DBO_USRALMHS_CT中查看：
会有同样的1000 条数据，唯一不同的是在DBO_USRALMHS_CT中会多几个字段，分别代表不同的含义，其中最主要的是 __$operation 代表含义 1 删除、2插入、3更新前的内容、4更新后的内容 @bglsn 开始时间的时间戳 @edlsn 结束时间的时间戳
b) 测试更新和删除操作（生成数据的__$operation 不同）
省略
4、 分析（系统自带数据库）
a) 分析存储过程
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | -- 查询当前作业配置 SELECT * FROM MSDB.dbo.cdc_jobs -- 或者使用 USE AdventureWorks2008R2; GO EXEC sys.sp_cdc_help_jobs; GO |
1.sys.sp_cdc_add_job
在当前数据库中创建变更数据捕获清理或捕获作业
1.创建捕获作业
USE AdventureWorks2008R2;
GO
EXEC sys.sp_cdc_add_job
@job_type = N'capture';
GO
2.创建清理作业
---创建清理作业,作业连续运行,更改数据行将在更改表中保留2880分钟,清除时使用一条语句最多删除4000条记录
USE AdventureWorks2008R2;
GO
EXEC sys.sp_cdc_add_job
@job_type = N'cleanup'
,@start_job=1
,@retention=2880
,@threshold =4000
2.sys.sp_cdc_change_job
修改当前数据库中变更数据捕获清除或捕获作业的配置
--仅在使用 sp_cdc_stop_job 停止作业并使用 sp_cdc_start_job 重新启动该作业后，对该作业所做的更改才会生效
1.更改捕获作业
--将每个循环扫描最多处理的事务数更改为200，为了从日志中提取所有行而要执行的最大扫描循环50次
USE AdventureWorks2008R2;
GO
EXECUTE sys.sp_cdc_change_job
@job_type = N'capture',
@maxtrans = 200,
@maxscans = 50;
GO
2.更改清除作业,将记录保留时间更改为3440分钟
USE AdventureWorks2008R2;
GO
EXECUTE sys.sp_cdc_change_job
@job_type = N'cleanup',
@retention = 3440;
GO
3.sys.sp_cdc_cleanup_change_table
根据指定的 low_water_mark 值从当前数据库的更改表中删除行,重置更改表中的最小 __$start_lsn,并删除小于该值的数据.
将同时清除cdc.HR_Department_CT,cdc.lsn_time_mapping表的记录
USE AdventureWorks2008R2;
GO
EXEC sys.sp_cdc_cleanup_change_table
@capture_instance =N'HR_Department',
@low_water_mark=0x0000037D000000D30008,
@threshold=2000;
SELECT sys.fn_cdc_increment_lsn(sys.fn_cdc_get_max_lsn())
4.sys.sp_cdc_drop_job
从 msdb 中删除当前数据库的变更数据捕获清除或捕获作业。
--下例删除 AdventureWorks2008R2 数据库的清除作业和捕获作业
USE AdventureWorks2008R2;
GO
EXEC sys.sp_cdc_drop_job @job_type = N'cleanup';
USE AdventureWorks2008R2;
GO
EXEC sys.sp_cdc_drop_job @job_type = N'capture';
5.sys.sp_cdc_disable_db
对当前数据库禁用变更数据捕获
禁用当前对数据库中的所有表启用的变更数据捕获。与变更数据捕获相关的所有系统对象（如更改表、作业、存储过程和函数）都将被删除。sys.databases 目录视图中的数据库条目的 is_cdc_enabled 列设置为 0。
如果在禁用变更数据捕获时为数据库定义了很多捕获实例，则长时间运行事务可能导致 sys.sp_cdc_disable_db 的执行失败。通过在运行 sys.sp_cdc_disable_db 之前使用 sys.sp_cdc_disable_table 禁用单个捕获实例，可以避免此问题。
USE AdventureWorks2008R2;
GO
EXECUTE sys.sp_cdc_disable_db;
GO
6.sys.sp_cdc_disable_table
对当前数据库中指定的源表和捕获实例禁用变更数据捕获
删除与指定的源表和捕获实例相关联的变更数据捕获更改表和系统函数。它会删除任何与来自变更数据捕获系统表的指定捕获实例相关联的行，并将 sys.tables 目录视图中的表项的 is_tracked_by_cdc 列设置为 0。
---下例对 HumanResources.Department 表禁用了变更数据捕获
USE AdventureWorks2008R2;
GO
EXEC sys.sp_cdc_disable_table
@source_schema = N'HumanResources'
, @source_name = N'Department'
, @capture_instance = N'HR_Department' ---这里是定义的实例名称，在一开始创建捕获的时候创建的，这里也可以制定ALL（禁用表HumanResources.Department的所有捕获）,
7.sys.sp_cdc_enable_db
对当前数据库启用变更数据捕获。必须先对数据库执行此过程，然后才能对该数据库中的任何表启用变更数据捕获。变更数据捕获可记录应用到所启用的表中的插入、更新和删除活动，同时采用易于使用的关系格式提供变更详细信息。此操作将为已修改的行捕获反映了所跟踪源表列结构的列信息，同时还捕获将更改应用到目标环境所需的元数据。
将创建以全数据库为作用域的变更数据捕获对象，包括元数据表和 DDL 触发器。它还会创建 cdc 架构和 cdc 数据库用户，并将 sys.databases 目录视图中的数据库条目的 is_cdc_enabled 列设置为 1。
USE AdventureWorks2008R2;
GO
EXECUTE sys.sp_cdc_enable_db;
GO
8.sys.sp_cdc_enable_table
--对需要进行跟踪的表启动CDC,sys.sp_cdc_enable_table
/*
为当前数据库中指定的源表启用变更数据捕获。对表启用变更数据捕获时，应用于此表的每个数据操纵语言 (DML) 操作的记录都将写入事务日志中。
变更数据捕获进程将从日志中检索此信息，并将其写入可通过使用一组函数访问的更改表中。
*/
sys.sp_cdc_enable_table
[ @source_schema = ] 'source_schema', ---表所属的架构名
[ @source_name = ] 'source_name' ,----表名
[ @role_name = ] 'role_name'---是用于控制更改数据访问的数据库角色的名称。
[,[ @capture_instance = ] 'capture_instance' ]--是用于命名特定于实例的变更数据捕获对象的捕获实例的名称
[,[ @supports_net_changes = ] supports_net_changes ]---指示是否对此捕获实例启用净更改查询支持
[,[ @index_name = ] 'index_name' ]--用于唯一标识源表中的行的唯一索引的名称。index_name 为 sysname，并且可以为 NULL。如果指定，则 index_name 必须是源表的唯一有效索引。如果指定 index_name，则标识的索引列优先于任何定义的主键列，就像表的唯一行标识符一样。
[,[ @captured_column_list = ] 'captured_column_list' ]--需要对哪些列进行捕获。captured_column_list 的数据类型为 nvarchar(max)，并且可以为 NULL。如果为 NULL，则所有列都将包括在更改表中。
[,[ @filegroup_name = ] 'filegroup_name' ]--是要用于为捕获实例创建的更改表的文件组。
[,[ @partition_switch = ] 'partition_switch' ]--指示是否可以对启用了变更数据捕获的表执行 ALTER TABLE 的 SWITCH PARTITION 命令。allow_partition_switch 为 bit，默认值为 1。
9.sp_cdc_generate_wrapper_function
生成用于为 SQL Server 中可用的变更数据捕获查询函数创建包装函数的脚本
EXEC sys.sp_cdc_generate_wrapper_function
- sys.sp_cdc_help_change_data_capture
返回当前数据库中为变更数据捕获启用的每个表的变更数据捕获配置。最多可为每个源表返回两行，为每个捕获实例返回一行。
---返回制定表的捕获信息
USE AdventureWorks2008R2;
GO
EXECUTE sys.sp_cdc_help_change_data_capture
@source_schema = N'HumanResources', --架构名
@source_name = N'Department';--表名
GO
--返回所有表的捕获信息
USE AdventureWorks2008R2;
GO
EXECUTE sys.sp_cdc_help_change_data_capture
11.sys.sp_cdc_get_captured_columns
返回指定捕获实例所跟踪的捕获源列的变更数据捕获元数据信息。
USE AdventureWorks2008R2;
GO
EXECUTE sys.sp_cdc_get_captured_columns
@capture_instance = N'HR_Department';
GO
12.sys.sp_cdc_get_ddl_history
返回自对指定的捕获实例启用变更数据捕获后与该捕获实例关联的数据定义语言 (DDL) 更改历史记录。
与查询表是一样的结果
SELECT * FROM cdc.ddl_history
USE AdventureWorks2008R2;
GO
