Setup MSDTC Cluster - SQL-FineBuild/Common GitHub Wiki
| 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:
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
-
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" -
Put the MSDTC cluster online with the following command:
CLUSTER "PDGB01SQLC01DTC" GROUP "PDGB01SQLC01DTC" /ON -
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 |
-
Run the following command to show all the ports currently in use on the server:
NETSTAT -an -
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
-
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 -
If you are using Windows 2008 R2 or above set the following registry key:
HKLM\Cluster\Resources\DTCGuid\MSDTCPRIVATE\MSDTC\ServerTcpPort=PortId -
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 |
|---|