[SQL]AlwaysON 環境下使用跨資料庫交易異常

搭配  AlwaysON lwaysON 與 MSDTC 的相關設定

這幾天剛好有朋友出了一個題目,他原本的環境因為升級到 SQL Server 2016 AlwaysON 的環境上,因為怕有異常因此需要把部分資料用 Trigger gger 寫回到舊的環境上,但每次一異動資料,整個 AlwaysON 的環境就出狀況了,因此希望幫忙處理一下。

其實問題說難似乎也沒有那麼困難,找一下微軟官方的文件,就可以看到明確的說明了

就是安裝 Service Pack ,如果安裝好 Service Pack,基本上就可以讓 AlwaysON 的環境來支援了。為了測試,我就直接從 Azure 上弄個環境來測試一下了。

基本上要設定 MSDTC 和防火牆,當要設定之前,先確定您的 DTC 服務是有在啟動的狀態

我們使用「元件服務」來進行 MSDTC 的設定,在「本機 DTC」 的節點按下滑鼠右鍵,選擇「內容

接著選擇「安全性」,設定「網路DTC存取」,勾選交易管理員通訊「允許輸入」和「允許輸出

這樣透過上述的步驟就完成 DTC 的設定,接著我們要來開啟防火牆設定

選擇「分散式交易協調器」,將後面的網路區域打勾,如果您不知道您是用哪個,就先全勾吧。

接下來就要進入到 SQL Server 的 AG 上,在可用性群組的屬性中,要記得勾選「每個資料庫 DTC 支援」,這樣您的 AlwaysON 上的資料庫,才有辦法支援跨伺服器的交易。


上述的設定完成之後,我們就可以來做個測試了。

首先我先在 AlwaysON 的 Primary 和 Secondary 的 Server 上,建立 Link Server。其實這個建立算蠻簡單的,在 1 的位置打上要連接的 SQL Server 主機別名, 2 的部分則是輸入真正 SQL Server 的名稱, 3 則是要連接的 SQL Server 資料庫。

然後進入到安全性的設定,下方輸入要連接的帳號密碼,設定好之後進行存檔,就可以來進行測試了。

設定好之後,我們先透過 SSMS 的 UI 介面,來看看是否可以連接,看起來都很正常

接下來下 SQL 測試看看,這裡可以成功,因此看起來 Link Server 的相關設定沒有問題,是可以運作的。

接著我們模擬原來的問題,在 Trigger 內做跨伺服器的資料異動,來確定 MSDTC 相關設定是正常的,下面是我寫的一個測試用範例 Trigger,當有觸發的時候就到另外一個資料表上去加入一筆紀錄

接著我們就來測試一下,當我們採用下面的語法去處理的時候,可以看到真的會有兩筆記錄存到我們的 Log 資料表,因為原本資料異動和 Trigger 內的資料異動都會在同一個交易內,而兩台位在不同的伺服器上,則需要透過 MSDTC 來作兩階段的交易,因此當我們下面這樣的範例是正常的時候,則表示我們前面相關步驟設定都正確了。

一般來說,我們盡可能避免這樣的跨伺服器交易處理,因為會讓原本的 DML 處理的時間變長許多,因此使用的時候還是要稍加注意一下。


後來又弄了 SQL Server 2016 的環境測試一下,有幾個地方要注意一下

1. 沒有升級到 SP2 以上,AlwaysON 的 Support DTC 是沒有辦法設定的。

2. 更改 MSDTC 相關設定之後,要記得將環境重新啟動才有辦法生效

3. 最好都在同一個網域內,這樣比較不會有異常狀況。