Setup MSDTC Cluster

Previous Setup MSDTC Network Access	Manual Configuration	Setup MSDTC Cluster Access Next

FineBuild can configure a MSDTC Cluster. This processing is only relevant if SQL Server is being installed as a cluster.

If MSDTC is setup as a cluster, then it can coordinate transactions that span multiple resource managers (such SQL database services, SSAS, and SSIS) with clustered instances of those managers.

If SQL Server is being installed on a Windows 2003 cluster then it is mandatory to also set up a MSDTC cluster. This is because MSDTC will not work unless it is also clustered, and SSIS packages will be unable to connect to SQL Server if MSDTC is not working. In addition, if you are installing a SQL 2005 cluster then MSDTC must be working in order to complete the SQL 2005 install.

For other versions of Windows it is not mandatory to set up a MSDTC cluster, but if MSDTC is required by your applications then it will work more efficiently if it is clustered.

It is therefore recommended to always setup a MSDTC cluster if Windows is clustered. When a MSDTC cluster is setup, then MSDTC on all nodes in the Windows cluster will automatically join the MSDTC cluster.

FineBuild MSDTC Cluster Configuration

The MSDTC Cluster configuration relates to Process Id 2ACD and is controlled by the parameters below:

SQL Version	Parameter	FULL Build	WORKSTATION Build	CLIENT Build
SQL2019	/SetupDTCCluster:	Yes	Yes	N/A
SQL2017	/SetupDTCCluster:	Yes	Yes	N/A
SQL2016	/SetupDTCCluster:	Yes	Yes	N/A
SQL2014	/SetupDTCCluster:	Yes	Yes	N/A
SQL2012	/SetupDTCCluster:	Yes	Yes	N/A
SQL2008R2	/SetupDTCCluster:	Yes	Yes	N/A
SQL2008	/SetupDTCCluster:	Yes	Yes	N/A
SQL2005	/SetupDTCCluster:	Yes	Yes	N/A

FineBuild also uses the following parameters to help configure MSDTC Cluster:

Parameter	Default	Description
/DTCMultiInstance:	YES	Creates separate MSDTC Clusters for each SQL Instance. Used on Windows 2008 and above
/ClusDTCSuffix:	TC	Suffix appended to Windows Cluster name for MSDTC Clusters
/ClusDTCIPSuffix:		IP Suffix (see note below). If this value is not supplied it will be calculated automatically
/LabDTC:	MSDTC	Disk Label to be applied to the drive holding the MSDTC Cluster files
/TCPPortDTC:	13300	Base port number for MSDTC

The IP suffix is added to the 'base' IP address for the Windows Cluster to create the IP address for the MSDTC cluster. In most situations the address calculated by SQL FineBuild can be used, but the ClusDTCIPSuffix parameter can be used to provide a specific address if it is required. For more details see Cluster IP Address Allocation.

For a Windows 2003 server, only one MSDTC cluster can be created.

On a Windows 2008 server and above, it is considered best practice to create one MSDTC cluster for each node of the windows cluster. This allows clustered services in Windows 2008 to access a local MSDTC cluster, which is more efficient than using the cluster network to access MSDTC. FineBuild will create one MSDTC cluster for every SQL Server instance you install on Windows 2008 or above. If you do not want FineBuild to create a MSDTC cluster for each SQL instance, then add the following parameter:

  /DTCMultiInstance:No

The following table gives examples of the effect of the above parameters.

Item	Value
Windows Cluster name	PDGB01SQLC01
Windows Cluster IP V4 address	192.168.0.150
/ClusDTCSuffix: value	TC
/ClusDTCIPSuffix: value	(blank)
/LabDTC: value	MSDTC
MSDTC Cluster name Windows 2003	PDGB01SQLC01TC
MSDTC Cluster name Windows 2008	PDGB01SQLC01TCA
MSDTC Cluster IP V4 address	192.168.0.151
MSDTC Cluster Drive label	MSDTC
/ClusDTCIPSuffix: value	160
Second MSDTC Cluster name Windows 2008	PDGB01SQLC01TCB
Second MSDTC Cluster IP V4 address	192.168.0.160
Second MSDTC Cluster Drive label	MSDTCB

SQL FineBuild will also work out the TCP Port to assign to the MSDTC Cluster and set up a Firewall exemption, based on the /TCPPortDTC: value.

The SQL FineBuild processing for Setup MSDTC Network Access consists of the following items:

• Configure MSDTC Network Access
• Configure MSDTC Port and Firewall Access

Top

---

Manual MSDTC Cluster Configuration

The following steps show what you would have to do for manual MSDTC Cluster configuration. FineBuild does all of this work for you automatically.

The example below uses the disk, server and IP addresses used in Install First SQL Server Cluster Node.

Configure MSDTC Network Access

1. Create the MSDTC cluster using the following commands:

   CLUSTER "PDGB01SQLC01DTC" GROUP "PDGB01SQLC01DTC" /CREATE
   
   CLUSTER "PDGB01SQLC01DTC" RESOURCE "PDGB01SQLC01DTC" ^
     /CREATE /GROUP:"PDGB01SQLC01DTC" /TYPE:""Distributed Transaction Coordinator"
   
   CLUSTER "PDGB01SQLC01DTC" RESOURCE "PDGB01SQLC01DTC Network Name" ^
    /CREATE /GROUP:"PDGB01SQLC01DTC" /TYPE:"Network Name" ^
    /PRIV NAME="PDGB01SQLC01DTC" DNSNAME="PDGB01SQLC01DTC"
   
   CLUSTER "PDGB01SQLC01DTC" RESOURCE "PDGB01SQLC01DTC IP Address" ^
     /CREATE /GROUP: "PDGB01SQLC01DTC" /TYPE:"IP Address" ^
     /PRIV ADDRESS="192.168.0.151" SUBNETMASK="255.255.255.0" NETWORK="Cluster"
   
   CLUSTER "PDGB01SQLC01DTC" RESOURCE "PDGB01SQLC01DTC Network Name" ^
     /ADDDEP:"PDGB01SQLC01DTC IP Address"
   
   CLUSTER "PDGB01SQLC01DTC" RESOURCE "PDGB01SQLC01DTC" ^
     /ADDDEP:"PDGB01SQLC01DTC Network Name"
   
   CLUSTER "PDGB01SQLC01DTC" RESOURCE "MSDTC" /MOVE:"PDGB01SQLC01DTC"
   
   CLUSTER "PDGB01SQLC01DTC" RESOURCE "PDGB01SQLC01DTC" /ADDDEP:"MSDTC" 
   

2. Put the MSDTC cluster online with the following command:

   CLUSTER "PDGB01SQLC01DTC" GROUP "PDGB01SQLC01DTC" /ON 
   

3. Setup network access for the MSDTC cluster

   Use the process given in Setup MSDTC Network Access, but using the MSDTC cluster name in place of the local MSDTC

Configure MSDTC Port and Firewall Access

The MSDTC Cluster must be assigned a TCP Port for Network Access, and that port must be given a Firewall exemption.

The following substitutions should be made if the strings appear in the commands below:

String	Value
PortId	Value for /TCPPortDTC:
HighPort	PortId value + 200
DTCGroup	Name of MSDTC Cluster
DTCGuid	Registry key of cluster resource with a \Name value that matches DTCGroup

1. Run the following command to show all the ports currently in use on the server:

   NETSTAT -an
   

2. If the port given in the /TCPPortDTC: parameter is not in use, then that port can be used for the MSDTC Cluster

   If it is in use then keep adding 1 (one) to the base port number until you identify a port number that is not in use

3. If you are using Windows 2008 R2 or above then run the following command to set up the Firewall port exemption:

   NETSH ADVFIREWALL FIREWALL ADD RULE 
    NAME="Distributed Transaction Coordinator DTCGroup (Port)" 
    LOCALPORT=PortId PROTOCOL=TCP ACTION=ALLOW PROFILE=DOMAIN DIR=IN
   

4. If you are using Windows 2008 R2 or above set the following registry key:

   HKLM\Cluster\Resources\DTCGuid\MSDTCPRIVATE\MSDTC\ServerTcpPort=PortId
   

5. If you are using Windows 2008 or below set the following registry keys:

   HKLM\SOFTWARE\Microsoft\Rpc\Internet\Ports="PortId-HighPort"
   HKLM\SOFTWARE\Microsoft\Rpc\Internet\PortsInternetAvailable="Y"
   HKLM\SOFTWARE\Microsoft\Rpc\Internet\UseInternetPorts="Y"
   

Copyright FineBuild Team © 2012 - 2021. License and Acknowledgements

Previous Setup MSDTC Network Access	Top	Setup MSDTC Cluster Access Next