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.

This should about cover it:

http://weblogs.sqlteam.com/geoffh/ar...-Clusters.aspx


--
Geoff N. Hiten
Principal SQL Infrastructure Consultant
Microsoft SQL Server MVP


"robd08" <robd08 (AT) discussions (DOT) microsoft.com> wrote

Thanks. I've read that article before. I find the the technet link confusing
- 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 that. If I've clustered the instance I
want high availablility. 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?

"Geoff N. Hiten" wrote:

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)



"robd08" wrote:

The Technet link was for clustered and non-clustered. Ignore the
non-clustered part.

--
Geoff N. Hiten
Principal SQL Infrastructure Consultant
Microsoft SQL Server MVP


"robd08" <robd08 (AT) discussions (DOT) microsoft.com> wrote

Now you see why I HATE the Active/Passive/... naming structure. It is a
holdover from SQL 7 that no longer adequately describes modern clusters.

You have a multi-instance cluster. That is correctly expressed with N nodes
and I instances. 5 Node 5 Instance cluster. Which node each instance is on
is purely a temporary configuration.

BTW, mixing local and clustered instances on the same hardware is not
recommended. Woprks, but is a PITA to administer.

--GNH


"robd08" <robd08 (AT) discussions (DOT) microsoft.com> wrote

Geoff

Not sure what you mean by "ignore the non-clustered part". The Technet
article talks about using a local DTC in a failover cluster. Would that work
in my multi-instance environment? I don't understand the difference between
using the local DTC and using a clustered DTC.

Rob

"Geoff N. Hiten" wrote:

Follow these steps to install MS DTC in cluster environment.

• Start Cluster Administrator.
• In the left pane, expand Groups. Right-click the group that contains the
dedicated disk for Use with MS DTC, and select Rename it; enter the new name
for the resource group as something logical, such as MS DTC
• Right-click the newly renamed resource group, select New, and then select
Resource In the New Resource dialog box, enter an appropriate name for the IP
resource that will be associated with MS DTC such as MS DTC IP Address. In
the Resource Type drop-down list, select IP Address. In the Group drop-down
list, make sure the right resource group is selected. Click Next.
• In the Possible Owners dialog box, verify that all nodes of the server
cluster are listed as possible owners of this resource. If a node is not
listed on the right side, select it from the Available Nodes list, and click
Add. Click Next.
• In the Dependencies dialog box, select the drive from Available Resources,
and click Add to add it to the Resource Dependencies list. When finished,
click next.
• In the TCP/IP Address Parameters dialog box, enter a static IP in the
Address input box, and select the proper public network to use. Click Finish.
• If the resource is created, then resource now appears in the resource
group and has a state of Offline. Right-click MS DTC’s resource group,
selects New, and then select Resource.
• In the New Resource dialog box, enter an appropriate name for the network
name resource that will be associated with MS DTC such as MS DTC Network
Name. In the Resource Type drop-down list, select Network Name. In the Group
drop-down list, make sure the right resource group is selected.. Click Next.
• In the Possible Owners dialog box, verify that all nodes of the server
cluster are listed as possible owners of this resource. If a node is not
listed on the right side, select it from the Available Nodes list, and click
Add. Click Next.
• In the Dependencies dialog box, select the MS DTC IP address from
Available Resources, and click Add to add it to the Resource Dependencies
list. When finished. Click Next.
• In the Network Name Parameters dialog box, enter a name for MS DTC that
will be used by other applications. Click Finish.
• After resource is created. Click OK. The resource now appears in the
resource group and has a state of Offline.
• Right-click MS DTC’s resource group, select New, and then select Resource.
In the New Resource dialog box, enter an appropriate name for the MS DTC
resource itself such as MS DTC. In the Resource Type drop-down list, select
Distributed Transaction Coordinator. In the Group drop-down list, make sure
the right resource group is selected.
• In the Possible Owners dialog box, verify that all nodes of the server
cluster are listed as possible owners of this resource. If a node is not
listed on the right side, select it from the Available Nodes list, and click
Add. Click Next.
• In the Dependencies dialog box, select the physical disk and the MS DTC
network name From Available Resources, and click Add to add them to the
Resource Dependencies list.
• Start the newly created resources by right-clicking the MS DTC group and
selecting Bring Online

Also, to ensure that external processes can utilize the clustered MS DTC,
you must enable network MS DTC access. Follow these steps:

1. Log on to the node that currently owns MS DTC.
2. Open Add or Remove Programs in Control Panel.
3. Click Add/Remove Windows Components.
4. Select Application Server, Click Details.
5. In the Application Server dialog box, make sure that the Enable Network
DTC access option is checked. Click OK.
6. Click Next. You might be prompted for your Windows installation media, so
you should have it nearby or on an accessible network share.
7. Click Finish on the Completing the Windows Components Wizard dialog box.
8. Stop MS DTC by right-clicking the cluster group and selecting the option
Bring Offline.
9. Restart MS DTC by right-clicking the cluster group and selecting the
option Bring Online.

For more information see
http://crazysql.wordpress.com/2009/0...ws-clustering/

Manpreet Singh
http://crazysql.wordpress.com/
MCITP –DBA, DD - SQL server 2005
MCTS – SQL server 2005
MCTS –SharePoint Server 2007


"robd08" wrote:

Manpreet

Thanks for the detailed info on how to cluster DTC. But is still not really
what I'm after. The question is - do I need to go to all that trouble of
clustering DTC. What will be the effects if I don't.

Rob

"Manpreet Singh" wrote:

If you have more than twice the instances as you have nodes, then local
MSDTC is good. Fewer than that and each instance should have its own MSDTC.


--
Geoff N. Hiten
Principal SQL Infrastructure Consultant
Microsoft SQL Server MVP


"robd08" <robd08 (AT) discussions (DOT) microsoft.com> wrote