How to configure individual MSDTC resource with SQL cluster application in SQL Server 2008 Failover Cluster

 

In the Windows 2008 Failover Cluster environment, we recommend the "Incoming Caller Authentication Required" security setting for the MSDTC service. I'll describe how to create the private MSDTC cluster resource for the SQL instance.

Environment: Windows 2008 SP2 Failover Cluster

SQL Server 2008

Step 1: Install the SQL Server cluster

Since this step is not the main purpose of this document, so I won't give you the detailed information of this topic. Please refer to the following documents to install SQL Server Cluster.

https://download.microsoft.com/download/6/9/D/69D1FEA7-5B42-437A-B3BA-A4AD13E34EF6/SQLServer2008FailoverCluster.docx

https://www.mssqltips.com/tip.asp?tip=1687

Step 2: Create the MSDTC resource for the SQL instance

1. In "Failover Cluster Management" MMC, right click the SQL instance and select Add a resource->More resources… ->2-Add Distributed Transaction Coordinator

 

STEP1.jpg

2. Then, the MSDTC resource will be listed in the same group. Please right click the MSDTC resource and select "Properties", then input the dependencies of Network name and disk.

 

STEP2.jpg

 

 

3.  Bring this MSDTC resource online

 

4. Open "Component Services" MMC, you can find this MSDTC instance on the left tree. Right click this MSDTC instance and select "Properties", then check the following items.

Network DTC Access

Allow Inbound

Allow Outbound

Incoming Caller Authentication Required

STEP4.jpg

 

5. Now, we need to change the Network resource name to the DNS name, otherwise the MSDTC runtime can't get the proper Network resource at runtime. After you install the SQL Cluster, the Resource Name could be "SQL Network Name(<DNS name>)", please refer to the following picture.

 

STEP5.jpg

 

To change the resource name, please run the following command:

Cluster.exe res "SQL Network Name (<DNS name>)" /ren:<DNS name>

For example, I need to run this command in my scenario: Cluster.exe res "SQL Network Name (sean-sl08)" /ren:sean-sl08

Now, your MSDTC resource can work properly. You can run the DTCTester tool to do testing.

References

Understanding MS DTC Resources in Windows Server 2008 Failover Clusters: https://msdn.microsoft.com/en-us/library/cc730992(WS.10).aspx

Enable Network Access Securely for MS DTC: https://technet.microsoft.com/en-us/library/cc753620(WS.10).aspx

How to Configure Multiple Instances of Distributed Transaction Coordinator (DTC) on a Windows Server Failover Cluster 2008: https://blogs.technet.com/askcore/archive/2009/02/18/how-to-configure-multiple-instances-of-distributed-transaction-coordinator-dtc-on-a-windows-server-failover-cluster-2008.aspx

Regards,

ZhiXing Lv