Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Failover Clustering and DTC Expand / Collapse
Author
Message
Posted Tuesday, March 20, 2012 9:05 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 9:04 AM
Points: 59, Visits: 561
I am installing SQL Server 2008 r2 SP1 on a 64-BIT Windows Server 2008 r2 SP1 Enterprise server in an Active/Passive failover cluster environment.

I am trying to cover all my bases and following the documentation provided by msdn...
I am going through the 'Before Installing Failover Clustering' ( http://msdn.microsoft.com/en-us/library/ms189910.aspx )

My question pertains to the following part of this article:
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.


I am installing the Database Engine and SSIS which puts me into the must install MSDTC category.
My question pertains to the statement 'you must configure MSDTC to work in a cluster by using the Cluster Administrator'.
Can someone provide an article for a best practice for how to do this?
Do I need to also (is it required/can I get away without)?...
Enable Network Access Securely For MS DTC (http://technet.microsoft.com/en-us/library/cc753620(WS.10).aspx)
Enable Network DTC Access ( http://technet.microsoft.com/en-us/library/cc753510(WS.10).aspx )

For now I will not be doing the two items above, but I feel that I will need to configure MSDTC to work in a cluster.

I really do not want to over configure this environment for features which I do not need.
Any advice would be useful.
Post #1269630
Posted Tuesday, March 20, 2012 10:31 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 10:52 AM
Points: 1,264, Visits: 3,558
It is very simple. You'll need an IP address and a disk resource to setup the MSDTC resource. This site is a pretty good walk through for the process. Check it out.

______________________________________________________________________________________________
Forum posting etiquette. Get your answers faster.
Post #1269721
Posted Wednesday, March 21, 2012 11:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:25 PM
Points: 6,194, Visits: 13,350
Calvo's link is fairly detailed, you may also see my article at this link, review the top section for installing the Windows roles and services which include Windows DTC configuration then scroll down to the section "Create the Clustered MSDTC Resource" to see how to create the clustered DTC application\service

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1270365
Posted Wednesday, March 21, 2012 11:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:40 AM
Points: 7,001, Visits: 8,439
I suggest to read the information already pointed to by the previous respondents.

To help out a bit more I've added a Powershell (2.0) script that I created at the time I installed my n-th instance.
We provide an MSDTC-resource with each individual SQLInstance on a cluster.

Keep in mind you need to test the script to see if it actually performs the configurations you aim for.

It still needs to be modified to handle the msdtc security settings in detail.


Test it, Test IT !


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me


  Post Attachments 
Configure Clustered msdtc.ps1.txt (22 views, 10.95 KB)
Post #1270373
Posted Monday, March 26, 2012 4:11 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:34 AM
Points: 2,859, Visits: 3,187
As already said, read the links in the posts. Also check this link http://blogs.technet.com/b/askcore/archive/2009/02/18/how-to-configure-multiple-instances-of-distributed-transaction-coordinator-dtc-on-a-windows-server-failover-cluster-2008.aspx

SQL FineBuild can take a bare metal Windows cluster and install a SQL cluster. It takes care of building the MSDTC clusters, so you have one MSDTC cluster per SQL node.

It will also deal with common MSDTC problems, such as creating a new CID for the local MSDTC and enabling network access for MSDTC.

If you are new to FineBuild, I advise getting a sandbox environment and doing a FineBuild Workstation install to see how the product works before starting on your cluster. It is also good to build your first cluster also using virtual machines, as you are unlikely to get the configuration you need for Production the first time you do a cluster install.

There is a lot to learn if you are going to use a SQL Cluster - plan to spend some time with a virtual cluster trying to break and repair it so that you know what to do when the inevitable problems happen in Production.


Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 28 July 2014: now over 30,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #1272519
Posted Wednesday, March 28, 2012 10:57 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 9:04 AM
Points: 59, Visits: 561
Perry Whittle (3/21/2012)
Calvo's link is fairly detailed, you may also see my article at this link, review the top section for installing the Windows roles and services which include Windows DTC configuration then scroll down to the section "Create the Clustered MSDTC Resource" to see how to create the clustered DTC application\service


This is very useful information.
I would suggest reading the whole series

Thank You for this!!!
Post #1274478
Posted Thursday, May 9, 2013 2:30 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 25, 2014 9:36 AM
Points: 965, Visits: 958
ALZDBA (3/21/2012)


To help out a bit more I've added a Powershell (2.0) script that I created at the time I installed my n-th instance.
We provide an MSDTC-resource with each individual SQLInstance on a cluster.


Apologies for digging up a thread from last year. However, I had found an article located here with similar code that is changing the settings in the registry, but if I go into Component Services and look at the properties of the clustered DTC, for whatever reason, Allow Inbound / Allow Outbound won't show checked off. However, the values are set to 1 in the appropriate HKLM\Cluster\Resources\<guid>\MSDTCPRIVATE\MSDTC\Security\ key.

NetworkDtcAccessInbound 1
NetworkDtcAccessOutbound 1

After setting the values with powershell, I'm stopping and starting the resource in the cluster via powershell as well to ensure the changes take effect.

With the registry values set, is that sufficient to assume the setting is in effect or would it be best to confirm that the GUI updates as well to be safe? Any thought as to what I'm missing?

Thanks in advance,
Steve
Post #1451317
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse