MSDTC in a multi-node, multi-instanced cluster

Question

MSDTC in a multi-node, multi-instanced cluster

Adam Bean

One Orange Chip

Points: 26631
More actions
August 17, 2015 at 10:09 am

#319846

Hey all,
More often than not, I typically don't touch DTC on clusters anymore; however on a project where the vendor states that it's required. So a couple things here.
1) Do you really need DTC per instance or one for all?
2) Should DTC be in its own resource group or within the instance's group?
2a) If in it's own resource group, how do you tie an instance to an outside resource group? tmMappingSet right?
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

lkennedy76 SSChampion Points: 13683 More actions · Answer 1

I am not really sure about 2 and 2a but on 1, yes you have to add it to each resource on your cluster. If it rolls over DTC has to be turned on or if it is AlwaysOn, which is really standalone installs, you have to have it on each one in case it becomes primary.

MCSE SQL Server 2012\2014\2016

Adam Bean One Orange Chip Points: 26631 More actions · Answer 2

lkennedy76 (8/17/2015)
I am not really sure about 2 and 2a but on 1, yes you have to add it to each resource on your cluster. If it rolls over DTC has to be turned on or if it is AlwaysOn, which is really standalone installs, you have to have it on each one in case it becomes primary.

Thanks for the response Ken.

Not sure I quite understand. You're saying that "yes you have to add it to each resource on your cluster", so are you saying that you should have DTC per instance? Further, when you say "each resource" are you then saying that DTC has to be married to the instance's resource group?

Thanks

lkennedy76 SSChampion Points: 13683 More actions · Answer 3

Adam Bean (8/17/2015)
lkennedy76 (8/17/2015)
I am not really sure about 2 and 2a but on 1, yes you have to add it to each resource on your cluster. If it rolls over DTC has to be turned on or if it is AlwaysOn, which is really standalone installs, you have to have it on each one in case it becomes primary.
Thanks for the response Ken.
Not sure I quite understand. You're saying that "yes you have to add it to each resource on your cluster", so are you saying that you should have DTC per instance? Further, when you say "each resource" are you then saying that DTC has to be married to the instance's resource group?
Thanks

if you have node1 and node2 and the application requires DTC you must give the account required permissions on both nodes.

MCSE SQL Server 2012\2014\2016

Adam Bean One Orange Chip Points: 26631 More actions · Answer 4

Adam Bean

One Orange Chip

Points: 26631

August 17, 2015 at 5:35 pm

#1820597

Not sure I'm following you on that one.

lkennedy76 SSChampion Points: 13683 More actions · Answer 5

Adam Bean (8/17/2015)
Not sure I'm following you on that one.

sent you a PM

MCSE SQL Server 2012\2014\2016

Adam Bean One Orange Chip Points: 26631 More actions · Answer 6

In reviewing this article: http://blogs.msdn.com/b/alwaysonpro/archive/2014/01/15/msdtc-recommendations-on-sql-failover-cluster.aspx#Why-would-I-create-a-MSDTC-instance-for-each-SQL-Server-Instance

It would seem that they're making a recommendation to keep DTC contained within each SQL resource group vs. independently in their own resource groups.

Adam Bean One Orange Chip Points: 26631 More actions · Answer 7

Adam Bean

One Orange Chip

Points: 26631

August 18, 2015 at 6:35 am

#1820686

Any other input on this topic?

Thanks!

Perry Whittle SSC Guru Points: 234013 More actions · Answer 8

Adam Bean (8/17/2015)
1) Do you really need DTC per instance or one for all?

depends how busy the instances using DTC will be, if you expect high volume then 1 per instance if not 1 for all would work

Adam Bean (8/17/2015)
2) Should DTC be in its own resource group or within the instance's group?

Ideally if you create a DTC for a specific instance put it in the same cluster role\group

Adam Bean (8/17/2015)
2a) If in it's own resource group, how do you tie an instance to an outside resource group? tmMappingSet right?
Thanks

Yes, you can map a DTC to specific groups via tmMappingSet

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Adam Bean One Orange Chip Points: 26631 More actions · Answer 9

Thanks Perry, good timing.

Ended up putting DTC in each resource group and mapped appropriately.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 10

if you put a dedicated dtc into the resource group for the instance it will serve you dont usually need to create the mapping, having said that it cant hurt

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉