Let me share my one of the experiences I had couple of weeks ago with my 4 node Active-Active Cluster. Our Non prod envi is a 4 Node A-A cluster with SQL Server 2005 SP3 (Don’t ask me why we are still on SQL 2005 and more even why on SP3 ;))
Folks out there who deal with lot of linked Servers and Distributed Queries might already have seen this behavior of SQL Server with Failover of MSDTC on SQL Server 2005 sitting on a Windows Server 2003.
Well, we all know our Windows administrators will be rolling over Patches and Hot fixes to Servers monthly once or at least once every couple of months. Due to the patching they had to restart windows Servers and I’m not sure what order they did and the next day Developers were standing behind me saying “ My distributed Queries stopped working” what’s happening?
When I verified MSDTC Service was running on Node2 without any issues. I tried to failover just MSDTC Service instead of entire SQL Group from Node 2 to Node 1 and fail it back to Node 2.
Well, this made the distributed transactions to run from Node 1 with no issues, but not from Node 2(In fact rest of nodes).
When I saw the SQL Error logs on Node 2, I saw my error log filled with “SQL Server could not register with Microsoft Distributed Transaction Coordinator (MS DTC) as a resource manager for this transaction. The transaction may have been stopped by client or the resource manager”
Everything looks pretty normal when I saw from “CLUADMIN” with SQL Server and MSDTC service. So at this point I suspected the order of Services being brought Online…when windows team restarted Servers(Nodes in this case)
So this time I tried DTC Service started before the SQL Server Service was brought Online, so that SQL won’t fail to register itself and DTC transactions will be initiated properly. Voilaaa….this made it workedJ
Actually, I got this clue from one of the MSDN blogs when I was BINGING the issue. So, I would like to say, don’t just bang your head against a wall if you see any issue, Look at your SQL Server Error Logs( My favorite place and my First place to look for any SQL errors) or even you can go to Microsoft blogs and seek for help. There are tons of tips and material for SQL Server ( It’s up to you whom to trust and whom not to).
MSSQLTREK 