How to configure DTC for SQL Server in a Windows 2008 cluster
How to configure DTC for SQL Server in a Windows 2008 cluster
[This post was revised 11 May 2009 with new mapping information, fixed formatting issues 23 March 2010]
With Windows 2008 you now have the option to use multiple DTCs. There is a lot of confusion over whether to do that for SQL Server and what the exact steps are. If you use multiple DTCs you must also make sure you map each SQL Server to a specific DTC.
How and Where
Here is some information to help you choose how many DTCs to have and where to put them:
Multiple DTCs:
· Positive: Better performance (not sure how noticeable to the average application)
· Negative: More administration
One DTC:
· Positive: Easy to admin
· Negative: May become overloaded/performance bottleneck (probably rare)
One DTC per SQL Server but all the DTCs in one group instead of in each SQL group:
· Negative: you have to assign a disk and IP to each
· Positive: you can have DTC affect the group - if you're willing to have all DTCs failover when one fails
· Negative: you can end up with DTC and SQL on separate nodes which (with the possible exception of a geo-cluster) probably has a negligible performance impact but still could theoretically present more problems than having DTC and SQL both local.
One DTC no matter how many SQL Servers - in its own group
· Negative: if it does down/fails over it affects all SQLs (or other apps that use it)
· Positive: easy to set up/maintain
· Negative: DTC and SQL can be on separate nodes (see above)
· Negative: you have to assign a unique disk and IP
One DTC no matter how many SQL Servers - in one of the SQL groups
· Negative: if DTC goes down/fails over it affects all SQLs (or other apps that use it)
· Positive: easy to set up/maintain
· Negative: DTC and SQL can be on separate nodes (see above)
One DTC per SQL Server, each in the same group as the SQL Server it is mapped to
· Positive: If you need to move or restart DTC it only affects one SQL Server
· Positive: no extra disks or IPs needed (though giving DTC its own disk is still recommended for the best performance)
· Positive: it's easy for admins to see how its configured (assuming they didn't forget the mapping)
· Negative: requires more administration (though still very minimal, just a couple minutes per DTC)
· Positive: best performance
· Positive: It can simplify troubleshooting as your testing can be isolated to one group.
One DTC per SQL Server, each in its own DTC group with only a disk, IP, and name
· Positive: DTC can affect the group
· Negative: requires more disks and IPs
· Negative: DTC and SQL can be on separate nodes (see above)
Multiple DTCs, but some DTCs service more than one SQL
· Combination of above
Steps for DTC in a SQL Group
Here are the steps for creating DTC in a Windows 2008 cluster with one DTC per SQL Server and the DTC is in the SQL Server group. The ability to use multiple DTCs is new in Windows 2008, for older versions of Windows these steps will not work.
In Windows 2008 you can no longer make DTC dependent on the quorum disk so it either needs its own disk or can share with an application such as SQL Server. For the best performance you should give DTC its own disk.
There are two ways you can go through these steps, both options are covered below.
1. Connect
· Start "Failover Cluster Management"
· Connect to your cluster (click on the local cluster on the left or choose "Manage a Cluster" on the right and enter your remote cluster name)
2. If you are using a firewall make sure it has an exception for DTC and/or the DTC IP addresses.
3. Add a new DTC resource using one of the two options below.
This is Option 1 from the blog mentioned at the end of this thread. The advantages are:
· DTC may have its own IP and name.
· Some steps can be completed before SQL is installed.
· Requires a disk NOT used by SQL Server during setup, but it can be a disk that will later be used by SQL Server.
· You can choose the name of the DTC resource.
a) 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.
b) Install SQL Server on at least one node.
c) 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.
d) (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.
This is Option 2 from the blog mentioned at the end of this thread – Pros/Cons include:
· DTC will depend on the SQL Server resource name and IP
· SQL must be installed first
· You have no choice on the DTC resource name
· Fewer overall steps
a) Install SQL Server on at least one node.
b) Add DTC
i. Right click on your SQL Server group under "Services and Applications" and choose "Add a resource" then "More resources" then "2 - Add Distributed Transaction Coordinator".
ii. Right click on the new (offline) DTC resource and choose "Properties". Note that the DTC resource name is MSDTC-SQL Server (YourInstanceName) and it cannot be changed.
iii. Go to the "Dependencies" tab make DTC dependent on a disk in the SQL Server group. If DTC is not heavily used you can choose a disk already used for SQL Server, but this could have a performance impact.
iv. Go to the "Dependencies" tab make DTC dependent on the SQL Server name.
v. 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.
vi. Bring DTC online.
c) (Optional for SQL Server 2008) Map SQL Server and 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 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.
d) 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.
Hints:
· Repeat the above steps for any additional SQL Server instances you install.
· If you use DTC heavily you should consider putting it on its own disk for optimal performance. Otherwise you can associate it with your least used SQL Server disk.
· Do NOT make SQL Server dependent on the DTC resource.
· Do NOT check "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.
More information:
· SQL Server 2008 Failover Clustering White Paper https://download.microsoft.com/download/6/9/D/69D1FEA7-5B42-437A-B3BA-A4AD13E34EF6/SQLServer2008FailoverCluster.docx (link updated 15 April 2014)
· How to Configure Multiple Instances of Distributed Transaction Coordinator (DTC) on a Windows Server Failover Cluster 2008 https://blogs.technet.com/askcore/archive/2009/02/18/how-to-configure-multiple-instances-of-distributed-transaction-coordinator-dtc-on-a-windows-server-failover-cluster-2008.aspx
· Windows Server 2008 Changes Simplify SQL Server Clustering https://www.sqlmag.com/Articles/ArticleID/101502/101502.html?Ad=1
Comments
- Anonymous
February 22, 2009
PingBack from http://www.anith.com/?p=12588 - Anonymous
March 21, 2009
<p><span style="font-size: small"><span style="font-family: Arial"><span lang="EN" style="font-family: 'Calibri','sans-serif'; mso-fareast-font- ... - Anonymous
June 10, 2009
I have copied this over from an older blog . I have cleaned it up a bit to clarify a few areas and added - Anonymous
October 04, 2010
The pros and cons put some options in different perspective. Thanks! - Anonymous
March 02, 2011
Really nice blog post Cindy :) - Anonymous
August 15, 2011
Very helpful, thanks! - Anonymous
May 07, 2012
This is a great article. I have followed it to install a separate MSDTC instance on each of my SQL app groups. Now I want to remove my old msdtc stand-alone group from my cluster. Is there anything in particular I need to do? or can I just delete the group? - Anonymous
May 28, 2013
Fantastic quick start. When editing the cluster DTC settings to enable network access it is worth pointing out that local accounts (i.e. the default "NT AUTHORITYNetworkService") are scoped to the cluster network name, unlike the SQL service accounts during setup, which warns you that local accounts are not supported (because they would be the physical cluster node not the virtual cluster resource name/computer account). Just to clarify it's okay there. Thanks. - Anonymous
July 18, 2013
Great post, very helpful. In your Option 1 -> a) step looks like you didn't mention IP address which is an IP address needed or can that row be unchecked?...and what is the recommended best practice if any? Thanks in advance. (Please excuse if this is duplicate since looks like my initial post didn't successfully go through.) - Anonymous
July 30, 2013
What type of RAID should be used for the MSDTC disk drive? - Anonymous
August 19, 2014
The comment has been removed - Anonymous
October 06, 2014
I agree with Marios. You missed the option of a local install.