五餅二魚工作室

前一陣子有個同事詢問一個問題，他在同一台電腦上使用 Trigger 來做處理，當特定資料表發生 DML 的時候，就將部分資料異動到其他資料表上， 這都沒有任何問題，但最近因為原本的資料庫要做升級，使得原本可以運作正常的  Trigger，再搭配 Link Server 之後，就無法正常運作了。當下一時之間只覺得應該是 MSDTC 沒有設定好，但沒有想到問題發生的原因，也就這樣放了兩三天。而剛好利用元旦假期，把相關環境給弄起來，模擬看看到底是哪個步驟疏忽了，才導致會有異常的狀況發生。

模擬的環境來源電腦是 Windows 2008 + SQL Server 2005 ，而目的端的電腦是 Windows 2012 + SQL Server 2012 的環境。在 SQL2005 的環境上建立一個 Link Server 連接到 SQL2012 上

我們在測試資料表 T1 下建立一個測試用的 TRIGGER

-- =============================================
-- Author:		James Fu
-- Create date: 2017/1/1
-- Description:	MSDTC Sample
-- =============================================
CREATE TRIGGER [dbo].[TRIG_INSERT_T1] 
   ON  [dbo].[T1] 
   AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here
	INSERT INTO SQL2012.Target.dbo.Log(Action,Action_Time,Old_F1,Old_F2)
		SELECT 'INSERT',GETDATE(),F1, F2 FROM inserted ;
END

GO

 

建立好環境之後，我們測試一下看是否可以正常在 SQL2005 上做 INSERT，然後在 SQL2012 上加入一筆紀錄。但實際執行後發現，會有錯誤發生，而從錯誤訊息中看出來，應該是我們沒有設定正確的 MSDTC 的參數，才導致有這樣的錯誤發生。

---

因為要設定MSDTC，這個部分我們可以 DOS 視窗使用 DCOMCNFG 來開啟元件服務

開啟後，選擇「元件服務」→「電腦」→「我的電腦」→「Distributed Transaction Coordinator」→「本機 DTS」，按下滑鼠右鍵選擇「內容」

就可以開啟  MSDTC 的設定，接著選擇「安全性」的頁籤，先把「網路 DTC 存取」給勾選之後，接下來再選擇 "用戶端和系統管理下" 的「允許遠端用戶端」。因為我們要從 SQL2005 這台寫入到 SQL2012 那台，因此在這台 SQL2005 上，我們在 "交易管理員通訊" 下選擇「允許輸出」和「不需要驗證」。

而接下來我們要到目的端的電腦上，也是重複同樣的設定

但在 SQL2012 這台上有一點不同，因為這台是目的端的電腦，因此在下圖 2 的位置，要改選擇「允許輸入」

除了 MSDTC 的設定之外，因為是跨兩台不同的電腦，因此要注意一下 Windows 內建的防火牆設定，要記得把「分算是交易協調器」設定為允許，這樣兩台電腦的  MSDTC 才可以正常連結。

而完成上述的設定，要記得把相關服務都要重啟，但如果嫌麻煩的話，就直接把電腦給重新啟動就可以了，接下來當服務都順利啟動之後，我們就可以再做測試了，而原本因為有跨 Server 而導致失敗的命令，當完成設定之後，也都可以正常的運作了。

在目的端電腦中，也可以看到透過 Trigger 所傳送過來的資料了。

---

使用 Trigger 實在不是一個最好的方式，但因為原本資料異動的作業已經無法修改，加上兩台機器又是不同版本的資料庫和作業系統，加上又有即時性的必要，因此只能使用 Trigger 來做解決了。好在資料異動量也不大，因此還算一個可以選擇的方式。