Can I listen to you Mr. MSDTC?
MSDTC unlike on earlier version of Windows, support multiple instances on Windows 2008. You can create a Clustered MSDTC resource in each Cluster Group.
The below link gives you more information understanding MSDTC resources in Windows 2008 Failover Cluster.
https://technet.microsoft.com/en-us/library/cc730992(WS.10).aspx
Some Default Facts:
1. If there are no clustered MSDTC resources, SQL Server will initiate a connection to the Local MSDTC instance.
2. If there is a clustered MSDTC resource then the first Clustered Instance will be the Default MSDTC used by SQL Server Instances. You can set another instance to be default from Component Services Management Console.
3. If you have an MSDTC Configured within the SQL Server Group, then SQL Will use this.
If you have SQL Server 2008 Clustered and would like to use MSDTC in the same group, below are quick steps on how to create an MSDTC resource within SQL Server Group.
1. Right click [SQL Server] group via Failover Cluster Management tool
2. Point [Add resource]-[Other resources]-[Distributed Transaction Coordinator]
3. Add the dependency ([Network Name] and [Physical Disk]) to MSDTC resource
4. Bring the MSDTC resource online.
--TSQL to test distributed transaction:
Begin Distributed transaction;
Commit Transaction;
--Result: “Command(s) completed successfully.”
Let us look at a practical scenario.
I have an MSDTC clustered Instance as an independent Cluster Group. I also have a SQL Server group in which another MSDTC is clustered. Typically the SQL Server will use the MSDTC configured within its Group.
Disaster struck, My MSDTC within SQL Server Group is unable to Start. Fortunately I have a failover mechanism.
I can map the SQL Server instance to any MSDTC instance on the cluster. You just need to create a map which will facilitate the SQL Server to hook on to a specified MSDTC on your cluster. Below are the steps on how to create this mapping.
In a command prompt construct the below command.
msdtc -tmMappingSet -name <MappingName> -service <SQLServerServiceName> -clusterResourcename <MSDTCResourceName>
<MappingName> is an arbitrary name that you choose to identify this mapping.
<SQLServerServiceName> is the service name for your SQL Server instance. If this is the default instance, use MSSQLServer as your service name. If this is a named instance, use MSSQL$<InstanceName> as your service name.
<MSDTCResourceName> is the resource name for the instance of MSDTC to which you want to map SQL Server. You can get this from the properties of the MSDTC resource.
Example command:
msdtc -tmMappingSet -name SQLinst2MappingToDefault -service MSSQL$SQL2008INST2 -clusterResourcename "MSDTC-CluOneDtc"
More details on the Msdtc command options are discussed in the below Whitepaper. Please refer for any clarification.
Note: If you create an incorrect mapping, the MSDTC command still succeeds, but your mapping will not work correctly.
View existing mappings across the entire cluster.
The following command is used to view all MSDTC mappings across the entire cluster.
msdtc -tmMappingView *
Delete an existing mapping.
The following command is used to delete and existing MSDTC mapping.
msdtc -tmMappingClear -name <MappingName>
These mappings can be seen in the Registry at HKLM\Cluster\MSDTC\TMMapping\Service
Misc Observation: In SQL Server Management Studio, under the Management folder you can view the Status of Distributed Transaction Coordinator. This seems to always check the status of the Local MSDTC instead of the configured MSDTC for that particular SQL Server Instance.
This will turn RED if the local MSDTC service is stopped, but still the transactions are processed if the mapping is correct.
Levi Justus
Tech Lead, Microsoft SQL Server Support