SQL SERVER 2008 更改跟踪

来源：互联网发布：广州中智软件编辑：程序博客网时间：2024/04/29 10:50

SQL SERVER 2008 更改跟踪

看书，看联机期间。。。顺带把看到的写下

与SQL SERVER 2008 CDC 异步捕获数据变更的不同,更改跟踪是同步进程,

是DML(INSERT/UPDATE/DELETE)事务的一部分,它可以使用最小的C盘存储

开销来侦测数据行的净变更.那么它也就不能像CDC那样可以提供用户表的

历史更改信息. 更改是使用异步进程捕获的,此进程读取事务日志,并

且对系统造成的影响很小.

更改跟踪捕获更改了表行这一事实,但不会捕获更改的数据.这样,应用程

序就可以确定使用从用户表中直接获取的最新行数据更改的行.因此,与变

更数据捕获相比,更改跟踪可以解答的历史问题比较有限.但是,对于不需

要历史信息的那些应用程序,更改跟踪产生的存储开销要小得多,因为它不

需要捕获更改的数据(不需要触发器和表时间戳).它使用同步跟踪机制来

跟踪更改.此功能旨在最大限度地减少DML 操作开销.

总的来说有以下几点:

1 减少了开发时间: 由于SQL Server 2008 中提供了更改跟踪功能,因此无需开发自定义解决方案.

2 不需要架构更改: 使用更改跟踪不需要执行以下任务：添加列;添加触发器;如果无法将列添加到用

户表,则需要创建要在其中跟踪已删除的行或存储更改跟踪信息的端表.

3 内置清除机制: 更改跟踪的清除操作在后台自动执行.不需要端表中存储的数据的自定义清除.

4 提供更改跟踪功能的目的是获取更改信息: 使用更改跟踪功能可使信息查询和使用更方便.列跟踪

记录提供与更改的数据相关的详细信息.

5 降低了DML 操作的开销: 同步更改跟踪始终会有一些开销.但是,使用更改跟踪有助于使开销最小

化.开销通常会低于使用其他解决方案,对于需要使用触发器的解决方案

,尤其如此.

6 更改跟踪是基于提交的事务进行的: 更改的顺序基于事务提交时间.在存在长时间运行和重叠事务的

情况下,这样可获得可靠的结果.必须专门设计使用timestamp

值的自定义解决方案,以处理这些情况.

7 配置和管理更改跟踪的标准工具: SQL Server 2008 提供标准的DDL 语句、SQL Server Management

Studio、目录视图和安全权限.

建立测试数据库

IF NOT EXISTS (SELECT NAME FROM SYS.databases WHERE name='CHANGE_TRACK_DB')

BEGIN

CREATE DATABASE CHANGE_TRACK_DB

END

要启用数据库更改跟踪功能,需要配置CHANGE_TRACKING数据库选项.也可以配置跟踪的数据在

数据库保留多久,以及是否启用自动清除.配置保留期将会影响到需要维护的跟踪数据的大小.

该值过高可能会影响存储.太低的话在远程应用程序同步不够的情况下,会引发通另一应用程序

的同步问题.

配置更改跟踪

ALTER DATABASE CHANGE_TRACK_DB

SET CHANGE_TRACKING= ON

(CHANGE_RETENTION=36 HOURS,

AUTO_CLEANUP=ON)

使用更改跟踪时的最佳实践是为数据库启用快照隔离.不使用快照隔离会引发事务不一致的

变更信息.对有显著DML活动的数据库和表,以一致的方式捕获更改跟踪的信息很重要(抓取最

新版本并使用该版本号来获取适当的数据)

由于行版本的生成,启用快照隔离会在tempdb中增加额外的使用空间.会带来I/O开销的增加.

启用快照隔离

ALTER DATABASE CHANGE_TRACK_DB

SET ALLOW_SNAPSHOT_ISOLATION ON

通过查询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

/*结果

DB_NAME IS_AUTO_CLEANUP_ON RETENTION_PERIOD RETENTION_PERIOD_UNITS_DESC

--------------- ------------------ ---------------- ----------------------------

CHANGE_TRACK_DB 1 36 HOURS

(1 行受影响)

创建测试表

USE CHANGE_TRACK_DB

CREATE TABLE CHANGE_TRACKING_USER

(USERID INT NOT NULL PRIMARY KEY IDENTITY(1,1)

,NAME VARCHAR(20) NOT NULL

,ADDRESS VARCHAR(100) NOT NULL)

对于要打开更改跟踪以及要跟踪哪些列被跟新了的表,需要打开表的CHANGE_TRACKING选项

和TRACK_COLUMNS_UPDATED选项.

ALTER TABLE CHANGE_TRACKING_USER

ENABLE CHANGE_TRACKING

WITH (TRACK_COLUMNS_UPDATED= ON)

查询sys.change_tracking_tables目录视图可以获得启用跟踪更改的详细信息.

SELECT OBJECT_NAME(OBJECT_ID) AS [TB_NAME]

,IS_TRACK_COLUMNS_UPDATED_ON

FROM sys.change_tracking_tables

/*结果

TB_NAME IS_TRACK_COLUMNS_UPDATED_ON

-------------------- ---------------------------

CHANGE_TRACKING_USER 1

(1 行受影响)

对表进行插入数据来捕获更改跟踪.

INSERT CHANGE_TRACKING_USER(NAME,ADDRESS) VALUES

('香蕉','dss省fdfd市'),

('鸽子','山东省青岛市'),

('水哥','江苏省苏州市'),

('土豆','XX省XX市');

用来查看正在同步的是一个函数CHANGE_TRACKING_CURRENT_VERSION(),返回的是最后提交的事务的版本号

.所有发生在启用更改跟踪表中的DML操作都会照成版本号的增长.版本号用来确定更改.

SELECT CHANGE_TRACKING_CURRENT_VERSION()

/*结果

--------------------

(1 行受影响)

函数CHANGE_TRACKING_MIN_VALID_VERSION()可以获得表的最小可用版本号.如果断开连接的程序不同步的时

间超过了更改跟踪保留期限.那么就要对应用程序的数据进行彻底的刷新.

SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('CHANGE_TRACKING_USER'))

/*结果

--------------------

(1 行受影响)

对于更改的侦测我们可以用函数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

/*结果

USERID SYS_CHANGE_OPERATION SYS_CHANGE_VERSION

----------- -------------------- --------------------

1 I 1

2 I 1

3 I 1

4 I 1

(4 行受影响)

当收集同步信息时,使用SET TRANSACTION ISOLATION LEVEL SNAPSHOT 和BEGIN TRAN..COMMIT TRAN

来封装收集的更改信息和相关的当前更改跟踪版本以及最小的可用版本.使用快照隔离允许更改跟踪

的数据具有事务一致性的形式.

UPDATE CHANGE_TRACKING_USER

SET NAME='柯少'

WHERE USERID=1

UPDATE CHANGE_TRACKING_USER

SET ADDRESS='火星'

WHERE USERID=4

DELETE FROM CHANGE_TRACKING_USER WHERE USERID=2

--检查最新的版本号

SELECT CHANGE_TRACKING_CURRENT_VERSION()

/*结果

--------------------

(1 行受影响)

当程序收集了自数据版本后的数据.下面可以检测自版本起发生的所有更改

SELECT USERID

,SYS_CHANGE_VERSION

,SYS_CHANGE_OPERATION

,SYS_CHANGE_COLUMNS

FROM CHANGETABLE(CHANGES CHANGE_TRACKING_USER,1) AS T

/*结果

USERID SYS_CHANGE_VERSION SYS_CHANGE_OPERATION SYS_CHANGE_COLUMNS

----------- -------------------- -------------------- -------------------

1 2 U 0x0000000002000000

2 4 D NULL

4 3 U 0x0000000003000000

(3 行受影响)

SYS_CHANGE_COLUMNS列式包含从最新版本开始更新过的列的VARBINARY值,可以使用

CHANGE_TRACKING_IS_COLUMN_IN_MASK函数来解释它.该函数接受个参数:表的列ID和VARBINARY

值.

下面使用这个函数来检查NAME列和ADDRESS列是否被修改过.

SELECT USERID

,CHANGE_TRACKING_IS_COLUMN_IN_MASK(

COLUMNPROPERTY(OBJECT_ID('CHANGE_TRACKING_USER'),'NAME','COLUMNID')--该函数返回对应的列ID

,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'--确定修改的列

/*结果

USERID NAME_IS_CHANGED ADDRESS_IS_CHANGED

----------- --------------- ------------------

1 1 0

4 0 1

(2 行受影响)

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

USERID NAME ADDRESS SYS_CHANGE_VERSION

----------- ------- --------------- --------------------

1 柯少 dss省fdfd市 2

3 水哥江苏省苏州市 1

4 土豆火星 3

(3 行受影响)

下面再演示一个UPDATE来演示版本的不同.

UPDATE CHANGE_TRACKING_USER

SET ADDRESS='冥王星'

WHERE USERID=3

SELECT CHANGE_TRACKING_CURRENT_VERSION() --检查最新的版本号

--------------------

(1 行受影响)

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 NAME ADDRESS SYS_CHANGE_VERSION

----------- -------------------- ------------- --------------------

1 柯少 dss省fdfd市 2

3 水哥冥王星 5

4 土豆火星 3

(3 行受影响)

可以看到USERID=3的版本号为,这是因为版本号是一致递增的,上面执行过如下代码:

UPDATE CHANGE_TRACKING_USER

SET NAME='柯少'

WHERE USERID=1

UPDATE CHANGE_TRACKING_USER

SET ADDRESS='火星'

WHERE USERID=4

DELETE FROM CHANGE_TRACKING_USER WHERE USERID=2

检查最新的版本号

SELECT CHANGE_TRACKING_CURRENT_VERSION()

/*结果

--------------------

(1 行受影响)

所以现在最新的版本号位.没有修改的行版本号不变.

最后测试如何通过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

('ALEX','水星')

--现在查询从版本发生的所有更改.

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

/* 结果

USERID SYS_CHANGE_OPERATION SYS_CHANGE_VERSION AppContext

----------- -------------------- -------------------- ------------

5 I 6 DS_ALEX

(1 行受影响)

到此结束!

以上主要涉及了建库建表,

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操作存储更改上下文,从而可以跟踪哪一应用程序

修改了什么数据.