IT工程師數位筆記本

SQL Server自定義業務功能的數據同步

在不同業務需求的驅動下，數據庫的模塊化拆分將會面臨一些比較特殊的業務邏輯處理需求。例如，在數據庫層面的數據同步需求。同步過程中，可能會有一些比較復雜的業務邏輯判斷。簡單介紹幾個SQL Server提供的數據同步功能。

1. 已鏈接服務（Linked Server）

通過鏈接數據庫可以實現不同實例間數據的訪問和更新操作。通常會與OPENQUERY行集函數一起使用，以避免分布式事務的干涉。不建議直接使用已鏈接服務來做遠程數據的更新操作，因為這需要使用到分布式數據庫的事務管理。SQL Server的分布式事務需要通過Windows的DTC（Distributed Transaction Controller，分布式事務控制器）來管理和協調不同服務器，或者說不同數據庫實例間的資源和事務調整，其性能與普通的事務管理成幾何倍的增長。

如圖10-4，頁面是已鏈接服務的配置界面，可以通過SSMS的Server Objects中的Linked Servers可視化頁面來進行配置。

圖11-4 數據庫的已鏈接服務

Provider是已鏈接服務器連接數據庫時使用的適配器的類型，如圖11-4中，左邊部分顯示的，列出了幾趾已有的適配器的類型。示例中，使用SQLOLEDB類型的適配器進行數據庫連接。

Security頁用來配置鏈接服務器的驗證信息，它包括如圖11-5中所示的4種模式的驗證方式。

Not be made

當選擇此認證模式時，已鏈接服務將使用本地服務的登陸用戶與遠程服務登陸用戶的映射配置列表中的賬號。如圖中，當選擇此模式時，本地將只能使用sa登陸時，模擬遠程服務的sa賬號的權限配置。

Without using a Security context

選擇此模式將不使用驗證模式，它只利用SQL Server服務的登陸賬號進行驗證，此服務要求在Windows服務啟動時，本地服務和遠程服務使用相同的登陸賬號。

Using current security context

當使用此模式時，要求本地和遠程服務都要有相同的賬號及密碼，通常這些配置為Windows集成認證的時候使用。

Using this security context

使用此模式時，將使用下面配置的用戶和密碼進行登陸遠程服務。

本小節中只是簡略地介紹了關于這些配置的簡要步驟，要獲取更詳細的內容，請參考SQL Server聯機叢書（http://technet.microsoft.com/zh-cn/library/ff772782.aspx）。

圖11-5 鏈接服務器安全配置

1. 更改跟蹤（Change Tracking）

更改跟蹤是SQL Server 2008加入的一個輕量級的數據修改記錄功能，它是數據變更捕獲功能的縮減版本。它可以將已修改數據的主鍵記錄在對應的視圖中，而后通過系統函數訪問該視圖，獲得相應的變更數據。通過變更數據的記錄，可以實現增量地處理復雜的業務邏輯，而后將數據結果保存到目標數據庫中。

示例代碼如代碼清單11-1中所示，開啟更改跟蹤是依據表來配置的，但是在配置表的更改跟蹤之前，需要將數據庫的更改跟蹤選項開啟，開啟數據庫更改跟蹤選項時，默認會將更改跟蹤記錄保留2天，并開啟自動清理的選項。

更改跟蹤可以跟蹤到具體的字段更改配置，如代碼清單11-1中，TRACK_COLUMNS_UPDATED配置所示，當選項為ON狀態時，將會記錄下更改跟蹤修改的字段，當為OFF時，將不會記錄。

USE master

GO

ALTER DATABASE [AdventureWorks2008R2] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS,AUTO_CLEANUP = ON);

GO

USE AdventureWorks2008R2

GO

ALTER TABLE Person.BusinessEntity ENABLE CHANGE_TRACKING WITH(TRACK_COLUMNS_UPDATED = ON);

GO

UPDATE TOP(10) Person.BusinessEntity

SET ModifiedDate=ModifiedDate;

GO

SELECT *

FROM CHANGETABLE(CHANGES Person.BusinessEntity,0) AS o;

GO

代碼清單11-1 設置更改跟蹤

圖11-6 更改跟蹤查詢結果

代碼清單11-1中的執行結果如圖11-6中所示，使用CHANGETABLE可以獲得對應表的更改歷史，更改歷史會將原有表的主鍵記錄下來，如圖11-6中BusinessEntityID字段所示。

關于更改跟蹤的詳細信息，可以參考一下SQL Server聯機叢書。

1. Service Broker

Service Broker是SQL Server自帶的消息隊列機制，通過Service Broker可以實現數據實例與實例間的通訊，同時也可以作為數據庫實例與應用程序的消息傳遞機制。

同時，Service Broker是隊列機制實現的，可以保證消息的執行順序，對于具有事務性要求的數據同步，Service Broker將是很理想的一個數據同步實現。

如代碼清單11-2中所示，配置了Service Broker在同實例下的同步配置。配置包括創建消息類型（Message Type），創建消息規則（Contract），隊列（Queue）以及服務（Server）。其層級結構如圖11-7中所示，首先，消息是存放在隊列中的，每個隊列都需要一個唯一的服務對應，服務將成為找到對應的標識。服務與服務間通訊時，需要指定相同的，相互可以識別到的消息規則，這些規則會指定對應的消息通訊的類型。其工作的流程，如圖11-8中所示。

圖11-7 Service Broker的組件組成

圖11-8 Service Broker的工作原理

當需要進行隊列傳輸前，需要開啟一個會話（Conversation），通過會話記錄下對應的服務標識，標識從源服務發送到目標服務。找到會話標識后便可以找到對應的隊列。

開啟會話以后，進行消息發送，對于數據庫實例來說，只需要將消息寫入到發送隊列就可以了。消息被寫入到發送隊列后，后續的工作都交給Service Broker來處理。

當消息進入發送隊列，Service Broker根據會話記錄的服務標識，找到目標服務，并將消息拆分為多個消息片斷，將消息發送到目標服務，服務接收完成所有的消息后，將消息寫入到目標隊列中。在寫入到目標隊列后，消息傳遞就結束了。后續的工作便交給應用處理了。

應用需要調用接收消息的命令，將消息從接收隊列中取出，并進行一系列的后續業務工作。

關于Service Broker可以參考SQL Server聯機叢書（http://technet.microsoft.com/zh-cn/library/ms166104(v=SQL.105).aspx）。

下面的代碼是一份在同一實例下進行Service Broker配置以及測試的腳本，可以參考一下代碼，并結合上圖11-8中的工作原理參考Service Broker的工作方式。

use master

go

alter database AdventureWorks2008R2 set enable_broker with rollback immediate;

go

use AdventureWorks2008R2

go

create message type ReceiveMsgType validation = none;

create message type SendMsgType validation = none;

go

create contract SampleContract(SendMsgType sent by initiator,ReceiveMsgType sent by target,FraudEndOfStream sent by initiator);

go

create queue SampleTargetQue;

create service SampleTargetSrv on queue SampleTargetQue(SampleContract);

go

create queue SampleInitQue;

create service SampleInitSrv on queue SampleInitQue(SampleContract);

go

/********************************Send Test********************************************

declare @handle uniqueidentifier,@msg varchar(8000) = 'this is a test message!';

begin dialog conversation @handle

from service SampleInitSrv

to service 'SampleTargetSrv'

on contract SampleContract

with encryption = off;

send on conversation @handle

message type SendMsgType(@msg)

**********************************************************************************/

/*****************************Receive Test*************************************************

declare @receivemsg varchar(8000),@Handle uniqueidentifier;

waitfor(receive top(1) @Handle = conversation_handle,

            @receivemsg = Message_Body

            from SampleTargetQue),timeout 1000

end conversation @handle;

select @receivemsg;

**********************************************************************************/

代碼清單11-2 同實例下的Service Broker配置