Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Bruce Sherwood
Starting Member
12 Posts |
 Posted - 2010-09-03 : 14:42:37
|
| Here is a stumbling block I am trying to wrap my head around:In an Active/Active cluster it is suggested you create a DTC resource for each clustered instance. Straight forward. There is even a wizard to help you do it.But the part I don't get is how do I tell which clustered application to use which DTC resource?The suggestion Allows each clustered application to have its own DTC resource in case one crashes, the other clustered applications aren't affected.If I understand it right, I should be able to use the MSDTC command line tool to do this. But I guess I don't quite get it as its a bit cryptic (written by Devs for Devs?) and when I piddle with it I mostly get failures.Also: what if I create the DTC resource inside the Clustered application? (yes, I will give it its own drive and its own name) Does this automatically make it the default DTC resource for that specific clustered application? (Seriously I need to get a book or something - but everyone just talks about Active/Passive clusters where this isn't an issue) |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
|
|
Bruce Sherwood
Starting Member
12 Posts |
Posted - 2010-09-07 : 18:39:57
|
| Boy howdy, this one was hard.The example given was a bit helpful, in that it gave an example that was partly useful, but jumped track by using MSMQ as the application needing DTC access mapped to a new clustered DTC instance.What threw me here is trying to figure out what the MSDTC command line option for '-service' is supposed to be. The actual name of the SQL Server service? The name of the clustered resource group?I finally figured out I can make it work if I used the clustered SQL instance server name. I validated this by shutting down the clustered DTC resource I theoretically mapped to and ran a distributed transaction query in TSQL to see what would happen.So as far as I can tell the command line to map a clustered SQL instance to a given clustered DTC instance is:msdtc -tmMappingSet -name MyMapping1 -service MySQLVirtServerName -ClusterResourName MyDTCresourceNamein my case MySQLVirtServerName happens to be "BS-SQLC1"and my MyDTCresourcename happpens to be "MSDTC-DTCC1"(** BTW: you have to be local administrator when running the MSDTC command line tool or you get very useless error messages claiming a failure, but not what or why.)Testing consisted of setting up my new clustered DTC resource. Mapping my clustered SQL instance to use it. Creating a linked server connection to a standalone SQL server that I know works just fine with distributed transactions. Then running a transaction with an insert statement across the linked connection to the standalone server. Turning off the clustered DTC resource (taking it offline) in the cluster manager would break the insert. Turning it on would let it work again.FYI: before you set the mapping, you fall through to using the current hosting nodes local DTC service. This can hide that you are not actually using the mapped resource. Turning off the one you think you mapped should show you a failure - if it does not, you are using the local nodes copy.At this point my only concern is that if I turn off my local nodes copy of of the DTC service, but leave my clustered DTC resources service running, my DTC transactions still fail. And I am not sure why. You would think the whole transaction would stay inside the mapped resource and not be reliant on the local nodes DTC service to be running. |
 |
|
|
Bruce Sherwood
Starting Member
12 Posts |
Posted - 2010-09-08 : 14:31:01
|
| Well, so much for that idea.I can now create mappings. And it seems the instances are using their mappings. But... It looks like the instance to instance communication only works inside the same node. If each instance is on different nodes, then it fails to run a distributed transaction.And.. No. It is not due to firewalls. DTCPing works fine from node to node.If I drop my mappings, and destroy my clustered DTC resources, which forces everything to fall back to the local nodes DTC service, it all works fine across nodes.Oye. A bit more thinking required on this one. |
|
|
|
Bruce Sherwood
Starting Member
12 Posts |
Posted - 2010-09-08 : 16:42:01
|
| Okay - so here is what I now have in had."Use Cluster Manager"!If you use cluster manager to create a DTC Service/Application outside of another application (such as SQL Server) it steps through more steps. Specifically it assures it has a unique name and IP (as well as dedicated disk of course). You really need those.So, use that. Then do your mapping with the MSDTC command line tool. Then when you have done all the testing you want, go into the DTC application in cluster manager and "move this resource into another service or application". Move it into your SQL server instance. This assures when a failover occurs, both resources (SQL Server, and the DTC resource) move together. Let the DTC resource keep its network name and IP address. Then it will work like a charm.How is that for more clarity then you normally find on the internet? |
|
|
|
Duran
Starting Member
9 Posts |
Posted - 2010-10-15 : 04:29:18
|
| Hello Bruce,I wondered if you could help me out, I am basically doing exactly what you were doing on your last post. I am also a little confused when it comes to the DTC part. I have already used cluster manager to create a MSDTC resource on a clustered disk. I will be putting multiple instances on each node.Do you know if I need to have a separate DTC resource for each instance, or can I/do I point all the instances to the one DTC group (using the command line after all the SQL instance have been installed)?I was also wondering if, as its Active/Active, do I need to create another DTC resource on another disk for the node B after making it the preferred onwner for that disk? I have a feeling I do.Any advice you could give would be appreciated.Regards,D.Yes, we have no banana's. |
|
|
|
|
|
|
|
|
MSDTC in Active/Active clustering