SQL Server 2008 引入了更改跟踪，这是一种轻量型解决方案，它为应用程序提供了一种有效的更改跟踪机制。通常，若要使应用程序能够查询对数据库中的数据所做的更改和访问与这些更改相关的信息，应用程序开发人员必须实现自定义更改跟踪机制。创建这些机制通常涉及多项工作，并且常常涉及使用触发器、 timestamp 列和新表组合来存储跟踪信息，同时还会涉及使用自定义清除过程。
通过更改跟踪，可以很容易地编写同步数据的应用，下面是一个使用更改跟踪实现单向数据同步的示例。
1 ． 建立示例环境
-- ====================================================
-- 测试的数据库
USE master ;
GO
CREATE DATABASE DB_test ;
GO
-- 启用更改跟踪
ALTER DATABASE DB_test SET
CHANGE_TRACKING = ON (
AUTO_CLEANUP = ON , -- 打开自动清理选项
CHANGE_RETENTION = 1 HOURS -- 数据保存期为 1 时
);
ALTER DATABASE DB_test SET
ALLOW_SNAPSHOT_ISOLATION ON ; -- 允许在测试数据库中使用 SNAPSHOT 事务隔离级别
GO
-- ====================================================
-- 测试的表
USE DB_test ;
GO
-- a. 同步的源表
CREATE TABLE dbo . tb_source (
pk_id int IDENTITY
PRIMARY KEY ,
col1 int ,
col2 varchar ( 10 ),
col3 nvarchar ( max ),
col4 xml
);
GO
-- 启用更改跟踪
ALTER TABLE dbo . tb_source
ENABLE CHANGE_TRACKING
WITH (
TRACK_COLUMNS_UPDATED = ON -- 记录 UPDATE 的列信息
);
GO
-- b. 同步的目录表
CREATE TABLE dbo . tb_Target (
pk_id int
PRIMARY KEY ,
col1 int ,
col2 varchar ( 10 ),
col3 nvarchar ( max ),
col4 xml
);
GO
-- 记录同步情况的表
CREATE TABLE dbo . tb_Change_Tracking (
id int IDENTITY
PRIMARY KEY ,
object_name sysname
UNIQUE ,
last_sync_version bigint ,
last_update_date datetime
);
GO
2 ． 实现同步处理的存储过程
-- ====================================================
-- 数据同步处理的存储过程
USE DB_test ;
GO
-- 数据同步的存储过程 - 同步未更新的数据
-- 单次更新，更新完成后退出
CREATE PROC dbo . p_SyncChangeData_tb_Srouce_Target
@last_sync_version bigint = NULL OUTPUT ,
@min_valid_version bigint = NULL OUTPUT
AS
SET NOCOUNT ON ;
-- ========================================
-- TRY...CATCH 中的标准事务处理模块
-- a. 当前的事务数
DECLARE
@__trancount int ;
SELECT
@__trancount = @@TRANCOUNT ;
-- TRY...CATCH 处理
BEGIN TRY
-- ========================================
-- 源表信息
DECLARE
@object_name sysname ,
@object_id int ;
SELECT
@object_name = N'dbo.tb_source' ,
@object_id = OBJECT_ID ( @object_name );
-- ========================================
-- 最后一次同步的版本
IF @last_sync_version IS NULL
BEGIN
SELECT
@last_sync_version = last_sync_version
FROM dbo . tb_Change_Tracking
WHERE object_name = @object_name ;
IF @@ROWCOUNT = 0
BEGIN
SET @last_sync_version = CHANGE_TRACKING_MIN_VALID_VERSION ( @object_id );
INSERT dbo . tb_Change_Tracking (
object_name , last_sync_version )
VALUES (
@object_name , @last_sync_version );
END ;
END ;
-- ========================================
-- TRY...CATCH 中的标准事务处理模块
-- b. 开启事务 , 或者设置事务保存点
SET TRANSACTION ISOLATION LEVEL SNAPSHOT ; -- 使用快照隔离级别的事务
IF @__trancount = 0
BEGIN TRAN ;
ELSE
SAVE TRAN __TRAN_SavePoint ;
-- ========================================
-- 版本验证
-- a. 验证是否有数据变更 ( 如果上次同步的版本号 = 当前数据库的最大版本号，则视为无数据变化 )
IF @last_sync_version = CHANGE_TRACKING_CURRENT_VERSION ()
GOTO lb_Return ;
-- b. 验证同步的版本号是否有效 ( 如果上次同步的版本号 < 当前可用的最小版本号，则视为无效 )
IF @last_sync_version < CHANGE_TRACKING_MIN_VALID_VERSION ( @object_id )
BEGIN
SET @min_valid_version = CHANGE_TRACKING_MIN_VALID_VERSION ( @object_id );
GOTO lb_Return ;
END ;
-- c. 验证同步的版本号是否有效 ( 如果上次同步的版本号 > 当前数据库的最大版本号，则视为无效 )
IF @last_sync_version > CHANGE_TRACKING_CURRENT_VERSION ()
BEGIN
SET @last_sync_version = NULL;
GOTO lb_Return ;
END ;
-- ========================================
-- 同步数据
-- a. 插入
WITH
CHG AS (
SELECT
DATA .*
FROM dbo . tb_source DATA
INNER JOIN CHANGETABLE ( CHANGES dbo . tb_source , @last_sync_version ) CHG
ON CHG . pk_id = DATA . pk_id
WHERE CHG . SYS_CHANGE_OPERATION = N'I'
)
INSERT dbo . tb_Target
SELECT * FROM CHG ;
-- b. 删除
WITH
CHG AS (
SELECT
CHG .*
FROM CHANGETABLE ( CHANGES dbo . tb_source , @last_sync_version ) CHG
WHERE CHG . SYS_CHANGE_OPERATION = N'D'
)
DELETE DATA
FROM dbo . tb_Target DATA
INNER JOIN CHG
ON CHG . pk_id = DATA . pk_id ;
-- c. 更新
WITH
COL AS (
lang
发表评论
评论