代码改变世界

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

2013-12-20 10:43  听风吹雨  阅读(27994)  评论(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复制入门(二)----复制的几种模式