SQL Server Performance

SQL Server Cluster and MSDTC

Discussion in 'SQL Server 2005 Clustering' started by robd08, Aug 5, 2009.

  1. robd08 New Member

    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.
  2. MohammedU New Member

  3. robd08 New Member

    Thanks. I've read that article before and it confuses me. It gives the impression I can just use the local DTC as long as my applications "do not require the degree of availability that is provided by failover clusters". I don't understand what that means. If I've clustered the instance I want high availability. If I don't cluster MSDTC does it mean the databases themselves are highly available but MSDTC isn't? How does that work in practice? Does it mean my users can connect to their databases OK in the event of a failover but distributed transactions will be lost?
    I'm also confused about whether I have an active/passive cluster or an active/active one. Here's my configuration...
    Physical Server 1
    SQL Instance 1 (clustered, primary node)
    SQL Instance 2 (clustered, primary node)
    SQL Instance 3 (clustered, primary node)
    SQL Instance 4 (clustered, passive node)
    SQL Instance 5 (clustered, passive node)
    Physical Server 2
    SQL Instance 1 (clustered, passive node)
    SQL Instance 2 (clustered, passive node)
    SQL Instance 3 (clustered, passive node)
    SQL Instance 4 (clustered, primary node)
    SQL Instance 5 (clustered, primary node)
    SQL Instance 6 (local)
    SQL Instance 7 (local)

Share This Page