合 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分钟
