GAPTHEGURU

Geek with special skills

How to configure DTC for SQL Server in a Windows 2008 cluster

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 

  • 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”
  • Choose “Next” to go to the “Select Service or Application” screen
  • Choose “Distributed Transaction Coordinator (DTC)” then click “Next”
  • 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).
  • Choose a disk that is not already used and will not be the disk you choose during SQL Server setup.
  • Click “Next” twice.

b)Install SQL Server on at least one node.

c)Move DTC to the SQL Server group

  • Right click on the DTC resource and choose “More Actions” then “Move this resource to another service or application”
  • Choose your SQL Server group in “Select A Service or Application”.
  • Rightclick 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.
  • 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.

  • Open a dos prompt using “Run as Administrator”.
  • 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]
  • 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.
  • To verify SQL Server is mapped to the expected instance of DTC you can either
  • Shutdown 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

  • Run DcomCnfg
  • Navigate to Computers.Component Services.My Computer.Distributed Transaction Coordinator.Clustered DTCs
  • Right click on the DTC service for this SQL Server group and select “Properties”.
  • Go to the security tab and check “Network DTC Access”, “Allow Inbound”, and “Allow Outbound”.
  • 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

  • Rightclick on your SQL Server group under “Services and Applications” and
    choose “Add a resource” then “More resources” then “2 – Add Distributed
    Transaction Coordinator”.
  • Rightclick 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.
  • 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.
  • Go to the “Dependencies” tab make DTC dependent on the SQL Server name.
  • 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.
  • 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.

  • Open a dos prompt using “Run as Administrator”.
  • 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]
  • 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.
  • To verify SQL Server is mapped to the expected instance of DTC you can either
  • § Shutdown 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

  • Run DcomCnfg
  • Navigate to Computers.Component Services.My Computer.Distributed Transaction Coordinator.Clustered DTCs
  • Right click on the DTC service for this SQL Server group and select “Properties”.
  • Go to the security tab and check “Network DTC Access”, “Allow Inbound”, and “Allow Outbound”.
  • 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.

2 Comments »

  1. THANKS! This finally explained the tmmappings

    Comment by NB | 11/27/2012 | Reply

    • Your welcome and thanks for your feedback.

      Im going to post some more about sql clustering this month.

      Comment by gaptheguru | 12/06/2012 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: