Post reply

SQL Distributed Transactions - DTC

  • August 14, 2012 at 3:33 am

    #392946

    Hi All

    I'm trying to understand the reason for DTC in Clustered Systems

    If we have a 2 node Cluster with Shared Storage, why is DTC necessary is the data is written to one place?

    Also, what is the purpose of the Distributed Transaction Coordinator in SQL Management Studio?

    Thanks

  • August 14, 2012 at 9:47 am

    #1525611

    the DTC is used by many applications including sql server. In SQL Server a dtc is used for remote transactions such as lnked servers and openrowset queries.

    A clustered DTC is used so that in the event of a sql server failover the DTC is not bound to a single node. If the local DTC were used on node1 when a clustered instance named SQLCLUST\INST1 was active on node1, transactions would flow but upon failover of the clustered instance to node 2 all in flights transactions on node1 would now be orphaned. With a clustered instance the virtual name and IP allow the service to be addressed wherever the resources lie.

    Does that make sense?

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

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

  • August 14, 2012 at 11:01 am

    #1525669

    Perry Whittle (8/14/2012)

    the DTC is used by many applications including sql server. In SQL Server a dtc is used for remote transactions such as lnked servers and openrowset queries.

    A clustered DTC is used so that in the event of a sql server failover the DTC is not bound to a single node. If the local DTC were used on node1 when a clustered instance named SQLCLUST\INST1 was active on node1, transactions would flow but upon failover of the clustered instance to node 2 all in flights transactions on node1 would now be orphaned. With a clustered instance the virtual name and IP allow the service to be addressed wherever the resources lie.

    Does that make sense?

    It's making sense, slowly.....

    So when you have a clustered instance of SQL Server, all transactions go through DTC, irrespective of failover?

    So if node1 fails, DTC fails over to the other node and makes sure that all transactions are applied?

    Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

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