SQL 2008 : Cluster DTC Configuration
In order to configure a DTC per SQL instance perform the following steps:
Create a new DTC resource
i. Look for a green arrow in the middle pane next to “Configure a Service or Application” and click on it to open the “High Availability Wizard”
ii. Choose “Next” to go to the “Select Service or Application” screen
iii. Choose “Distributed Transaction Coordinator (DTC)” then click “Next”
iv. You can either use the default name or create your own unique name for the “Client Access Point” then click “Next”. You may want to name it something like DTC_YourSQLServerResourceName. For example: if your SQL instance’s virtual name is SQL01, call your DTC resource something like DTC_SQL01 or MSDTC-SQL Server (SQL01).
v. Choose a disk that is not already used and will not be the disk you choose during SQL Server setup.
vi. Click “Next” twice.
Install SQL Server on at least one node.
Move DTC to the SQL Server group
i. Right click on the DTC resource and choose “More Actions” then “Move this resource to another service or application”.
ii. Choose your SQL Server group in “Select A Service or Application”.
iii. Right click on the DTC resource and choose “Properties”. Go to the “Policies” tab and uncheck “if restart is unsuccessful, fail over all resources in this service or application” for the DTC resource unless you are sure you want a DTC failure to cause SQL Server to fail over. This is the same thing as “affect the group” in older versions of Windows.
iv. Optionally you can take DTC offline and make it dependent on the SQL Server name and disk. Then remove its dependencies on the original name and disk. Then you can optionally delete the original IP, name, and disk or move them out of the SQL Server group.
(Optional for SQL Server 2008) Map SQL Server to this DTC resource.
If you skip this step SQL Server 2005 will use the default DTC. SQL Server 2005 will only use the DTC in the SQL Server group if that one is the default for the entire cluster or if you do the mapping step. SQL Server 2008 will use the DTC in its group unless that DTC is not online/started when SQL Server starts or if you create a mapping to a different DTC.
i. Open a dos prompt using “Run as Administrator”.
ii. Modify the below statement with a mapping name (make up a name to id the mapping), service (the SQL Server service name – not the display name), and ClusterResourceName (DTC resource name) then run it
Example: Msdtc -tmMappingSet -name DTC_SQL01_Mapping -service “MSSQL$SQL01” -ClusterResourceName “MSDTC-SQL Server (SQL01)” <<-- Note the correction I made here on 3/21/09 to the service name for SQL, and the additional verification steps below
Do you want to continue with this operation? [y/n] y [enter]
iii. Run “msdtc -tmmappingview *” to verify the mapping was created successfully. The mapping is also stored in HKEY_LOCAL_MACHINE\Cluster\MSDTC\TMMapping\Service\ as a new key named the same as the mapping parameter used in the msdtc command.
iv. To verify SQL Server is mapped to the expected instance of DTC you can either
§ Shut down all the other DTC services and make sure the mapped DTC is online. Verify you can do a BEGIN DISTRIBUTED TRANSACTION from this SQL Server.
§ Do a BEGIN DISTRIBUTED TRANSACTION from this SQL Server, verify it succeeds and roll it back. Then shut down the mapped DTC and verify a new BEGIN DISTRIBUTED TRANSACTION will work.
e) Enable network access
i. Run DcomCnfg
ii. Navigate to Computers.Component Services.My Computer.Distributed Transaction Coordinator.Clustered DTCs
iii. Right click on the DTC service for this SQL Server group and select “Properties”.
iv. Go to the security tab and check “Network DTC Access”, “Allow Inbound”, and “Allow Outbound”.
v. When you hit “Apply” or “OK” it will restart the DTC resource.