It’s not a pretty title and it’s not a pretty subject, that much I can tell you. Strap in, this one’s a biggy….
The company I work for are busy developing an application that will make use of DTC from a DMZ, firing queries back to SQL using MSDTC hosted on an active-passive Microsoft Cluster running SQL 2005 all through a firewall. All of which I might add have been configured by me, so I know them like the back of my hand. On Wednesday this week I was given the above problem with a requirement to make it work on said highly available SQL 2005 Cluster by the following Tuesday. The obvious problems of ensuring uptime meant the work could only really be done out of hours and so it came down to doing it on a Sunday.
I obviously spent a fair amount of time researching the problem before Sunday and admittedly, lots of people have had the same issue so finding several KB article explaining how to achieve each task wasn’t too difficult but none of them really covered every aspect of what I was doing. Either they were about getting DTC working over a firewall or they were about configuring DTC on a cluster.
When you configure SQL Server 2005 on a Microsoft Cluster Server, the DTC resource is, as far as I can remember, added to the SQL cluster resource group. In one KB article I found, Microsoft recommends moving DTC to another resource group. I concluded, perhaps incorrectly, that while DTC is only being used for SQL, there is little sense or benefit in separating the resource from the SQL group. After all, if the SQL resource group is down, DTC won’t be used anyway. That’s not to say that if you use DTC for another purpose, other than SQL queries, that Microsoft’s recommendations shouldn’t be followed – they should.
Right, down to it then….
