[料理佳餚] SQL Server 用 Change Tracking(變更追蹤)土砲 Replication(複寫)

在 SQL Server 可商用的版本中,Express 及 Web 版本是唯二沒有完整 Replication 的版本,但是一個免費、一個便宜,對於老闆來講這是一個可以節省成本的點,如果真的有 Replication 的需求,除了升級之外,我們還可以寫點程式自己土砲。

透過 Replication 機制我們可以做到寫入一台資料庫,複寫到多台資料庫,在讀取這件事情上做到資料庫的水平擴充,以解決單一台資料庫的效能瓶頸,其中 Replication 機制扮演著至關重要的角色,但是在沒有支援 Replication 的 SQL Server 版本中該怎麼辦?

Change Tracking(變更追蹤)

還好 SQL Server 的每個版本都有支援 Change Tracking,Change Tracking 是從 SQL Server 2008 開始有的功能,預設是關閉的,我們可以視需要來啟用,它會將資料表中資料列的變更操作記錄下來,我們只要定期地去查詢變更記錄,就可以知道哪些資料列有被 INSERTUPDATEDELETE,然後根據變更記錄從來源資料表將資料撈出來同步到目標資料表,來達成 Replication 的目的。

啟用變更追縱

要啟用變更追縱,資料庫及資料表都需要做設定,首先是資料庫的部分,從資料庫屬性中,可以看到一個變更追縱的頁籤,把它設為 True 就啟用了,其中保留週期保留週期單位自動清除這三個設定是一組的,目的是自動刪除過期的變更記錄,避免佔用資料庫空間。

再來是資料表,在資料表屬性中也有變更追縱的頁籤,一樣把它設為 True 就啟用了,其中追縱資料行已更新的設定可以讓我們只處理異動的欄位,當一個資料表大部分的欄位都不常異動的話,我們就不必經常地去同步它們,只需要同步有異動的欄位就好,以減少處理的資料量。

查詢變更記錄

我們透過 CHANGETABLE() 函式,輸入 CHANGES [資料表名稱]last_sync_version 參數後,就可以取得資料表的變更記錄了。

從取得的記錄中我們可以看到幾個陌生的欄位,底下就來做個說明:

  • SYS_CHANGE_VERSION:資料列最新的變更版本號
  • SYS_CHANGE_CREATION_VERSION:資料列被新增時的變更版本號
  • SYS_CHANGE_OPERATION:這個欄位有三個值,分別是 I = INSERT、U = UPDATE、D = DELETE。
  • SYS_CHANGE_COLUMNS:列出 last_sync_version 以來變更操作為 U 的資料行,但不包含計算資料行。
  • SYS_CHANGE_CONTEXT:一些與變更操作相關的上下文,但是必須在當初 INSERT、UPDATE、DELETE 的時候就寫入,否則此欄位會一直是 NULL。
  • 主索引鍵欄位:主索引鍵值

與來源資料表 JOIN

我們不管更新欄位,總是整筆資料同步的話,是最為單純的,只要將變更記錄與來源資料做 LEFT OUTER JOIN,我們就可以取得異動的資料,將其同步到目標資料表即可,還要順便將這次同步的最後版本號取回來存放,以便下一輪同步作業使用。

如果我們想要的是有異動的欄位才同步的話,就比較麻煩一點,我們必須將變更操作 U 跟 I、D 的變更記錄分開查詢,想想這挺合理的,因為 I 跟 D 都是對整筆資料操作,所以變更記錄中的 SYS_CHANGE_COLUMNS 欄位一直都會是 NULL,自然也就無法得知是哪些欄位被異動了,下面我們把查詢語法修改一下。

其中針對異動欄位的部分還挺麻煩的,變更記錄儲存的是 ColumnId,我們必須先透過 COLUMNPROPERTY() 函式找出個別欄位的 ColumnId 後,再透過 CHANGE_TRACKING_IS_COLUMN_IN_MASK() 函式進行比對確認該欄位有無異動,若該欄位有異動,才將它的值從來源資料表中撈出來,花的工還挺不少的。

撈出異動的資料之後,我們可以選擇用 MERGE INTO 將資料一次同步到目標資料表或是在查詢變更記錄的時候就將 I、U、D 分開,將資料依變更操作分組同步到目標資料表,最後,將同步資料的步驟用 SQL Server Agent Job 或是用 C# 撰寫一個應用程式定期地來執行,那麼我推薦後者,因為除了同步資料到另一個資料庫之外,我們可能還會想同步資料到其他地方(比如:Redis),後者會比較容易做得到。

參考資料