SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Failover Clustering and DTC


Failover Clustering and DTC

Author
Message
BrerSQL
BrerSQL
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 578
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.
calvo
calvo
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2068 Visits: 4016
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.
Perry Whittle
Perry Whittle
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26713 Visits: 17338
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" ;-)
ALZDBA
ALZDBA
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15703 Visits: 8967
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


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- 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 :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Attachments
Configure Clustered msdtc.ps1.txt (70 views, 10.00 KB)
EdVassie
EdVassie
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7357 Visits: 3875
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 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist. - Archbishop Hélder Câmara
BrerSQL
BrerSQL
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 578
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!!!
S. Kusen
S. Kusen
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1828 Visits: 1117
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search