Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Failover Clustering and DTC


Failover Clustering and DTC

Author
Message
BrerSQL
BrerSQL
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 575
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
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1380 Visits: 3965
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
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8825 Visits: 16561
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
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6978 Visits: 8839
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 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 (29 views, 10.00 KB)
EdVassie
EdVassie
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3154 Visits: 3820
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. 1 Dec 2016: now over 39,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
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 575
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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1096 Visits: 1109
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