SQL Server 变更数据捕获（CDC）vs 更改跟踪（Chang Tracking）

Hehuyi_In

已于 2023-01-15 21:23:25 修改

阅读量4.8k

点赞数

分类专栏：数据同步 SQLServer 监控文章标签： 1024程序员节

于 2020-10-24 00:00:20 首次发布

原文链接：https://www.cnblogs.com/gaizai/p/3479731.html

版权

SQLServer 同时被 3 个专栏收录

142 篇文章 24 订阅

订阅专栏

数据同步

26 篇文章 0 订阅

订阅专栏

监控

18 篇文章 0 订阅

订阅专栏

一、 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

--启用数据库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;

启动之后会自动创建一些系统表、新用户和架构

2. 创建测试表，对表变更启用CDC

/******* 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

--检查是否成功
SELECT name, is_tracked_by_cdc
FROM sys.tables
WHERE OBJECT_ID= OBJECT_ID('dbo.Department');

--返回某个表的变更捕获配置信息
EXEC sys.sp_cdc_help_change_data_capture 'dbo', 'Department'

Department表启用CDC后，首先会创建cdc.dbo_Department_CT系统更改表（CT代表Capture Table，表名为：用户.架构_表_CT）。对于每一个表启用CDC的表，都会生成一个对应的更改表。

另外会创建两个作业 cdc.CDC_DB_capture和cdc.CDC_DB_cleanup（捕获和清理作业），清理作业默认凌晨２点执行，清除72小时以上的数据。如果同一数据库的表已经启用CDC，不会重建job。需要开启SQL Server Agent服务，不然会报错。

多了个数据库角色

3. DML测试

测试DML操作，观察cdc.dbo_Department_CT帮我们记录些什么。

/******* 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. 获取更改数据

/******* 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的维护

1. 获取配置信息

--返回所有表的变更捕获配置信息
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信息

--所有数据库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执行会报错）

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有什么不同？如何配置和管理？

四、 CT与CDC的主要区别

CDC是异步程序，CT是轻量级的同步程序
CDC使用异步捕获进程读取事务日志，CT作为事务的一部分，不需要读取事务日志
CDC记录被跟踪表完整的修改历史，CT只会记录最后的更改数据，没有中间值

五、 CT配置方法

1. 对数据库启用CT

--启用更改跟踪（Chang Tracking），2天清理一次（HOURS）
ALTER DATABASE CT_DB
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS,
AUTO_CLEANUP = ON)

--查看数据库是否启用更改跟踪
SELECT DB_NAME(database_id) DataBaseName,is_auto_cleanup_on,
retention_period,retention_period_units_desc
FROM sys.change_tracking_databases

2. 对表启用CT

--创建测试表
USE CT_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

--对表启用更改跟踪
ALTER TABLE  [dbo].[Department]
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)

--查看表是否启用更改跟踪
SELECT OBJECT_NAME(object_id) TableName,is_track_columns_updated_on
FROM sys.change_tracking_tables

六、 CT测试

1. 测试insert操作

分两次把数据插入到表，查看CT信息，可以发现，两次不同的插入会生成两个版本，说明CT跟DML操作是同步的。

--测试插入数据（版本将变成1）
INSERT  INTO dbo.Department(
    Name ,
    GroupName ,
    ModifiedDate
)VALUES('Marketing','Sales and Marketing',GETDATE())

--再次测试插入数据（版本将变成2）
INSERT  INTO dbo.Department(
    Name ,
    GroupName ,
    ModifiedDate
)VALUES
('Production','Manufacturing',GETDATE()),
('Purchasing','Inventory Management',GETDATE());

--表记录
SELECT * FROM dbo.Department;

--当前版本
SELECT CHANGE_TRACKING_CURRENT_VERSION () AS CURRENT_VERSION;

--最小版本
SELECT CHANGE_TRACKING_MIN_VALID_VERSION (OBJECT_ID('dbo.Department')) AS MIN_VERSION;

--使用Changes关键字查看更改信息
SELECT DepartmentID,SYS_CHANGE_OPERATION,SYS_CHANGE_VERSION FROM CHANGETABLE(CHANGES dbo.Department, 0) AS CT;

2. 测试Update、Delete操作

--测试更新数据（版本将变成3）
UPDATE dbo.Department SET Name = 'Marketing Group',ModifiedDate = GETDATE() WHERE Name = 'Marketing';
--测试删除数据（版本将变成4）
DELETE FROM dbo.Department WHERE Name='Production';

--表记录
SELECT * FROM dbo.Department;

--当前版本
SELECT CHANGE_TRACKING_CURRENT_VERSION () AS CURRENT_VERSION;

--最小版本
SELECT CHANGE_TRACKING_MIN_VALID_VERSION (OBJECT_ID('dbo.Department')) AS MIN_VERSION;

--使用Changes关键字查看更改信息
SELECT DepartmentID,SYS_CHANGE_OPERATION,SYS_CHANGE_VERSION FROM CHANGETABLE(CHANGES dbo.Department, 0) AS CT;

七、 CT管理

1. 查看列变更说明

--返回哪些列被修改，1为真,0为假
SELECT DepartmentID,
CHANGE_TRACKING_IS_COLUMN_IN_MASK(
COLUMNPROPERTY(
OBJECT_ID('dbo.Department'),'Name', 'ColumnId') ,
SYS_CHANGE_COLUMNS) '是否改变Name',
CHANGE_TRACKING_IS_COLUMN_IN_MASK(
COLUMNPROPERTY(
OBJECT_ID('dbo.Department'),'GroupName', 'ColumnId') ,
SYS_CHANGE_COLUMNS) '是否改变GroupName',
CHANGE_TRACKING_IS_COLUMN_IN_MASK(
COLUMNPROPERTY(
OBJECT_ID('dbo.Department'), 'ModifiedDate', 'ColumnId') ,
SYS_CHANGE_COLUMNS) '是否改变ModifiedDate'
FROM CHANGETABLE(CHANGES dbo.Department, 2) AS CT
WHERE SYS_CHANGE_OPERATION = 'U'

2. 使用Version关键字查看更改信息

SELECT * FROM dbo.Department d
CROSS APPLY CHANGETABLE(VERSION dbo.Department, (DepartmentID), (d.DepartmentID)) AS ct

3. 判断DML是由哪个应用产生的

--设置跟踪外部程序上下文信息，通过在外部应用程序中的上下文信息判断这个DML是由哪个应用产生的
DECLARE @context VARBINARY(128) = CAST('我要插入记录' AS VARBINARY(128));
WITH CHANGE_TRACKING_CONTEXT (@context)
--测试插入数据（版本将变成）
INSERT  INTO dbo.Department(
    Name ,
    GroupName ,
    ModifiedDate
)VALUES('Document Control','Quality Assurance',GETDATE())

--查询Context更改
SELECT DepartmentID,SYS_CHANGE_OPERATION,SYS_CHANGE_VERSION,
CAST(SYS_CHANGE_CONTEXT AS VARCHAR) ApplicationContext
FROM CHANGETABLE(CHANGES dbo.Department, 4) AS CT

4. 获取更改跟踪版本2之后的表数据

SELECT SYS_CHANGE_OPERATION,SYS_CHANGE_VERSION,SYS_CHANGE_COLUMNS,D.*
FROM CHANGETABLE(CHANGES dbo.Department, 2) AS CT
LEFT JOIN dbo.Department AS D
ON CT.DepartmentID = D.DepartmentID

在更改跟踪的记录中包括了表Department 的唯一编号，还有DML的操作符字段SYS_CHANGE_OPERATION，枚举这些值（I=Insert、U=Update、D=Delete），还有DML操作的版本号：SYS_CHANGE_VERSION，它是每进行一次DML，都会递增一个版本号，所以你可以针对I=Insert、U=Update、D=Delete不同的类型加上版本号过滤，就可以找到那些数据进行了更新。

参考

变更数据捕获

变更数据捕获基本知识

跟踪数据更改 (SQL Server)

关于变更数据捕获 (SQL Server)

关于CDC功能的答疑

SQL Server 2008中新增的变更数据捕获（CDC）和更改跟踪

跟踪数据更改

SQL Server 更改跟踪（Chang Tracking）监控表数据 - 听风吹雨 - 博客园

Hehuyi_In

关注

0
点赞
踩
8

收藏

觉得还不错? 一键收藏
0
评论
SQL Server 变更数据捕获（CDC）vs 更改跟踪（Chang Tracking）

一、简介在2008版本之前，通常使用DML触发器监控对表数据库的变更，但是触发器的维护比较困难，性能也不高。2008推出了新功能变更数据捕获（Change Data Capture，即CDC）。二、实现过程（Realization）1. 启用CDC例如我们的测试库名为CDC_DB--启用数据库CDC USE CDC_DB GO EXECUTE sys.sp_cdc_enable_db; GO --检查启用是否成功 SELECT is_cdc_enabled,CA.
复制链接

扫一扫