使用更改跟踪实现数据同步

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(