与SQL SERVER 2008 CDC 异步捕获数据变更的不同,更改跟踪是同步进程,是DML(INSERT/UPDATE/DELETE)事务的一部分,它可以使用最小的C盘存储开销来侦测数据行的净变更.那么它也就不能像CDC那样可以提供用户表的历史更改信息.更改是使用异步进程捕获的,此进程读取事务日志,并且对系统造成的影响很小.
更改跟踪捕获更改了表行这一事实,但不会捕获更改的数据.这样,应用程序就可以确定使用从用户表中直接获取的最新行数据更改的行.因此,与变更数据捕获相比,更改跟踪可以解答的历史问题比较有限.但是,对于不需要历史信息的那些应用程序,更改跟踪产生的存储开销要小得多,因为它不需要捕获更改的数据(不需要触发器和表时间戳).它使用同步跟踪机制来跟踪更改.此功能旨在最大限度地减少DML 操作开销.
总的来说有以下几点:
1.减少了开发时间:由于SQL Server 2008 中提供了更改跟踪功能,因此无需开发自定义解决方案.
2.不需要架构更改:使用更改跟踪不需要执行以下任务:添加列;添加触发器;如果无法将列添加到用户表,则需要创建要在其中跟踪已删除的行或存储更改跟踪信息的端表.o
3.内置清除机制:更改跟踪的清除操作在后台自动执行.不需要端表中存储的数据的自定义清除.
4.提供更改跟踪功能的目的是获取更改信息:使用更改跟踪功能可使信息查询和使用更方便.列跟踪记录提供与更改的数据相关的详细信息.
5.降低了DML 操作的开销:同步更改跟踪始终会有一些开销.但是,使用更改跟踪有助于使开销最小化.开销通常会低于使用其他解决方案,对于需要使用触发器的解决方案,尤其如此.
6.更改跟踪是基于提交的事务进行的:更改的顺序基于事务提交时间.在存在长时间运行和重叠事务的情况下,这样可获得可靠的结果.必须专门设计使用timestamp
值的自定义解决方案,以处理这些情况.
7.配置和管理更改跟踪的标准工具:SQL Server 2008 提供标准的DDL 语句、SQL Server Management Studio,目录视图和安全权限.
具体步骤:
1.建立测试数据库
IF NOT EXISTS (SELECT name FROM SYS.databases WHERE name = N'CHANGE_TRACK_DB')
BEGIN
CREATE DATABASE CHANGE_TRACK_DB
END
要启用数据库更改跟踪功能,需要配置CHANGE_TRACKING数据库选项.也可以配置跟踪的数据在数据库保留多久,以及是否启用自动清除.配置保留期将会影响到需要维护的跟踪数据的大小.该值过高可能会影响存储.太低的话在远程应用程序同步不够的情况下,会引发通另一应用程序的同步问题.
2.配置更改跟踪
ALTER DATABASE CHANGE_TRACK_DB
SET CHANGE_TRACKING = ON(CHANGE_RETENTION = 36HOURS, AUTO_CLEANUP = ON)
使用更改跟踪时的最佳实践是为数据库启用快照隔离.不使用快照隔离会引发事务不一致的变更信息.对有显著DML活动的数据库和表,以一致的方式捕获更改跟踪的信息很重要(抓取最新版本并使用该版本号来获取适当的数据)由于行版本的生成,启用快照隔离会在tempdb中增加额外的使用空间.会带来I/O开销的增加.
3.启用快照隔离
ALTER DATABASE CHANGE_TRACK_DB
SET ALLOW_SNAPSHOT_ISOLATION ON
GO
4.通过查询sys.change_tracking_databases来确认数据库是否以正确启用更改跟踪.
SELECT DB_NAME(DATABASE_ID) AS [DB_NAME], IS_AUTO_CLEANUP_ON, RETENTION_PERIOD, RETENTION_PERIOD_UNITS_DESC
FROM sys.change_tracking_databases
GO
5.创建测试表
USE CHANGE_TRACK_DB
GO
CREATE TABLE CHANGE_TRACKING_USER
(USERID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
NAME VARCHAR(20) NOT NULL,
ADDRESS VARCHAR(100) NOT NULL)
GO
对于要打开更改跟踪以及要跟踪哪些列被跟新了的表,需要打开表的CHANGE_TRACKING选项和TRACK_COLUMNS_UPDATED选项.
ALTER TABLE CHANGE_TRACKING_USER
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED= ON)
6.查询sys.change_tracking_tables目录视图可以获得启用跟踪更改的详细信息.
SELECT OBJECT_NAME(OBJECT_ID) AS [TB_NAME], IS_TRACK_COLUMNS_UPDATED_ON
FROM sys.change_tracking_tables
GO
7.对表进行插入数据来捕获更改跟踪.
INSERT CHANGE_TRACKING_USER(NAME, ADDRESS)
VALUES('Kobe','Lakers'),
('Jordon', 'Bull'),
('Wade', 'Heat'),
('Howard', 'Magic')
GO
8.查看正在同步的是一个函数CHANGE_TRACKING_CURRENT_VERSION(),返回的是最后提交的事务的版本号.所有发生在启用更改跟踪表中的DML操作都会照成版本号的增长.版本号用来确定更改.
SELECT CHANGE_TRACKING_CURRENT_VERSION()
9.函数CHANGE_TRACKING_MIN_VALID_VERSION()可以获得表的最小可用版本号.如果断开连接的程序不同步的时间超过了更改跟踪保留期限.那么就要对应用程序的数据进行彻底的刷新.
SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('CHANGE_TRACKING_USER'))
10.对于更改的侦测我们可以用函数CHANGETABLE.该函数有种用法:使用CHANGES关键字来检测从指定的同步版本以来发生的更改;或者使用VERSION关键字来返回最新的更改跟踪版本.
SELECT USERID --返回的是主键
,SYS_CHANGE_OPERATION --I 代表INSERT, U代表UPDATE, D代表DELETE
,SYS_CHANGE_VERSION --返回的是版本号,因为这条数据是在同一个INSERT中添加的,所以下面的结果版本号相同
FROM CHANGETABLE(CHANGES CHANGE_TRACKING_USER, 0) A -- 此函数返回的是自版本以来的更改.第一个参数是表名称
11.当收集同步信息时,使用SET TRANSACTION ISOLATION LEVEL SNAPSHOT 和BEGIN TRAN..COMMIT TRAN来封装收集的更改信息和相关的当前更改跟踪版本以及最小的可用版本.使用快照隔离允许更改跟踪的数据具有事务一致性的形式.
UPDATE CHANGE_TRACKING_USER
SET NAME = 'Kobe Bryant'
WHERE USERID = 1
UPDATE CHANGE_TRACKING_USER
SET ADDRESS = 'Lakers'
WHERE USERID = 4
DELETE FROM CHANGE_TRACKING_USER WHERE USERID = 2
--检查最新的版本号
SELECT CHANGE_TRACKING_CURRENT_VERSION()
12.当程序收集了自数据版本后的数据.下面可以检测自版本起发生的所有更改
SELECT USERID, SYS_CHANGE_VERSION, SYS_CHANGE_OPERATION, SYS_CHANGE_COLUMNS
FROM CHANGETABLE(CHANGES CHANGE_TRACKING_USER, 1) AS T
SYS_CHANGE_COLUMNS列式包含从最新版本开始更新过的列的VARBINARY值,可以使用CHANGE_TRACKING_IS_COLUMN_IN_MASK函数来解释它.该函数接受个参数:表的列ID和VARBINARY值.
13.下面使用这个函数来检查NAME列和ADDRESS列是否被修改过.
SELECT USERID, --该函数返回对应的列ID
CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID('CHANGE_TRACKING_USER'),'NAME','COLUMNID'),
SYS_CHANGE_COLUMNS) NAME_IS_CHANGED,CHANGE_TRACKING_IS_COLUMN_IN_MASK(
COLUMNPROPERTY(OBJECT_ID('CHANGE_TRACKING_USER'),'ADDRESS','COLUMNID')
,SYS_CHANGE_COLUMNS) ADDRESS_IS_CHANGED
FROM CHANGETABLE(CHANGES CHANGE_TRACKING_USER,1) AS T
WHERE SYS_CHANGE_OPERATION = 'U' --确定修改的列
14.CHANGETABLE 通过VERSION 参数来返回最新的版本.
SELECT A.USERID, NAME, ADDRESS, SYS_CHANGE_VERSION
FROM CHANGE_TRACKING_USER A
CROSS APPLY CHANGETABLE(VERSION CHANGE_TRACKING_USER, (USERID), (A.USERID)) T
15.下面再演示一个UPDATE来演示版本的不同.
UPDATE CHANGE_TRACKING_USER
SET ADDRESS = 'MIAMI HEAT'
WHERE USERID = 3
SELECT CHANGE_TRACKING_CURRENT_VERSION() --检查最新的版本号
SELECT A.USERID, NAME, ADDRESS, SYS_CHANGE_VERSION
FROM CHANGE_TRACKING_USER A
CROSS APPLY CHANGETABLE(VERSION CHANGE_TRACKING_USER, (USERID), (A.USERID)) T
可以看到USERID=3的版本号为5,这是因为版本号是一致递增的(第11步版本号已经到4),所以现在最新的版本号位.没有修改的行版本号不变.
16.最后测试如何通过DML操作提供更改跟踪应用程序上下文信息,可以确定是哪一应用程序对那些行进行了数据修改.它的作用是如果有多个应用程序对数据源进行数据同步,这将会是有用的信息.使用CHANGE_TRACKING_CONTEXT函数来查询,函数只有一个输入参数CONTEXT,它是VARBINARY数据类型.
首先要保存上下文信息的变量,然后在CHANGE_TRACKING_CONTEXT函数中使用变量,再向更改跟踪表中插入一条新行
DECLARE @CONTEXT VARBINARY(128) = CAST('DS_ALEX' AS VARBINARY(128));
WITH CHANGE_TRACKING_CONTEXT(@CONTEXT)
INSERT CHANGE_TRACKING_USER(NAME,ADDRESS)
VALUES('James', 'Heat')
--现在查询从版本发生的所有更改.
SELECT USERID, SYS_CHANGE_OPERATION, SYS_CHANGE_VERSION, CAST(SYS_CHANGE_CONTEXT AS VARCHAR(50)) AppContext
FROM CHANGETABLE(CHANGES CHANGE_TRACKING_USER, 5) AS T
以上主要涉及了建库建表,
ALTER DATABASE ....ENABLE CHANGE_TRACKING. 启用数据库更改跟踪
CHANGE_RETENTION 和AUTO_CLEANUP 指定更改跟踪保留期限和自动清除.
查询SYS.CHANGE_TRACKING_DATABASES目录视图检查数据库更改跟踪的状态.
ALTER TABLE ...ENABLE CHANGE_TRACKING
TRACK_COLUMNS_UPDATED 指定列级别更改也会被跟踪.
SYS.CHANGE_TRACKING_TABLES目录视图确认表的更改跟踪状态
一些检测更改跟踪数据的不同函数:
CHANGE_TRACKING_CURRENT_VERSION() 返回最后提交的事务版本号
CHANGE_TRACKING_MIN_VALID_VERSION() 返回更改跟踪表的最小可用版本号
CHANGETABLE:VERSION 返回最新的更改版本
CHANGES 检测自指定同步版本以来的更改
CHANGE_TRACKING_IS_COLUMN_IN_MASK 检测更改跟踪表中那些列被更新
CHANGE_TRACKING_CONTEXT 通过DML操作存储更改上下文,从而可以跟踪哪一应用程序修改了什么数据.
