September 30, 2009 at 12:03 pm
Does MSDTC have to be setup as a clustered resource or can i simply enable local DTC on both nodes in order for SQL Server DTC to work properly?
These two articles have me confused.
http://msdn.microsoft.com/en-us/library/ms189910.aspx
Install Microsoft Distributed Transaction Coordinator
Before installing SQL Server on a failover cluster, determine whether the Microsoft Distributed Transaction Coordinator (MSDTC) cluster resource must be created. If you are installing only the Database Engine, the MSDTC cluster resource is not required. If you are installing the Database Engine and SSIS, Workstation Components, or if you will use distributed transactions, you must install MSDTC. Note that MSDTC is not required for Analysis Services-only instances.
Configure Microsoft Distributed Transaction Coordinator
After you install the operating system and configure your cluster, you must configure MSDTC to work in a cluster by using the Cluster Administrator. Failure to cluster MSDTC will not block SQL Server Setup, but SQL Server application functionality may be affected if MSDTC is not properly configured.
http://technet.microsoft.com/en-us/library/cc730992(WS.10).aspx
Examples of MS DTC deployment in Windows Server 2008 failover clusters
This section describes clustering scenarios that represent changes in how you can deploy MS DTC in a Windows server cluster. The key modifications to MS DTC clustering support are as follows:
The local DTC can be active on all nodes in a failover cluster, independent of any additional MS DTC resources that might exist.
Multiple, clustered MS DTC resources can be active at the same time in a failover cluster.
You can have this configuration while having one clustered MS DTC resource that serves as the default MS DTC resource for the entire failover cluster.
October 1, 2009 at 1:02 pm
I was able to resolve this by following the steps listed on the pages below. DTC for SQL Server should be a clustered resource and be on a seperate group than SQL.
October 2, 2009 at 12:10 pm
To recap, when dealing with clusters – sometimes it is helpful to review the issue from two vantage points – the Windows cluster (2003/2008 or whatever version will be running at your site), as well as the application(s) that will be running on the cluster (in this case SQL 2008.) If you take that approach – you will be able to easily determine what is possible in terms of features and constraints related to the Windows cluster and certain things that may or may not be required from the Windows cluster side. Since your team is going to use Windows 2008, there are more options with configuring MSDTC then there were in previous versions. The new features are best utilized when an “active/active” cluster scenario will be in place as multiple MSDTC resources can now be configured (and can run in conjunction with a local DTC.) So once the features and limitations of the Windows cluster are understood – this will help determine how best to setup the cluster at your organization (also taking into consideration what hardware/software etc. is available to your organization.) The next thing that is required is to determine what application software your operation will be using on the cluster and what specific items are needed for that software. So in the case of SQL 2008, the documentation states that if any type of distributed transaction processing will be performed (or certain components of SQL used – like SSIS), then an “MS DTC resource” needs to be configured on the cluster. Since your company will be using an “active/passive” cluster – there really is no need to configure multiple MS DTC resources on the cluster – just one. Since the whole idea for a cluster is for high availability it is important to remember that any local DTCs will not be considered for use by other nodes in the cluster in the event of a failover - that is key.
Additionally – per general best practices – MSDTC and SQL should have their own group in the cluster and nothing should be added or removed from the Cluster group
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply