Problem: How can I run Distributed Transaction Coordinator (DTC) on all servers in an AlwaysOn Availability Group (AG)? I do NOT need to maintain transactions over failover/switchover events.
Setup: I have a Windows Failover Cluster (WSFC) with three Windows 2008 R2 servers where they are all running SQL 2012. Two servers are in one data center and are part of an AlwaysOn Failover Cluster (FCI), while the third server is in a second data center. The WSFC is a multi-subnet cluster. Here's a sketch of the setup:
I've been able to install and configure DTC to work between the two FCI nodes because they are on the same subnet and share storage. I have configured a couple of AGs and they have been working fine. This screenshot shows DTC installed on the FCI:
This screenshot shows that I can configure DTC on one of the FCI nodes (whichever is active):
I'd like to migrate an application that uses DTC onto this cluster and use an AG. I've read that DTC is not supported with AGs (Reference). I have not been able to find a way to configure DTC on the third node in the second data center. When I try to configure DTC on the third node, it seems to be unavailable, as shown in this screenshot:
In Brent Ozar's Free Setup Checklist PDF for Availability Groups he lists:
Cluster Installation…
29. If an FCI is involved, configure DTC per your Planning section decisions.
In the comments on SQL Server 2012 AlwaysOn Availability Groups Rock Brent says that "… nothing changes when AGs are in play. Just keep in mind that databases in an Availability Group don’t support transactional consistency when failed over together to another replica…"
This makes it seem that DTC can be used in Availability Groups as long as you understand that transactions will not be maintained in a AG switchover. I would not need it to maintain transactions from the FCI nodes. I would just need DTC available for the application to use in the case of a catastrophic disaster (where I lost my primary data center).
How do I configure DTC on my third node? Or, is it the case that I'm just out of luck when it comes to using AGs and an application that needs DTC?
UPDATE: The solution I've settled on is to use Log Shipping. However, in the case of a fail over, I still need DTC to be available on Node3. I've discovered that it becomes available by uninstalling the clustered MSDTC-MSSQLSERVERCLU instance of DTC that is shared between Node1 and Node2. Once removed, I can setup and configure a LocalDTC instance on Node3. Afterwards, I can reinstall the clustered MSDTC-MSSQLSERVERCLU instance. Doing the install sequence in that order seems to work. I've been running like that for a little while now and I have not discovered any ill effects. It seems like this would also work for running an AlwaysOn Availability Group. I understand that distributed transactions would not be preserved in an AG failover, I would just need new ones to work after the failover. But I haven't gone there…
Best Answer
Elijah. There's two separate questions here:
1. Is DTC supported with AlwaysOn Availability Groups?
You're using SQL Server 2012, and according to Microsoft's Documentation, that answer is no. I totally understand that you want to try it anyway, but keep in mind that you're now putting something into production that Microsoft simply will not support, AND you're using two separate niche features together (AGs and DTC). If anything whatsoever goes wrong, you're going to be in a world of hurt. This just isn't something I'd ever even think about trying in production.
Keep in mind that if your managers find out that you deployed something Microsoft specifically says in big letters, "YOU CAN'T DO THIS," and you have any kind of outage where you have to call Microsoft for support, you're going to have some ugly explaining to do.
Technically, DTC is supported starting with SQL Server 2016 SP2 and later, but it just means that you can pick which database loses data on failover, and the application has no idea data was permanently lost. That's not what a normal database administrator would call DTC support.
2. How should DTC be configured in a multi-node, multi-subnet cluster?
Read Allan Hirt's post on configuring DTC with multiple instances of SQL Server in a cluster, and make sure to read all of the links in the post as well.