Wednesday, July 18, 2012

What is MSDTC

MSDTC - Microsoft distributed transaction coordinator.

SQL Server uses MSDTC when it wants to execute any transaction on more than one server. Distributed transaction is also a normal transaction but it spans across multiple servers. In distributed transaction some part of transaction is executed on server1 and then it connected to server2 to execute remaining part of the transaction. In simple words when a transaction updates data on more than one server. MSDTC helps to ensure the distributed transaction successfully completed. This DTC uses two phase commit protocol, in the phase one the transaction manager request to prepare commit for each component involved in the transaction and in the phase two, if all the components in the transaction is successfully prepare then it announce the commit decision.

a) Application manager calls the transaction manager to begin a transaction

b) When application has completed its changes then it asks transaction manager to commit the transaction. If all components are prepared then it broadcasts commit decision, if any of the components are not prepared then it broadcasts abort decision.

c) If a transaction manager fails when the transaction is prepared for commit or rollback then that transaction will become in-doubt transaction. The reconciliation will happen when the transaction manager re-connects again.

To enable & Configure MSDTC in windows 2003 ServerControl Panel – Add remove programs – Application Server – Details
Enable network DTC access – OK
Component Services – My computer – properties -- MSDTC Tab -- Configure from here.

To enable & Configure MSDTC in windows 2008 Server
Server Manager – Roles – Add Role – Select Application Server – Select Inbound and Outbound Transactions – Install – After completion
Administrative Tools – Component Services – My Computer – Distributed Transaction Coordinator - Local DTC –Configure from here

No comments: