取代DTCPing的新工具-DTCTester
4 |
今天要設定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的設定",算是平時與工作伙伴溝通的習慣用語,因工作環境的防火牆預設都是啟動狀態且會阻擋大部分的進入連線,故常用"你要開(放)防火牆才會通"的說法。不過文中的用法的確可能造成混淆,謝謝你的提醒,已修正。
# by Jack
你好,請問....你的防火牆是如何修改才能讓MSDTC的服務正常通過呢??
# by Jeffrey
to Jack, 若是 Windows 防火牆,允許 MSDTC.exe 接受連線即可。若是額外的防火牆設備比較麻煩,可參考這篇:https://learn.microsoft.com/zh-tw/troubleshoot/windows-server/application-management/configure-dtc-to-work-through-firewalls?WT.mc_id=DOP-MVP-37580