I'm setting up my first cluster (SQL Server 2005 on Windows 2008) and need advice regarding MSDTC (Microsoft Distributed Transaction coordinator). From my research it seems one of the steps in setting things up is to cluster MSDTC and that appears to be a fairly complicated process for a humble DBA. In a non clustered environment you don't have to worry about it - MSDTC is at the operating system level and just works. Apparently it's role is to coordinate distributed transactions to ensure consistent updates across multiple databases.
When clustering SQL on Windows 2003 you have to first cluster MSDTC (otherwise I think the SQL install won't complete). But with Windows 2008 clustering has changed in some ways and you can actually install a clustered SQL instance without clustering MSDTC, as I have done. I think this has something to do with a 2008 cluster being able to access the local MSDTC service whereas 2003 can't. I have monitored our database servers with SQL profiler and I can't find any applications that use distributed transactions, although we do use distributed queries via linked servers.
So my question is, given that I'm using Windows 2008 and none of my apps use distributed transactions, do I need to go to the trouble of clustering MSDTC? I've read something about Integration Services relying on it, but I have that running on my cluster now without issue. Also, in the future if I do get an app that uses distributed transactions, what behaviour should I expect on a cluster without MSDTC clustered? Will errors show in the SQL logs or will things seem to work as normal except updates won't be consistent between databases? In my current environment I've been able to run a BEGIN TRANSACTION statement across 2 servers without issue, so I don't see why I need to waste time clustering MSDTC.
I think the only time this has come up for us was when we installed a SQL Server service pack. Not the best time to have to fall back, and figure out why things are failing. if i were you, I would go ahead and get MSDTC clustered. It is not terribly hard on Windows 2003. Due to budgetary problems, I can not attempt it on Windows 2008 for a while, though.
Thanks for your reply. I've already applied a SQL Server service pack to my cluster without issue. My feeling at the moment is to leave MSDTC as is and only cluster it if there's a reason to.
From what I've read about Windows 2008 clustering it's recommended to have a DTC resource for each virtual instance. I have a multi-instance setup - some clustered, some local - and I'm running out of drives. Plus you have to do something in component services to enable network access? Seems a lot of hassle when everything is working OK as is. I don't want to risk breaking an alreading working system by clustering MSDTC incorrectly.
I have a test clustered instance on the same physical servers so I can test the impact of service packs, new apps etc. If the test instance breaks because MSDTC isn't clustered I can always cluster it at that point. Does that seem reasonable?