3

I've been working on setting up clustered environment with a two new DTC roles, DTC1 and DTC2. I set up DTC1 which allows both clustered VM's and SQL Server Instances to function correctly. However, when I disable DTC1, DTC2 will remain online but no DTC transactions will work. I'm actually going through this issue with a Microsoft tech right now (without much luck). Both DTC services are set up with similar configurations. Anyone here have an idea as to what might be going on?

Here's my setup:

  • One clustered environment
  • Two DTC Roles
  • Two Separate ISCSI disks, one for each role
  • Two VM's (same configuration)
  • Two SQL Server Instances, on on each VM

Networking configuration is allowed, everything looks to be set up properly.

  • But in the future please don't just re-create an identical question on the other network - flag it for migration. Making duplicate questions just creates extra work for others. – Aaron Bertrand Dec 29 '14 at 20:26
  • Sorry about that. – HMan06 Dec 29 '14 at 20:39
  • Which versions of Windows and SQL Server? Could you also explain the intended purpose of each DTC instance? For example, do you want each SQL Server instance associated with a different DTC instance? – James L Dec 29 '14 at 21:27
  • SQL Server 2014 and SQL Server 2012. I want them to be generic so that either instance can use either DTC just in case one fails. – HMan06 Dec 29 '14 at 21:40
0

I don't believe what you want to do is possible through Windows or SQL Server configuration. Per Microsoft's guidance here, SQL Server will choose an MSDTC instance when it starts and, if that MSDTC instance fails, distributed transactions will fail:

What happens if the MSDTC Resource that was picked at SQL Service startup fails?

If the MSDTC instance that is being used by a specific SQL Server Group fails, SQL Server does not automatically attempt to use the next available MSDTC Resource such as the default cluster instance or the local machine instance of MSDTC.

You would need to completely remove the failed instance of MSDTC from the SQL Server group to use another instance of MSDTC.

Likewise, if you create a mapping for SQL Server and the mapped instance of MSDTC fails, your distributed transactions will also fail. If you want SQL Server to use a different instance of MSDTC, you must either add an instance of MSDTC to the local cluster group of the SQL Server or delete the mapping.

This matches the behavior you're seeing; SQL Server successfully opens DTC transactions through DTC1 but doesn't automatically switch to using DTC2 if DTC1 fails.

In some cursory tests I found:

  • DTC transactions resumed working when the failed MSDTC instance recovered.

  • SQL Server was indeed able to automatically switch to using a different MSDTC resource without a restart after the MSDTC resource it was using was deleted (as opposed to just failed).

The tests I ran weren't comprehensive and there might be scenarios where these findings aren't true.

The more important point in the document is that you may not need to cluster MSDTC at all:

What will SQL do if I do not Cluster MSDTC in Windows 2008 and above?

If there is no MSDTC resource in the cluster then SQL Server will use the MSDTC service that is running locally on the node.

What is the Simple recommendation for MSDTC with Windows 2008 and later?

In Windows 2008 and later you either Create a clustered instance of the MSDTC resource for EVERY SQL Server instance /Group that requires its functionality or DO NOT CLUSTER MSDTC at all.

Based on this, you should be able to achieve pretty good high availability by relying on local MSDTC resources (in which case you might need to configure them to restart automatically on failure) or through adding a clustered MSDTC resource to each SQL Server cluster group if you prefer for some reason. Windows can handle restarting the service on failure, and SQL Server appears to successfully resume creating DTC transactions once the MSDTC service recovers.

If you're trying to protect against scenarios not covered here (e.g., what happens if the DTC resource fails and can't restart automatically?) you might be forced to deal with that manually or at the application layer.

It will be interesting to see if Microsoft provides you with different advice.

  • Thank you, I believe I read this incorrectly. So if I wanted this to work automatically, I would have to create some type of powershell script to delete the DTC. – HMan06 Dec 30 '14 at 20:43
  • So if there is a failure though, and the first node switches over to the second node, DTC should still work? – HMan06 Dec 30 '14 at 20:57
  • Yes, if a node failed, all resources from the failed node should come back online on the other node, and DTC should resume working. – James L Dec 30 '14 at 21:11
  • Great, that answered my question. Thanks James. – HMan06 Dec 30 '14 at 21:19
  • To answer your other question, yes, if you wanted to make SQL Server automatically use DTC2 if and only if DTC1 failed, you'd have to delete DTC1 as well as any mappings of the SQL Server instance to DTC1 and make sure DTC2 is next in the order of evaluation listed in the documented I linked to. But I don't see any advantage in doing this versus using local DTC instances or configuring a DTC instance in each SQL Server group. With either of those options, Windows can be configured to bring up MSDTC if it fails, and you don't have to deal with telling SQL Server to use another DTC instance. – James L Dec 30 '14 at 21:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.