今天要設定Windows 2008防火牆使用SQL分散式交易,查資料時發現了檢測MSDTC連線的新工具--DTCTester

簡單來說,它是用以取代DTCPing的改良版。主要優點是只需在Client執行,不像DTCPing得在Client,Server都跑互連,而且由於它會真的連線SQL Server進行一些讀寫測試,相較於DTCPing只檢查網路傳輸層,更能正確反應SQL連線的真實狀態。換句話說,過去DTCPing成功但SQL分散交易不通的"偽陽性"失真情況可望改善。

使用時要先在ODBC設定DSN(注意: x64 OS要記得設定32bit的ODBC),然後傳入DSN名稱、User Id、Password作為參數(密碼會明碼顯示是一大缺點)執行dtctester即可。

測試結果還算易讀,失敗時的訊息範例如下(故意不開通防火牆):

X:\Tools\DTCTester>dtctester labdb user pwd
Executed: dtctester
DSN:  labdb
User Name: user
Password: pwd
tablename= #dtc27984
Creating Temp Table for Testing: #dtc27984
Warning: No Columns in Result Set From Executing: 'create table #dtc27984 (ival int)'
Initializing DTC
Beginning DTC Transaction
Enlisting Connection in Transaction
Error:
SQLSTATE=25S12,Native error=0,msg='[Microsoft][SQL Server Native Client 10.0]The
 transaction has already been implicitly or explicitly committed or aborted
'
Error:
SQLSTATE=24000,Native error=0,msg=[Microsoft][SQL Server Native Client 10.0]Inva
lid cursor state
Typical Errors in DTC Output When
a.  Firewall Has Ports Closed
-OR-
b.  Bad WINS/DNS entries
-OR-
c.  Misconfigured network
-OR-
d.  Misconfigured SQL Server machine that has multiple netcards.
Aborting DTC Transaction
Releasing DTC Interface Pointers
Successfully Released pTransaction Pointer.

成功時的訊息範例:

X:\Tools\DTCTester>dtctester labdb user pwd
Executed: dtctester
DSN:  labdb
User Name: user
Password: pwd
tablename= #dtc30032
Creating Temp Table for Testing: #dtc30032
Warning: No Columns in Result Set From Executing: 'create table #dtc30032 (ival
int)'
Initializing DTC
Beginning DTC Transaction
Enlisting Connection in Transaction
Executing SQL Statement in DTC Transaction
Inserting into Temp...insert into #dtc30032 values (1)
Warning: No Columns in Result Set From Executing: 'insert into #dtc30032 values
(1) '
Verifying Insert into Temp...select * from #dtc30032 (should be 1): 1
Press enter to commit transaction.

Commiting DTC Transaction
Releasing DTC Interface Pointers
Successfully Released pTransaction Pointer.
Disconnecting from Database and Cleaning up Handles

建議可用它取代DTCPing。

【延伸閱讀】


Comments

# by JimmyChen

請問 您內文寫的 "測試結果還算易讀,失敗時的訊息範例如下(故意不開防火牆):" 應該是"故意開防火牆"吧?不太懂不開防火牆怎會無法使用msdtc

# by Jeffrey

to JimmyChen, 原意為"故意不開放防火牆相關Port的設定",算是平時與工作伙伴溝通的習慣用語,因工作環境的防火牆預設都是啟動狀態且會阻擋大部分的進入連線,故常用"你要開(放)防火牆才會通"的說法。不過文中的用法的確可能造成混淆,謝謝你的提醒,已修正。

Post a comment