How to configure shared MSDTC resource for Windows 2008 Failover Cluster

  • Article

 

In this document, I'd like to give you some brief ideas about how to configure MSDTC resource in Windows 2008 Failover cluster environment. I suppose you have successfully setup windows failover cluster. If you haven't, please refer to the following articles to setup.

 

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

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

 

Environment: Windows 2008 SP2 Failover Cluster

Step 1: Make sure to add the DTC role

 

In "Server Manager", click "Add Role" to add DTC role. The screenshot is like below after finishing.

 

HOWTO_DTC_1.png

 

 

Step 2: Make sure to allow inbounds and outbounds in Firewall

1) If the windows firewall is turned on, please make sure to enable all "Distributed Transaction Coordinator" related "Inbound Rules" and "Outbound Rules" in "Windows Firewall with Advanced Security" MMC, please refer to the picture.

HOWTO_DTC_9.png

2) If there's some other firewall within your network, please refer https://support.microsoft.com/kb/250367 to make MSDTC work through the Firewall.

Step 3: Create MSDTC resource

1) Open the Failover Cluster Management console on any of the cluster node.

2) Under the cluster name, right-click on Server and Applications and select Configure a Service or Application. This will run the High Availability Wizard

HOWTO_DTC_2.png

2)  In the Select Service or Application dialog box, select Distributed Transaction Coordinator (DTC) and click Next.

HOWTO_DTC_3.png

4)   In the Client Access Point dialog box, enter the name and IP address of the clustered MSDTC. This should be a different IP addresses and host name from the one that the Windows Server 2008 cluster is already using. Click Next.

HOWTO_DTC_4.png

5) In the Select Storage dialog box, select the disk subsystem that will be used by MSDTC. These disk subsystems have to be defined as available storage in your cluster. Click Next

HOWTO_DTC_5.png

6) In the Confirmation dialog box, validate the configuration you have selected for MSDTC and click Next

7) In the Summary dialog box, click Close. This completes the installation of MSDTC on the cluster.

8) You can validate your installation of MSDTC by expanding the Services and Applications node and check the cluster name of MSDTC. Make sure that all of the dependency resources are online

HOWTO_DTC_6.png

Step 4: Enable Network Access for MSDTC resource

1) Open "Component Services" MMC, right click "My Computer" and select "Properties", on the "MSDTC" tab, we can find the MSDTC resource which we created in the previous step is the "Default Coordinator" now. In this situation, all the transactions will be handled by this MSDTC resource.

HOWTO_DTC_7.png

2) Expand My Computer->Distributed Transaction Coordinator->Clustered DTCs, right click our MSDTC resource(SQL2K8-CLUSDTC) and select Properties.

HOWTO_DTC_8.png

3) On the Security tab, check "Network DTC Access", "Allow Inbound", "Allow Outbound", "Incoming Caller Authentication Required", then click OK.

HOWTO_DTC_10.png

Now you can run the DTCTester tool from a client machine to do testing if you installed SQL Server Failover on this cluster. Please pay attention that all the services will use this MSDTC resource to coordinate with other DTC as we only have one MSDTC resource. We can also configure a private MSDTC resource for the SQL Server instance, please refer to How to configure individual MSDTC resource with SQL cluster application in SQL Server 2008 Failover Cluster

Of course we can also map a MSDTC resource to a specific service, please refer to https://technet.microsoft.com/en-us/library/cc742483(WS.10).aspx

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

 

Best Regards,

 

Zhixing Lv