初探 SQL Server 2008 中的更改跟踪与变更数据捕获

1 概述

1.1 更改跟踪

1.2 变更数据捕获

1.3 比较更改跟踪和变更数据捕获

2 使用

2.1 更改跟踪

2.2 变更数据捕获

1 概述

“更改跟踪”和“变更数据捕获”捕获和记录用户表的DML更改（插入、更新和删除操作），为某些有特殊需求的应用程序服务。

1.1 更改跟踪

更改跟踪捕获表的数据行更改这一行为，但不会捕获更改的具体数据。捕获的结果包含表的主键及相关的跟踪信息（例如更改的操作类型、更新操作影响的列等）。

应用程序可以利用这个捕获的结果来确定表的最新更新，并可以关联原始来来获取最新的数据。

1.2 变更数据捕获

变更数据捕获使用异步进程读取事务日志，获取DML更改实际数据做为数据捕获的结果。在捕获结果中，还包含更改相关的一些信息（例如更改的操作类型、更新操作影响的列等）。

应用程序可以从捕获结果中获取DML更改的全部数据，而无需查询数据变更的原始表。

1.3 比较更改跟踪和变更数据捕获

比较更改跟踪和变更数据捕获，它们的异同情况如下表所示。

功能	更改跟踪	变更数据捕获
跟踪的更改	DML更改（插入、删除、更新）	DML更改（插入、删除、更新）
跟踪的信息	更新涉及的列 DML类型 更改行的主键列值	更新涉及的列 DML类型 历史变更数据
适用的版本	所有版本	仅企业版
实时性	与数据更改同步	异步读取事务日志
可控性	n 数据库级别的自动清理设置 启用或禁用，及跟踪信息的保存期 n 表级别的跟踪设置 启用或者禁用，可选择是否记录UPDATE操作影响的列 无法限制要跟踪的更改类型	n 数据库级别的清理及捕获设置 启用或禁用捕获/清除作业，及作业相关的参数：例如数据捕获的模式（单次触发模式和连续模式）、清除作业要保留的捕获记录的保留期等 n 表级别的捕获设置 启用或者禁用，每个表可以设置<=2个捕获实例，可选择是否要捕获的列的列表及为启用净更改记录查询提供的唯一索引名 无法限制要捕获的数据变更类型
历史数据查询	无法查询历史数据 对于变更信息的查询，仅能查询最新更改跟踪信息（起始于某个版本，或者基于某个特定的主键值的最新跟踪信息）	只要历史数据未被清除，就可以查询
对表的要求	表必须有主键	无特别要求
对表结构修改的限制	无法进行与主键相关的DDL操作（删除主键定义、修改主键列定义、禁用主键）	为表启用变更数据捕获后，只能由服务器角色sysadmin成员、数据库角色db_owner、db_ddladmin 成员能将DDL操作应用于该表； 另外，修改@index_name参数指定的唯一索引或者相关列之前，需要先禁用数据变更捕获才能修改。 @index_name在启用表的变更数据捕获设定，如果未指定，但该表有主键，则@index_name为该表的主键名称（主键在启用变更数据捕获后建立的话，不受此限制） 特别注意： 修改表结构之前，还应该检查数据捕获表中的数据，如果表结构修改操作无法应用到数据捕获表中，则会导致数据捕获Job失败。例如，如果要把某个列的定义从varchar改成int，原始表中该列的数据都可以转换成int，但数据捕获表中包含无法转换成int的数据，则表结构修改操作会成功，但由于数据捕获表无法应用这个DDL，所以数据捕获的Job会失败
TRUNCATE TABLE	不会跟踪TRUNCATE TABLE删除的行，并且会更新最低有效版本。当应用程序检查其版本时，检查结果会表明该版本太陈旧，需要进行重新初始化。这与禁用后又重新启用表的更改跟踪的效果相同	不允许
ALTER TABLE SWITCH	不允许	在配置时可以设置是否允许，但始终不会捕获此操作导致的数据变更
对SQL Agent的要求	无	使用Job进行数据捕获及历史数据清除，因此需要SQL Agent服务的支持
对DML的影响	与添加一个索引导致的性能开销差不多	异步读取事务日志来获取数据，所以基本上不会有影响。 日志读取使用sp_replcmds存储过程，如果数据库上配置有事务复制，则与事务复制共用日志读取器，否则使用单独的job
对存储的影响	n 内部更改表 启用了更改跟踪的每个用户表都有一个内部更改表。对于用户表中每行的每个更改，都会向内部更改表中添加一行，该行的大小=较小的固定开销+大小等于主键列大小的可变开销+由应用程序设置的可选上下文信息（使用WITH CHANGE_TRACKING_CONTEXT）+UPDATE影响的列（4字节，启用列跟踪的情况下才有） n 内部事务表 每个数据库一个。对于每个已提交的事务，都会向内部事务表中添加一行	每个捕获的实例对应一张名为cdc.<capture_instance>_CT的变更表，该表包含捕获实例需要捕获的所有列及捕获信息列。对源表执行的每个插入和删除操作，都会在变更表中插入一行；为对源表执行的每个更新操作，将插入两行：一行为更新前的值，一行为更新后的值。
其他		Ø 对于稀疏列，不支持在使用列集时捕获更改 Ø 不跟踪xml类型列中，对单个 XML元素的更改

2 使用

下面用两个示例简单说明更改跟踪和变更数据捕获的配置及变更信息的查询。

2.1 更改跟踪

更改跟踪的配置如下：

a. 在数据库上启用更改跟踪（ALTER DATABASE … CHANGE_TRACKING = ON），并设置跟踪结果保持期；

b. 在需要跟踪更改的每个表上启用更改跟踪（ALTER TABLE … ENABLE CHANGE_TRACKING），并设置是否要求记录UPDATE的列信息。（启用更改跟踪的表需要有主键）。

更改跟踪结果的查询包括：

a. CHANGE_TRACKING_CURRENT_VERSION

返回与上次提交的事务相关联的版本号。启用了更改跟踪的数据库具有一个版本计数器，在对启用了更改跟踪的表进行更改时，该计数器会随之递增。每个更改的行都有一个关联的版本号。可以在每次查询完成后，记录这个版本号，下次查询时，基于这个版本号查询，以获取后续的最新更改。

b. CHANGE_TRACKING_MIN_VALID_VERSION

指定表可用的最低有效版本号。在第一次查询数据的时候，可以使用此函数得到查询更改信息的起始版本号；

c. CHANGETABLE(CHANGES)

返回自指定版本起对表所做的所有更改的跟踪信息；

d. CHANGETABLE(VERSION)

返回指定行的最新更改跟踪信息。（通过指定特定行对应的主键列值）；

e. CHANGE_TRACKING_IS_COLUMN_IN_MASK

通过CHANGETABLE(CHANGES …)函数返回的SYS_CHANGE_COLUMNS值及列id，确定该列是否被UPDATE。

下面的T-SQL示例创建一个测试数据库，并在测试数据库中演示配置更改跟踪及查询更改跟踪信息。

-- ====================================================

-- 测试的数据库

USE master;

GO

CREATE DATABASE DB_test;

GO

ALTER DATABASE DB_test SET

CHANGE_TRACKING = ON(

AUTO_CLEANUP = ON, -- 打开自动清理选项

CHANGE_RETENTION = 1 HOURS -- 数据保存期为1 时

);

GO

-- ====================================================

-- 测试的表

USE DB_test;

GO

CREATE TABLE dbo.tb(

id int

CONSTRAINT PK_tb_id PRIMARY KEY,

col1 int,

col2 varchar(10),

col3 nvarchar(max),

col4 varbinary(max),

col5 xml

);

GO

ALTER TABLE dbo.tb

ENABLE CHANGE_TRACKING

WITH(

TRACK_COLUMNS_UPDATED = ON -- 记录UPDATE 的列信息

);

GO

SELECT

CHANGE_TRACKING_CURRENT_VERSION(),

CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'dbo.tb'));

GO

-- ====================================================

-- 数据测试

-- a. 插入初始数据

INSERT dbo.tb(

id,

col1, col2, col3, col4, col5)

VALUES(

1,

1, 'AA', 'AAA', 0x1, '<a>aa</a>'),

(

2,

2, 'BB', 'BBB', 0x2, '<b/>'),

(

3,

3, 'CC', 'CCC', 0x2, '<c/>');

SELECT

CHANGE_TRACKING_CURRENT_VERSION(),

CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'dbo.tb')),

*

FROM CHANGETABLE(CHANGES dbo.tb, 0) CHG

LEFT JOIN dbo.tb DATA

ON DATA.id = CHG.id;

-- b. 更新数据

BEGIN TRAN;

UPDATE dbo.tb SET

col1 = 11

WHERE id = 1;

UPDATE dbo.tb SET

col1 = 111

WHERE id = 1;

COMMIT TRAN;

SELECT

CHANGE_TRACKING_CURRENT_VERSION(),

CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'dbo.tb')),

*

FROM CHANGETABLE(CHANGES dbo.tb, 0) CHG

LEFT JOIN dbo.tb DATA

ON DATA.id = CHG.id;

-- c. 更新xml 和varbinary(max) 数据

UPDATE dbo.tb SET

col5.modify('replac