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:
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...