Step6：SQL Server 資料變更時間戳記（timestamp）在複製中的運用

標籤：

一.本文所涉及的內容（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的所有處理；

（Figure1：變更資料擷取）

 

方案二：

　　使用變更追蹤（Chang Tracking）這個功能，變更追蹤會包括跟蹤表的唯一值，還有欄位SYS_CHANGE_OPERATION，枚舉值（I=Insert、U=Update、D=Delete），還有DML操作的版本號碼：SYS_CHANGE_VERSION，它是每進行一次DML，都會遞增一個版本號碼，所以你可以針對I=Insert、U=Update、D=Delete不同的類型加上版本號碼過濾，就可以找到那些資料進行了更新；

（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 複製：事務發布，在訂閱伺服器查看錶的資訊，如所示：

（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]GOCREATE 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

（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) 列。

Step6：SQL Server 資料變更時間戳記（timestamp）在複製中的運用