Hi - we are configuring a new SQL Server cluster.  The cluster details are the following:  2 nodes, Active/Passive, SQL Server Version = 10.0.4000 (SQL Server 2008 SP2) and Windows 2008 Service Pack 2.  This cluster will be in production, and my question is the following:

Do we need to cluster the MSDTC resource? 

We have an ongoing debate about whether to cluster MSDTC on this SQL Server cluster or not.  We're just beginning to swap out our Windows 2003 / SQL Server 2005 clusters with new Windows 2008 / SQL Server 2008 clusters, and some of our DBA's have said with Windows 2008 the need to cluster MSDTC is not necessary for high availability as it was in Windows 2003.  We can use the local DTC on each node.

My concern is during an unexpected failover of the SQL Server services how will transactions be handled if we do not cluster MSDTC?  Couldn't we lose transactions?  This would not be good (financial impact) if we lost transactions. 

Some of our DBA's are very adamant that this isn't necessary (clustering MSDTC on the new Windows 2008 clusters).  But when I research Microsoft seems to recommend clustering MSDTC for high availability.  But why offer that option if Windows 2008 has redesigned MSDTC so you don't have to? 

Need some advice\perspective. 

I would add an MSDTC resource to the SQL instance.  You still should create a clustered MSDTC instance, but you can now have more than one per cluster.

Hi,

Check this document in the link below and search for MSDTC 

http://www.google.com.sg/url?sa=t&source=web&cd=11&ved=0CBYQFjAAOAo&url=http%3A%2F%2Fdownload.microsoft.com%2Fdownload%2F6%2F9%2FD%2F69D1FEA7-5B42-437A-B3BA-A4AD13E34EF6%2FSQLServer2008FailoverCluster.docx&rct=j&q=how%20to%20configure%20msdtc%20for%20sql%20server%202008%20cluster%20site%3Amicrosoft.com&ei=p9DETfD4OYbqrQeK7pDXBA&usg=AFQjCNGI1hQIgs4JkiNAfD6zVvVl1-8UuA 

Some more good information which will help you to decide on MSDTC

http://blogs.msdn.com/b/cindygross/archive/2009/02/22/how-to-configure-dtc-for-sql-server-in-a-windows-2008-cluster.aspx

http://blogs.msdn.com/b/asiatech/archive/2009/10/29/how-to-configure-msdtc-resource-for-sql-server-2008-failover-cluster.aspx

http://technet.microsoft.com/en-us/library/cc770748(WS.10).aspx

HTH 

Hello,

 

I have found this to be a great guide to way all the options. A nice list with pros & cons :http://blogs.msdn.com/b/cindygross/archive/2009/02/22/how-to-configure-dtc-for-sql-server-in-a-windows-2008-cluster.aspx

 

Best regards & good luck,

 

Didier Van Hoye

http://workinghardinit.wordpress.com

 

One DTC per SQL server and one SQL server per group seems like the way to go in 2008 and later. All positive on the performance and fail-over sides, only negative is a few steps of configuration at the start.

Check the article in @WorkingHardInIt's post above for detailed instructions.

The link from the Microsoft answer no longer works, so I suggest his post is the correct answer.

In the above mentioned post of http://blogs.msdn.com/b/cindygross/archive/2009/02/22/how-to-configure-dtc-for-sql-server-in-a-windows-2008-cluster.aspx  in her  Option 1 -> a)   she doesn't mention an IP address...which is an IP address needed or can that row be unchecked?  Thanks in advance.

I think the confusion is over the use of the word "IP Address" when really it just means "Server Name" using exact MS Failover Cluster definitions. Only the logical clustered server name is the fixed part, the address could even assigned via DHCP.

For example in my configuration I have two SQL Server clustered instances on two possible cluster nodes so both can be active to distribute load during normal operation, then one node takes over all services during a failure. As suggested in the preferred configuration everything they need (including MSDTC) is housed within their own clustered service/application "group".

The configuration looks like this in Failover Cluster Manager:

Services and Applications

> SQLCluster1 : Preferred Owners: SQLNode1

> > Server Name : SQLCluster1

> > MSDTC : Network Name (SQLCluster1)

> > Disk Drives : SQL Cluster 1 Data : Volume (S:)

> > Other Resources : SQL Server (Cluster1), SQL Server Agent (Cluster1)

> SQLCluster2 : Preferred Owners: SQLNode2

> > Server Name : SQLCluster2

> > MSDTC : Network Name (SQLCluster2)

> > Disk Drives : SQL Cluster 2 Data : Volume (R:)

> > Other Resources : SQL Server (Cluster2), SQL Server Agent (Cluster2)

Notes:

1. MSDTC exists within the clustered service/application (SQL) group so does not require a separate disk, name or quorum access.
2. Optionally for performance you might want to add separate disks dedicated to the MSDTC, SQL log and temp data files. Only makes sense if the physical disk at the end of the line (including iSCSI targets) is separate, else it's just more hassle to configure with no benefit.
3. Each SQL instance must have a unique SQL Instance name (in case they end-up running on the same server, i.e. during a failure). My instance names were Cluster1 and Cluster2, i.e. SQL Server addresses "SQLCluster1\Cluster1" and "SQLCluster2\Cluster2".

There's one other important point to keep in mind for anyone setting up their first SQL cluster. When you access the SQL Server from another machine over the network, you must omit the instance name. But only remotely, not when connecting locally on the cluster node, e.g. running management tools.

It's like the clustered SQL instance becomes the default instance even though it has it's own name different to the typical MSSQLSERVER default instance name. I guess this has something to do with the possibility that multiple "default" instances could end up on the same physical server. Still it's very confusing when you try to connect and get errors that the SQL server was not found, e.g. you must connect remotely as SQLCluster1 but locally as SQLCluster1\Clu