SQL Server Performance

MS Distributed Transaction Coordinator (MSDTC)

Discussion in 'General DBA Questions' started by jwright, Jun 22, 2005.

  1. jwright New Member

    Greetings,

    I'm trying to get MSDTC to work between two databases. Right now we're just testing between our QA and DEV servers. As near as I can tell, I can implement transactions from one server (DEV->QA) but not the other way (QA->DEV).

    One thing I've noticed across several different servers is the MSDTC tab under the "My Computer" properties in Component Management varies a lot. I'm not sure if this could be causing the issue or not, but how do I verify the verison of DTC I'm running, and upgrade as necessary?

    DEV server
    MS Windows Server 2003 Standard Edition
    Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: )

    Services.msc
    Distributed Transaction Coordinator
    NT AUTHORITYNetworkService

    Dcomcnfg.exe
    My Computer -> Properties -> MSDTC Tab -> Security Configuration
    Network DTC Access is checked
    Network Administration is checked
    Network Transactions is checked
    Network Clients is checked
    XA Transactions is checked
    Account is NT AUTHORITYNetworkService

    QA server
    MS Windows Server 2003 Standard Edition Service Pack 1
    Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

    Services.msc
    Distributed Transaction Coordinator
    NT AUTHORITYNetworkService

    Dcomcnfg.exe
    My Computer -> Properties -> MSDTC Tab -> Security Configuration
    Network DTC Access is checked
    Client and Administration
    Allow Remote Administration is checked
    Transaction Manager Communication
    Allow Inbound is checked
    Allow Outbound is checked
    No Authentication Required is selected
    XA Transactions is checked
    Account is NT AUTHORITYNetworkService

    When I run the DTCPing test I get the following:

    Please refer to following log file for details:
    C:dtcpingDBQA3212.log
    Invoking RPC method on dbdev
    RPC test is successful
    ++++++++++++RPC test completed+++++++++++++++
    Please start PING from dbdev to complete the test
    Please send following LOG to Microsoft for analysis:
    Partner LOG: DBDEV1732.log
    My LOG: DBQA3212.log
    ++++++++++++Start Reverse Bind Test+++++++++++++
    Received Bind call from DBDEV
    Trying Reverse Bind to DBDEV
    Reverse Binding success: DBQA-->DBDEV
    ++++++++++++Reverse Bind Test ENDED++++++++++
    ++++++++++++Start DTC Binding Test +++++++++++++
    Trying Bind to DBDEV
    Received reverse bind call from DBDEV
    Binding success: DBQA-->DBDEV
    ++++++++++++DTC Binding Test END+++++++++++++

    Based on what I see with DTCPing, it appears the communication between DBDEV and DBQA is successful.

    When I run the DTCTest I from DBQA to DBDEV get the following:

    DBQA test to DBDEV
    C:DTCTest>dtctester dbdev xxxxx xxxxxxx
    Executed: dtctester
    DSN: dbdev
    User Name: xxxxxxx
    Password: xxxxxxx
    tablename= #dtc23696
    Creating Temp Table for Testing: #dtc23696
    Warning: No Columns in Result Set From Executing: 'create table #dtc23696 (ivalint)'
    Initializing DTC
    Beginning DTC Transaction
    Enlisting Connection in Transaction

    Error:
    SQLSTATE=25S12,Native error=-2147168219,msg='[Microsoft][ODBC SQL Server Driver] Distributed transaction error'
    Error:
    SQLSTATE=24000,Native error=0,msg=[Microsoft][ODBC SQL Server Driver]Invalid 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.

    When I run the DTCTest from DBDEV to DBQA I get the following:

    DBDEV test to DBQA
    C:dtctest>dtctester dbqa xxxxx xxxxxxx
    Executed: dtctester
    DSN: dbqa
    User Name: xxxxx
    Password: xxxxxxx
    tablename= #dtc25564
    Creating Temp Table for Testing: #dtc25564
    Warning: No Columns in Result Set From Executing: 'create table #dtc25564 (ival
    int)'
    Initializing DTC
    Beginning DTC Transaction
    Enlisting Connection in Transaction
    Executing SQL Statement in DTC Transaction
    Inserting into Temp...insert into #dtc25564 values (1)
    Warning: No Columns in Result Set From Executing: 'insert into #dtc25564 values
    (1) '
    Verifying Insert into Temp...select * from #dtc25564 (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

    I've scanned the forums here and there were several useful threads (locked) which allowed me to get as far as I have. The problem is - although I'm still searching - I seem to be reaching a stage of diminishing returns. I'm hoping that a fresher pair of eyes than mine will be able to come up with a suggestion or too.

    Regards,
    John Wright
  2. Argyle New Member

    1.
    MSDTC has to be running on both the SQL Server and the client machine/server (web/app server, workstation etc.) to be able to work.

    2.
    If the MSDTC service is running on both machines but still doesn't work then it's most likely due to a name resolution issue or a firewall. MSDTC needs to be able to do name lookups (in both directions) on the servers involved to be able to function.

    To check this try and ping the SQL server by name from the web/app server and vice versa ping the web/app server by name from the SQL server. IF it's a cluster be sure to ping the virtual SQL Server name.

    Use the DTCping.exe
    http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q306843

    Or an even better tool might be:
    HOWTO: Use DTCTester Tool
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q293799

    You will notice when using these tools that even if the client connects to the MSDTC service trough the virtual SQL Server name it will sometimes still resolve the physical node name of the node that currently owns the MSDTC service in the cluster. So make sure that name reolution works for the physical node names as well and not only the virtual names.

    3.
    If DNS or WINS are miss-configured or traffic is blocked in routers or something then add name and IP address entries in "lmhosts" and "hosts" files on the involved SQL server and web/app server or fix the DNS or WINS.

    4.
    I run into a MSDTC issue with multiple IPs. Make sure than name resoultion works and resolves to the IP you intend to use for MSDTC. If you for example have a frontend and management IP make sure the name resolves to the frontend ip. This goes for both when the servers (source and destination) ping or resolve eachothers name as well as locally on each server when it resolves it's own name.

    You might have to change the binding order of your networks to get around this.

    Also if you have muliple names on the server, for example in a cluster, all names need to be resolvable (node names, cluster names etc) from all parties involved in MSDTC.

    5.
    If there is a firewall between the servers look at this article on what ports to open:

    INFO: Configuring Microsoft Distributed Transaction Coordinator (DTC) to Work Through a Firewall (Q250367)
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;q250367

    6.
    For enlist errors:

    INFO: Error "-2147168246 (8004d00a)" Failed to Enlist on Calling Object's Transaction
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;191168

    7.
    If the server is installed with a ghost image or other tools like it you can end up with computers having the same ID on the network used by MSDTC.

    8.
    If nothing else work remove and reinstall MSDTC.

    /Argyle



  3. jwright New Member

    Argyle,

    Your reply looks familiar - I think I used this to help check off my status before posting.

    1. MSDTC is running on both DBDEV and DBQA.
    2. DTCPing works between DBDEV and DBQA.
    DTCTest failes between DBQA->DBDEV, but not between DBDEV->DBQA. I have verified the following:
    2a. Windows Firewall is turned off on DBQA. I added the ports and msdtc.exe just to be safe anyway. DBDEV doesn't have it installed.
    2b. DNS / WINS seems to be setup correctly.
    2c. There is only a single NIC on DBDEV. There are two NIC cards on DBQA, but the second has been disabled.
    2d. Unable to tell if I have a misconfigured "SQL Server machine".
    3. DNS / WINS seems to be setup correctly.
    4. No multiple IPs. Both servers are able to ping and resolve their respective names correctly.
    5. No active firewall between servers.
    6. No enlist error.
    7. Not installed with a host image or other tool.

    Regarding 2d - I'm not sure what to look for exactly. Otherwise I believe I've tackled the other items. If you have further clarification / suggestions I would greatly appreciate it.

    Regards,
    John Wright

  4. Argyle New Member

    Yea I have that in a text file. It should be ghost image and not host image, changed my post <img src='/community/emoticons/emotion-4.gif' alt=':p' /><br /><br />On windows 2003 you also need to perform the following:<br /<a target="_blank" href=http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=3451>http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=3451</a><br /><br />If nothing helps one can try and reinstall MSDTC<br /><br />The difference you have under porprties is most likely due to service pack 1 for win 2003 on one of the servers. Might be some new settings regarding SP1 and MSDTC as well.
  5. jwright New Member

    Argyle,

    Outstanding! That link you just provided did the trick. When I checked DBQA it already had the TurnOffRpcSecurity DWORD in the registry, but DBDEV did not. I added the DWORD to DBDEV and re-ran DTCTester between the two machines and both were able to complete successfully!

    I appreciate your help, and I hope this thread proves useful to other members.

    Regards,
    John Wright

Share This Page