I have 4 servers with two active/passive clusters, let's call them Cluster A with Servers A1 and A2 and Cluster B with server B1 and B2. All servers have SQL Server 2005 Enterprise Edition SP2 on Windows 2003. My problem is occuring on cluster B. When I failover to B2, Distributed Transactions fail for about 20 minutes for all linked server queries from cluster A to B and for all linked queries from cluster B to A. Linked Server queries without the Begin Distributed Transaction work fine right after the failover. After about 20 minutes, without making any changes, Distributed Transactions start back working. When I fail back to B1, Distributed Transactions work just fine immediately.
The settings on MSDTC( In Component Services) are the same for both B1 and B2. The error messages are usally any of the standand messages received from Linked Server. See the link below.
Any help would be greatly appreciated.
asked Oct 14 '09 at 11:24 AM in Default
The order of the groups moving shouldn't cause a problem. When the SQL Server starts up are there any DTC related errors in the logs? How about after DTC starts working?
When the distributed transactions are failing is the failed transactions counter going up on the ClusterB?
Which check boxes to you have checked on the two clusters in the DTC Security area?
answered Oct 19 '09 at 02:08 AM
It sounds like MSDTC isn't starting immediately on the failover or is waiting for other services to get established before starting.
answered Oct 15 '09 at 06:01 AM
I had exactly the same issue, SQL could not start a distributed transaction after a successful cluster node failover, even though the MSDTC appeared to be up-and-running. I found a hint to the issue here: link text
I added the SQL Service account local Administrators group on the server - then it all worked fine. You may have to uninstall/reinstall MSDTC on the cluster nodes to get everything in a working state first.
answered Nov 03 '11 at 06:37 PM