Mike Steineke

Clustered DTC and Multiple SQL Instances

MSteineke

There are a few ways you can configure MSDTC when you need to use it on a SQL Fail over Cluster Instance.   We have applications that need to commit transactions across multiple SQL FCIs so a Clustered DTC is necessary.  If you only have 1 active SQL node, it is pretty easy, you create a clustered DTC, which lives as its own clustered Application. 

 

As you can see MSDTC has an IP resource, a name resource and a Disk resource in the cluster, and the disk resource is mounted as a drive letter.

Although there are a few ways that are supposed to work to create multiple MS DTC instances in a cluster, there is only one that I have found to work reliably.  That way is to create an MSDTC service inside each SQL application.

 

 As you can see above there are all the same resources that are in a single clustered MSDTC, with a few modifications. I have shared the Drive Volume F with the SQL System databases, to not waste another drive letter, since SQL needs to use one anyway. You need to make sure that you have enough space to hold the databases and DTC.  In our configurations we also move TempDB to another LUN, by default it would be in this location and it would be easy to run out of disk.  I have also added a dependency for the SQL Service on the MSDTC Service to ensure that the MSDTC instance is running before SQL starts.  SQL will then always pick the MSDTC that is running in the same cluster application.  I have found to make this work, you must have a unique name and IP for each MSDTC contrary to documentation that says you can share a name and IP.

 

 

Comments (1) -

September 3. 2013 12:04

Interesting!  I have a DTC resouce which is not part of the SQL App.  If SQL Fails over without DTC, performance goes to nothing.  Can you please comment on this configuration.  Is it possible to resolve this without creating separate DTC resources, that is, making the SQL failover dependent on DTC failover without manual intervention??

Thanks for your help,

Mark

Mark United States

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading