What exactly is MSDTC, and when do I need it?

MSDTC (aka Microsoft Distributed Transaction Control) is a piece of software that a lot of people use, but they don’t really know what it does, or how it works.

MSDTC is used by SQL Server and other applications when they want to make a distributed transaction between more than one machine.  A distributed transaction is simple a transaction that 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 canceled.  The work on machine 1 needs to then be rolled back.

DTC is for the most part a black box.  It just sort of works without much interaction except for the initial setup.

The only time that DTC needs to be used is when more than one physical computer is going to be involved in an explicit 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.

I hope this helps explain DTC a little bit at least.  If you have specific questions about DTC, feel free to post them below and I’ll try and find the answers for you.

Denny

Share

22 Responses

  1. hi
    i want to learn about sql 2008 failover clustering on windows 2008 server.
    what is the order for installation?
    do we have to install MS DTC , and then the SQL instance?
    or we have to install sql cluster failover instance and then add MS DTC resource?
    and do we need to give a different ip to MS DTC?
    thanks

  2. Msakip,
    It is recommended that you install MSDTC on the cluster first that way SQL Server 2008 doesn’t show a warning during the install. However MSDTC can be installed afterward.

    When using Windows 2003 you don’t need a dedicated IP for MS DTC. Windows 2008 does require a dedicated IP for MS DTC.

  3. Hi Denny,
    I am new here and want to thank you for this valuable info.

    I have a 2 node cluster….win 2003.
    One node is iis server and the second is a file server. both are DC’s.
    I have inherited this system configured and do not have much knowledge about it.

    I have noticed in the past few months that every time i reboot the iis server (where there is a MS DTC resource)… it fails OK to the other node.
    But when it tries to fail back…..the DTC Fails…which cause the group to fail again to the other node….after few tries…the Cluster service on the IIS server stops. only if i shutdown the SAN and the 2 nodes…it is being resolved upon rebooting.
    Now…i don’t really know if i need this DTC on there since it is not SQL server….but let’s just say…. i want to delete every trace of this DTC on both nodes and recreate it….
    Can you give me some tips on how to do it?
    I am shiting myself every time this server needs a restart…

    cheers,

    Dadio

  4. Pingback: Configuring SQL Server Instances
  5. Hi Denny,

    I just configure Active Active Clustering on sql server 2008R2 and windows server 2012R2, now i want to know if one of my cluster fails then my website is not opening,so what should be done to failover website to the other instance.

  6. If you have two instances installed in the cluster, and they are both online at the same time, then you haven’t setup Failover Clustering correctly.  Failover clustering with SQL Server only supports Active/Passive clustering where the instance is active on only one node at a time.  When it is setup like this and the currently active node goes offline then the second node kicks in and the database comes up automatically without any user interaction.

  7. Hi Denny,

    We have 2 node cluster setup in our environment by windows admin with msdtc configured using ip address. I as a sql server dba asked my admin to provide one ipaddress for active/passive sql server cluster instance but he in turn replied and asked me to use the msdtc ipaddress. Is it possible to configure active/passive sql server cluster instance with the same ipaddress of msdtc?

    Windows 2012 Enterprise and SQL 2012 standard edition’s were involved in this scenario

    Thanks,

    Sandeep

  8. What are the advantages of setting up DTC as a clustered resource? We simply set up DTC on each of the individual nodes at the server level (and forgo clustering it). It seems to work for us and we haven’t had any issues.

  9. Sandeep,

    Yes you can have MSDTC configured with the same network name and IP address for the MSDTC and for the SQL Server. To do this simply create a new MSDTC resource within the resource group.

  10. FrancisApel,

    By putting the MSDTC in the same resource group as the SQL Server you reduce the number of checks that the SQL Server has to do when it is looking for the resource group.  I have seen situations where not having the MSDTC clustered could cause the distributed transactions to fail.

    My suggestion would be that if it’s working now, don’t change it. MSDTC is a pain and I’d only change it if there was a good reason to.

  11. I’ve got this issue where my Dell laptop is waking up (if it’s on AC power) at 10:26 pm, and I don’t know why. I have nothing enabled in my “scheduled tasks” (I’ve actually disabled Scheduled Tasks itself on several occasions, only to have it running again the next time I check) and every “wake computer to perform this task” I can find is unchecked. I’ve been watching the TaskManager around this time, trying to figure out which process is the culprit. I did catch “msdtc.exe” starting around this time. Does it have the privilege of waking the computer from a shut down state? My Dell is not part of a network, doesn’t have any hardwired internet access, and is out of range of any accessible Wi-Fi signals when this is happening.

  12. Wendy,

    Whatever’s waking up your computer you can be rest assured that it isn’t MSDTC. It doesn’t have the ability to do that.

    If there’s a mouse connected it could be something as simple as a pet bumping the table, or if the mouse is sensitive enough, I’ve woken a computer up by walking past the desk. My weight pushing down on the carpet caused the desk to shift just a little bit, which caused the mouse to wake up, which caused the computer to wake up. I’m just making some wild guesses here, it could really be anything.

  13. Hey Denny, I know MSDTC will cause failure if you have Memory Optimized Tables referenced in your TSQL. My question is, do you have experience with MSDTC and TempDB? I have an instance(InstanceA) with memory optimized TEMPDB and another instance(InstanceB) without. If the code opens a distributed transaction and both instances have some reads and writes, but none of the system tables/views in TempDB are referenced, will it fail?

  14. Brian,
    Honestly, I’m not sure. You can test this by forcing a DTC transaction using “CREATE DISTRIBUTED TRANSACTION” which will involve DTC for you.
    Denny

  15. Thanks. I am going to try that as well as setup a distributed transaction in within c# hitting both servers.

  16. Hi, I loose my DTC setting in Component Services>Computer>My Computer>Distributed Transaction Coordinator…I cannot view the details. When I reboot the server, this appears again, it happens every time (night) and my server is not intended to be rebooted due to need to be consistence for data replication.

Leave a Reply to Configuring SQL Server InstancesCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Trust DCAC with your data

Your data systems may be treading water today, but are they prepared for the next phase of your business growth?