SQL Server Performance

MSDTC - do I need to allow network access?

Discussion in 'SQL Server 2008 Clustering' started by sqlguy, Oct 26, 2011.

  1. sqlguy New Member

    Hi

    I have installed a SQL 2008 R2 cluster with a clustered MSDTC resource.

    SQL runs fine as it is.

    This article http://technet.microsoft.com/en-us/library/cc753510(WS.10).aspx mentions allowing network connections to MSDTC.

    If SQL ins't connecting or allowing other MSDTC enabled connections to it from around the network, do I need to alter the as-installed default settings of MSDTC?

    When I run the Best Practice Advisor ( BPA ) it throws up a warning :

    Category: Policy

    Source: localhost

    Issue: The Microsoft Distributed Transaction Coordinator (MSDTC) is currently configured improperly and can result in errors
    [ NetworkDtcAccess:0 , NetworkDtcAccessClients:0 , NetworkDtcAccessTransactions:0 , NetworkDtcAccessInbound:0 , NetworkDtcAccessOutbound:0 , XaTransactions:0 , AllowOnlySecureRpcCalls:1 , TurnOffRpcSecurity:0 ]

    Impact: The existing MSDTC settings and configuration can result in error conditions when performing distributed transactions that involve SQL Server

    Resolution: Change the settings for MSDTC


    When it says "heres more info " it sends me to look at http://support.microsoft.com/kb/2027550

    I'm happy to alter the settings, I'm just wondering if I really need to?

    Any thoughts please?

    Cheers,
  2. MichaelB Member

    "If you are developing COM+ or System.EnterpriseServices components and you are using the Transaction service then you are indirectly using MSDTC. The COM+ infrastructure is hiding all the details from you so you can focus on your business needs instead of implementation details. If you need to bypass COM+ and talk directly to MSDTC you can do it using the MSDTC proxy (msdtcprx.dll). For now, to directly access MSDTC from .Net apps you need to use COM Interop. For future apps, please refer to my previous post about .Net and Transactions."
    Taken from:http://blogs.msdn.com/b/florinlazar...msdtc-and-why-do-i-need-to-care-about-it.aspx
  3. Shehap MVP, MCTS, MCITP SQL Server

    It sounds like you go with the option of cluster configuration with unneeded resources and thus , you have reached to its end “Well configured but not used” since it looks like SQL + Relevant applications were up and running fine

    But if we talked about the option of cluster configuration with needed resources of DTC …what we the appropriate configuration terms and when to be used …?

    1. When to be used:

    When an application hosted on another server rather than DB server acquire DTC to control Commit + Roll back process of some scopes of transactions together to ensure data consistency for them together.

    2- How to be configured:

    · You have to add a DTC resource within cluster configurations and much preferred to has an own SAN LUN allocated for DTC only ( 1 GB is enough)

    · Then you have to right click SQL Service >>Server Properties>> connection >>then mark require distributed transactions for server to server communications >.
    · Or faster , you could run the below script:

    EXECsys.sp_configureN'remote proc trans',N'1'
    GO
    RECONFIGUREWITHOVERRIDE
    GO

    · Then you have to go to DTC configurations of cluster in Win 2008 , 2008 R2 or DTC configurations of local nodes with win 2003 , 2003 R2.
    · Then apply the same setting you have carried out above
    · Then go to APP server and apply the same setting
    · Then assure the following 24 K ports are open bidirectional between DB+ APP server (1024 -65535) TCP +TCP 135 also (From DB to APP and from APP to DB)
    · Then download DTC ping from Microsoft and run it on both DB + APP server to validate everything is going properly before going live.
    · If fine , restart SQL Service to activate using DTC .

    If DTC not ping well with you , please let me know to come up with additional steps to overcome Firewall +OS obstacles VS DTC
    satya likes this.

Share This Page