取代DTCPing的新工具-DTCTester
今天要設定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。
【延伸閱讀】