[SQL][問題處理]在資料表的 Trigger 內如何異動遠端資料庫內的資料

在資料表上使用  Trigger ,來配合不同的 DML 做相關的資料異動是很常見的,但如果要掛 Server 來處理的時候,該如何設定 MSDTC 呢 ?

前一陣子有個同事詢問一個問題,他在同一台電腦上使用 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 來做解決了。好在資料異動量也不大,因此還算一個可以選擇的方式。