Post reply

SQL DTC Confusion

  • February 25, 2013 at 2:33 am

    #290268

    Hi All

    I am trying to figure out the ins and outs of DTC and how SQL uses it.

    Does SQL only ever use DTC when SQL is installed as clustered?

    Thanks

  • February 26, 2013 at 9:03 pm

    #1591373

    SQL also will the DTC if you try using explicit transactions with Linked Servers. I also use it with SSIS transactions, not technically the database engine but another use of DTC nonetheless.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • February 26, 2013 at 11:14 pm

    #1591391

    opc.three (2/26/2013)

    SQL also will the DTC if you try using explicit transactions with Linked Servers. I also use it with SSIS transactions, not technically the database engine but another use of DTC nonetheless.

    Thanks

    So even if SQL is installed as non clustered, DTC can still become involved somehow

    If that's the case, why when dealing with Clustered instances of SQL Server do we have to explicitly create a DTC component in the Cluster?

    Thanks

  • February 27, 2013 at 9:05 am

    #1591597

    So even if SQL is installed as non clustered, DTC can still become involved somehow

    Yep.

    If that's the case, why when dealing with Clustered instances of SQL Server do we have to explicitly create a DTC component in the Cluster?

    My understanding is that the Windows cluster relies on it. DTC is not a SQL Server-specific piece of software. It is a stand-alone Windows service that SQL Server sometimes leverages. SQL Server clusters sit on top of Windows clusters, and its the Windows cluster that requires the DTC to be configured.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • February 28, 2013 at 3:44 am

    #1591923

    SQLSACT (2/26/2013)

    So even if SQL is installed as non clustered, DTC can still become involved somehow

    DTC is not just used for SQL Server, the OS uses it for remote calls too.

    SQLSACT (2/26/2013)

    If that's the case, why when dealing with Clustered instances of SQL Server do we have to explicitly create a DTC component in the Cluster?

    Thanks

    In a clustered environment, a clustered DTC is used to recover inflight transactions. Take the following scenario:

    Node1 and Node2 have a clustered instance INST1 which is currently active on Node1. DTC is local and not clustered.

    A remote transaction is started and during the transaction the instance goes offline and fails over to Node2. The SQL instance is no longer local to the stand alone DTC and cannot connect to recover the inflight transaction.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • February 28, 2013 at 6:11 am

    #1591956

    Thanks Guys - This clears up a lot

    Perry, would you say that it's safe to not cluster DTC when you have a single node cluster?

    Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply