DTC - conditions DTC is invoked

Under what conditions would DTC be invoked?  I understand that it is between two servers, using a linked server, with four part naming schemes.  Is DTC only used when the remote action is inside a transaction?  Would implicit transactions enlist in a distributed transaction as well?  If you call a SP on the remote server and send in an xml document with your rows to insert/update/select, would that participate in a DTC transaction?  

We are contemplating using mirroring or always on in our environment and know that DTC is not supported.  We are looking at what we would need to do to have our environment (which I have traced in profiler and know is using DTC transactions) changed in order to use these technologies.

March 20th, 2015 4:27pm

Hello,

DTC is use to create distributed transactions with one or more computers. DTC may be used on linked servers, SSIS packages, OPENROWSET, OPENQUERY, OPENDATASOURCE, remote procedure calls, replication between instances and BEGIN DISTRIBUTED TRANSACTION.


For more information.

http://blogs.msdn.com/b/cindygross/archive/2010/10/18/do-i-need-dtc-for-my-sql-server.aspx


Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com


Free Windows Admin Tool Kit Click here and download it now
March 20th, 2015 5:41pm

If you have a transaction in progress and you access a linked server, by default the transaction will be promoted to a distributed transaction. There is a server option to turn this off. But it goes without saying that if you are performing updates on both sides, you most likely want a distributed transaction.

One thing people often run into is that the do things like:

   INSERT localtbl (...)
      EXEC SERVER.db.db.some_sp

Since the INSERT statement defines a transaction by itself, the stored procedure will execute in the context of that transaction which will be promoted to a distributed transaction. This can be a major hassle, if you cannot get DTC to work. On the other hand, assuming that you call a read-only procedure, you may not care about that transaction why you can turn off promotion if that is all you do on that server.

Then again, I don't think those types of distributed transaction are something to worry about in a mirroring scenario. That is, there is nothing to stop you from using distributed transaction just because you have mirroring in force. It works just fine - as long as there are no failover. But if there is a failover, there is no guarantee that the transactional consistency is upheld.

March 20th, 2015 7:24pm

Hi DBAFan,

A distributed transaction is simple a transactions which spans between two or more machines.  The basic concept is that machine 1 starts a transaction, and does some work.  It then connects to machine 2 and does some work.  The work on machine 2 fails, and is cancled.  The work on machine 1 needs to then be rolled back.

The only time that DTC needs to be used is when more than one physical computer is going to be involved in an explicet distributed transaction.  If you are going from one instance to another on the same server DTC will not be needed.  If you are going from one instance to another within a cluster you will want to have DTC available as you may have to go between nodes of the cluster as you have no guarantee that the instances will be on the same physical node.

Hope this little information helps you.. for more information on DTC ( MSDTC ) click on below link:

http://blogs.msdn.com/b/florinlazar/archive/2004/03/04/what-is-msdtc-and-why-do-i-need-to-care-about-it.aspx

https://technet.microsoft.com/en-us/library/cc759136(v=ws.10).aspx

DTC in Depth

Yashwant Vishwakarma,
SQLOcean.com
Free Windows Admin Tool Kit Click here and download it now
March 21st, 2015 1:45am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics