SQL SERVER 2008 更改跟踪,该怎么处理
SQL SERVER 2008 更改跟踪
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
GO
/*
通过查询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
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)
/*
对于要打开更改跟踪以及要跟踪哪些列被跟新了的表,需要打开表的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
(1 行受影响)
*/
/*
函数CHANGE_TRACKING_MIN_VALID_VERSION()可以获得表的最小可用版本号.如果断开连接的程序不同步的时
间超过了更改跟踪保留期限.那么就要对应用程序的数据进行彻底的刷新.
*/
SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('CHANGE_TRACKING_USER'))
/*结果
--------------------
0
(1 行受影响)
*/
/*
对于更改的侦测我们可以用函数CHANGETABLE.该函数有2种用法:使用CHANGES关键字来检测从指定的同步
版本以来发生的更改;或者使用VERSION关键字来返回最新的更改跟踪版本.
*/
SELECT USERID --返回的是主键
,SYS_CHANGE_OPERATION --I 代表INSERT,U代表UPDATE,D代表DELETE
,SYS_CHANGE_VERSION --返回的是版本号,因为这4条数据是在同一个INSERT中添加的
--,所以下面的结果版本号相同
--以下的函数返回的是自版本0以来的更改.第一个参数是表名称
FROM CHANGETABLE(CHANGES CHANGE_TRACKING_USER,0) A
/*结果
USERID SYS_CHANGE_OPERATION SYS_CHANGE_VERSION
----------- -------------------- --------------------
1 I 1