代码改变世界

SQL Server 数据变更时间戳(timestamp)在复制中的运用

2013-12-20 10:43  听风吹雨  阅读(25744)  评论(7编辑  收藏

一.本文所涉及的内容(Contents)

  1. 本文所涉及的内容(Contents)
  2. 背景(Contexts)
  3. 方案(Solution)
    1. 方案一(Solution One)
    2. 方案二(Solution Two)
    3. 方案三(Solution Three)
  4. 实现过程(Process)
  5. 注意事项(Attention)
  6. 参考文献(References)

二.背景(Contexts)

  SQL Server数据库中Basic与Group两个表需要提供部分字段给其它程序读取,程序把这两个表的数据缓存到内存中,但是程序想知道这两个表数据的变更信息,包括:Insert/Update/Delete,有什么方式可以实现呢?

三.方案(Solution)

  上面的场景,使用SQL Server复制(Replication)是无可厚非的,但是如何及时获取变更信息呢?

方案一:

  使用变更数据捕获CDC这个功能,在数据库订阅库使用CDC,再创建一个存储过程;通过向存储过程传入最后一次记录(程序自己保存)的日志序列号(LSN),返回表变更的数据列表,程序先从内存中删掉这些ID值,再把变更数据插回内存,这个逻辑可以简化对Insert/Update/Delete的所有处理;

wps_clip_image-19686

(Figure1:变更数据捕获)

 

方案二:

  使用更改跟踪(Chang Tracking)这个功能,更改跟踪会包括跟踪表的唯一值,还有字段SYS_CHANGE_OPERATION,枚举值(I=Insert、U=Update、D=Delete),还有DML操作的版本号:SYS_CHANGE_VERSION,它是每进行一次DML,都会递增一个版本号,所以你可以针对I=Insert、U=Update、D=Delete不同的类型加上版本号过滤,就可以找到那些数据进行了更新;

wps_clip_image-116

(Figure2:更改跟踪)

 

方案三:

  使用timestamp,在订阅的两个表中加入这个字段,timestamp记录的是数据变更的时间,在程序中读取大于这个timestamp的数据进行操作(操作如想法一所示);但是有个缺点,这种方式没有办法记录到删除的记录,除非表中有个字段是用来标识是否删除的,发布库是不存在Delete操作的,只能有Insert和Update。

需要同步的字段如下:

Basic表:ID,Name,Category,overseas,GroupID,Delete;

Group表:ID,NAME,Delete;

CDC的基本使用可以参考:SQL Server 变更数据捕获(CDC)监控表数据,更改跟踪可以参考:SQL Server 更改跟踪(Chang Tracking)监控表数据,下面我讲讲想法三的具体实现;

四.实现过程(Process)

(一) 环境信息

系统环境:Windows Server 2008 + SQL Server 2008 R2

发布服务器:192.168.1.152,服务器名称:USER-H2B2

订阅服务器:192.168.1.151,服务器名称:USER-FJMO

发布数据库:Task

订阅数据库:TaskSiteInfo

 

(二) 实现概述

  首先是通过Task发布、TaskSiteInfo进行订阅数据,在这两个表中是有一个Delete的字段,用来标识数据是否给删除的,另外需要在TaskSiteInfo数据库的两个表都加入timestamp字段,加入这个字段的目的是由程序记录查询的最大的timestamp,通过这个timestamp返回大于某个时间的数据。

 

(三) 搭建步骤

A. 搭建复制的过程请参考文档:SQL Server 复制:事务发布,在订阅服务器查看表的信息,如下图所示:

wps_clip_image-3331

(Figure3:表数据)

 

B. 接下来我们修改Basic和Group表结构,为每个表添加一个timestamp类型的字段;

--修改表结构
ALTER TABLE  [dbo].[Basic] ADD
    timestamp timestamp NOT NULL

--修改表结构
ALTER TABLE  [dbo].[Group] ADD
    timestamp timestamp NOT NULL

 

C. 为这个timestamp类型的字段分别创建索引;

--创建索引
CREATE NONCLUSTERED INDEX IX_Basic_TimeStamp ON [dbo].[Basic]
(
    timestamp
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX IX_Group_TimeStamp ON [dbo].[Group]
(
    timestamp
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

 

D. 通过timestamp字段查询变更数据,假如上次保存的时间戳的值是:0x0000000000163E30,那么我们通过下面的SQL脚本就能获取到这个时间戳之后变更的记录,获取到这里数据就可以更新内存数据了;

--返回某时间戳之后的数据
SELECT * FROM [dbo].[Basic] 
WHERE timestamp > 0x0000000000163E30

wps_clip_image-2204

(Figure4:某时间戳之后变更的记录)

五.注意事项(Attention)

1. 每个数据库都有一个计数器,当对数据库中包含 timestamp 列的表执行插入或更新操作时,该计数器值就会增加。 该计数器是数据库时间戳;

2. 一个表只能有一个 timestamp 列;

3. 注意删除数据操作是没有办法记录时间戳的,所以你删除记录的逻辑应该是用一个字段标识这行记录已经被删除;

4. 这一属性使 timestamp 列不适合作为键使用,尤其是不能作为主键使用;

5. 如果该列属于索引键,则对数据行的所有更新还将导致索引更新;

6. 若要返回数据库的当前时间戳值:SELECT @@DBTS

7. 在 DDL 语句,请尽量使用 rowversion 而不是 timestamp,在SSMS设计表的时候是没有rowversion数据类型的;

8. 在 CREATE TABLE 或 ALTER TABLE 语句中,不必为 timestamp 数据类型指定列名,如果不指定列名,则 Microsoft SQL Server 数据库引擎将生成 timestamp 列名;但 rowversion 同义词不具有这样的行为。 在使用 rowversion 时,必须指定列名。

9. 不可为空的 rowversion 列在语义上等同于 binary(8) 列。 可为空的 rowversion 列在语义上等同于 varbinary(8) 列。

六.参考文献(References)

timestamp (Transact-SQL)

rowversion (Transact-SQL)

SQL Server 复制 订阅与发布

SQL Server复制入门(一)----复制简介

SQL Server复制入门(二)----复制的几种模式