初探 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   更改跟踪

更改跟踪的配置如下：

1. 在数据库上启用更改跟踪（ALTER DATABASE … CHANGE_TRACKING = ON），并设置跟踪结果保持期；
2. 在需要跟踪更改的每个表上启用更改跟踪（ALTER TABLE … ENABLE CHANGE_TRACKING），并设置是否要求记录UPDATE的列信息。（启用更改跟踪的表需要有主键）。

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

1. CHANGE_TRACKING_CURRENT_VERSION

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

1. CHANGE_TRACKING_MIN_VALID_VERSION

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

1. CHANGETABLE(CHANGES)

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

1. CHANGETABLE(VERSION)

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

1. 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('replace value of /a[1]/text()[1] with "replace"')

WHERE id = 1;

 

UPDATE dbo.tb SET

    col5.modify('insert <a>1</a> as last into /')

WHERE id = 2;

 

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;

 

UPDATE dbo.tb SET

    col4 = col4 + 0x12345

WHERE id = 3;

 

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;

 

-- d. 更新主键

UPDATE dbo.tb SET

    id = 11

WHERE id = 1;

 

INSERT dbo.tb(

    id,

    col1, col2, col3, col4, col5)

VALUES(

    1,

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

 

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;

 

SELECT

    CHANGE_TRACKING_CURRENT_VERSION(),

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

    *

FROM dbo.tb DATA

    OUTER APPLY CHANGETABLE(VERSION dbo.tb, (id), (DATA.id)) CHG

 

 

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

-- 删除测试

/*--

USE master;

GO

ALTER DATABASE DB_test SET

    SINGLE_USER

    WITH

        ROLLBACK AFTER 0;

GO

DROP DATABASE DB_test;

--*/

2.2   变更数据捕获

变更数据捕获配置如下：

1. 在数据库上启用变更数据捕获（调用系统存储过程sys.sp_cdc_enable_db）；
2. 通过系统存储过程sys.sp_cdc_add_job创建捕获和清理Job（可选，如果没有捕获和清理Job，会在创建数据库中的第一个变更数据捕获时自动建立，自动建立的Job可以通过调用系统存储过程sys.sp_cdc_change_job来调整捕获和清理相关的一些选项）；
3. 在需要捕获变更数据的每个表上建立变更数据捕获实例（每个表上可以建立<=2个捕获实例，创建捕获实例使用系统存储过程sys.sp_cdc_enable_table）。

捕获的变更数据的查询包括：

1. sys.fn_cdc_get_min_lsn

返回指定捕获实例的有效性间隔的低端点（start_lsn）；

1. sys.fn_cdc_get_max_lsn

返回cdc.lsn_time_mapping系统表的最大日志序列号(LSN)；

1. cdc.fn_cdc_get_all_changes_<捕获实例>

针对在指定日志序列号(LSN)范围内应用到源表的每项更改均返回一行。如果源行在该间隔内有多项更改，则每项更改都会表示在返回的结果集中。除了返回更改数据外，四个元数据列还提供了将更改应用到另一个数据源所需的信息。行筛选选项可控制元数据列的内容以及结果集中返回的行。当指定“all”行筛选选项时，针对每项更改将只有一行来标识该更改。当指定“all update old”选项时，更新操作会表示为两行：一行包含更新之前已捕获列的值，另一行包含更新之后已捕获列的值。

此枚举函数是在对源表启用变更数据捕获时创建的。此函数名称是派生的，采用cdc.fn_cdc_get_all_changes_capture_instance格式，其中capture_instance是在对源表启用变更数据捕获时为捕获实例指定的值；

1. cdc.fn_cdc_get_net_changes_<capture_instance>

针对指定日志序列号(LSN)范围内每个已更改的源行返回一个净更改行。净更改行指：如果在LSN范围内源行具有多项更改，则该函数将返回反映该行最终内容的单一行。例如，如果事务在源表中插入一行，并且LSN范围内的后续事务更新了该行中的一个或多个列，则该函数将只返回一行，其中包含多个更新的列值。

此枚举函数是在对某源表启用变更数据捕获并指定净跟踪时创建的。函数名称是派生的，采用cdc.fn_cdc_get_net_changes_capture_instance格式，其中capture_instance是对变更数据捕获启用源表时为捕获实例指定的值；

1. sys.fn_cdc_map_time_to_lsn

为指定的时间返回cdc.lsn_time_mapping系统表中start_lsn列中的日志序列号(LSN)值；

1. sys.fn_cdc_has_column_changed

标识指定的更新掩码是否指示已更新关联的更改行中的指定列。

下面的T-SQL示例创建一个测试数据库，并在测试数据库中演示配置变更数据捕获及查询捕获结果。

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

-- 测试的数据库

USE master;

GO

CREATE DATABASE DB_test;

GO

 

-- 启用变更数据捕获

USE DB_test;

EXEC sys.sp_cdc_enable_db;

GO

 

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

-- 检查SQL Server Agent 服务的状态，如果未启动，则启动它

DECLARE

    @agnt_service sysname;

SET @agnt_service = N'SQLServerAgent';

 

DECLARE @tb_agent_status TABLE(

    state varchar(50)

);

INSERT @tb_agent_status

EXEC master.sys.xp_servicecontrol

    N'QUERYSTATE',

    @agnt_service;

 

IF NOT EXISTS(

        SELECT * FROM @tb_agent_status

        WHERE state = N'Running.')

    EXEC master.sys.xp_servicecontrol

        N'START',

        @agnt_service;

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

 

-- 创建一个变更数据捕获实例- 所有列

-- 创建数据库中的第一个变更数据捕获实例的时候，数据捕获和清理的JOB 会自动创建

-- 可以通过sys.sp_cdc_change_job 这个存储过程去调整捕获和清理的相关设置

-- 也可以在创建第一个变更数据捕获实例前，使用sys.sp_cdc_add_job去创建数据捕获和清理Job，在创建时做好相关的设置

EXEC sys.sp_cdc_enable_table

    @source_schema = N'dbo',

    @source_name = N'tb',

    @capture_instance = N'dbo_tb',

    @role_name = NULL;

 

-- 创建一个变更数据捕获实例- 特定列

EXEC sys.sp_cdc_enable_table

    @source_schema = N'dbo',

    @source_name = N'tb',

    @capture_instance = N'dbo_tb_col',

    @role_name = NULL,

    @captured_column_list = N'id,col1,col2';

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/>');

 

WITH

LSN AS(

    SELECT

        from_lsn = sys.fn_cdc_get_min_lsn(N'dbo_tb'),

        to_lsn = sys.fn_cdc_get_max_lsn()

),

CHG_ALL AS(

    SELECT

        CHG.*

    FROM LSN

        CROSS APPLY cdc.fn_cdc_get_all_changes_dbo_tb(LSN.from_lsn, LSN.to_lsn, 'ALL UPDATE OLD') CHG

),

CHG_NET AS(

    SELECT

        CHG.*

    FROM LSN

        CROSS APPLY cdc.fn_cdc_get_net_changes_dbo_tb(LSN.from_lsn, LSN.to_lsn, 'ALL') CHG

)

SELECT * FROM CHG_ALL;

 

-- b. 更新数据

BEGIN TRAN;

    UPDATE dbo.tb SET

        col1 = 11

    WHERE id = 1;

 

    UPDATE dbo.tb SET

        col1 = 111

    WHERE id = 1;

COMMIT TRAN;

 

WITH

LSN AS(

    SELECT

        from_lsn = sys.fn_cdc_get_min_lsn(N'dbo_tb'),

        to_lsn = sys.fn_cdc_get_max_lsn()

),

CHG_ALL AS(

    SELECT

        CHG.*

    FROM LSN

        CROSS APPLY cdc.fn_cdc_get_all_changes_dbo_tb(LSN.from_lsn, LSN.to_lsn, 'ALL UPDATE OLD') CHG

),

CHG_NET AS(

    SELECT

        CHG.*

    FROM LSN

        CROSS APPLY cdc.fn_cdc_get_net_changes_dbo_tb(LSN.from_lsn, LSN.to_lsn, 'ALL') CHG

)

SELECT * FROM CHG_ALL;

 

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

UPDATE dbo.tb SET

    col5.modify('replace value of /a[1]/text()[1] with "replace"')

WHERE id = 1;

 

UPDATE dbo.tb SET

    col5.modify('insert <a>1</a> as last into /')

WHERE id = 2;

 

UPDATE dbo.tb SET

    col4 = col4 + 0x12345

WHERE id = 3;

 

WITH

LSN AS(

    SELECT

        from_lsn = sys.fn_cdc_get_min_lsn(N'dbo_tb'),

        to_lsn = sys.fn_cdc_get_max_lsn()

),

CHG_ALL AS(

    SELECT

        CHG.*

    FROM LSN

        CROSS APPLY cdc.fn_cdc_get_all_changes_dbo_tb(LSN.from_lsn, LSN.to_lsn, 'ALL UPDATE OLD') CHG

),

CHG_NET AS(

    SELECT

        CHG.*

    FROM LSN

        CROSS APPLY cdc.fn_cdc_get_net_changes_dbo_tb(LSN.from_lsn, LSN.to_lsn, 'ALL') CHG

)

SELECT * FROM CHG_ALL;

 

-- d. 更新主键

UPDATE dbo.tb SET

    id = 11

WHERE id = 1;

 

INSERT dbo.tb(

    id,

    col1, col2, col3, col4, col5)

VALUES(

    1,

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

 

WITH

LSN AS(

    SELECT

        from_lsn = sys.fn_cdc_get_min_lsn(N'dbo_tb'),

        to_lsn = sys.fn_cdc_get_max_lsn()

),

CHG_ALL AS(

    SELECT

        CHG.*

    FROM LSN

        CROSS APPLY cdc.fn_cdc_get_all_changes_dbo_tb(LSN.from_lsn, LSN.to_lsn, 'ALL UPDATE OLD') CHG

),

CHG_NET AS(

    SELECT

        CHG.*

    FROM LSN

        CROSS APPLY cdc.fn_cdc_get_net_changes_dbo_tb(LSN.from_lsn, LSN.to_lsn, 'ALL') CHG

)

SELECT * FROM CHG_ALL;

 

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

-- 删除测试

/*--

USE master;

GO

ALTER DATABASE DB_test SET

    SINGLE_USER

    WITH

        ROLLBACK AFTER 0;

GO

DROP DATABASE DB_test;

--*/

文章来源：http://blog.csdn.net/zjcxc/article/details/3975644