This project has moved. For the latest updates, please go here.

Previous Setup MSDTC Network Access Manual Configuration Install .Net 2.0 Next

Setup MSDTC Cluster

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 2AD and is controlled by the parameters below:

Parameter Build SQL2005 SQL2008 SQL2008 R2 SQL2012 SQL2014
SetupDTCCluster FULL Yes Yes Yes Yes Yes
SetupDTCCluster WORKSTATION Yes Yes Yes Yes Yes
SetupDTCCluster CLIENT N/A N/A N/A N/A 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.

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 using 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 MSDTC Port
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 © 2012 - 2014 Edward Vassie. License and Acknowledgements
Previous Setup MSDTC Network Access Top Install .Net 2.0 Next

Last edited Oct 31, 2014 at 5:05 PM by EdVassie, version 8