March 20, 2012 at 9:05 am
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.
March 20, 2012 at 10:31 am
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.
March 21, 2012 at 11:12 am
Calvo's link is fairly detailed, you may also see my article at this link[/url], 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" 😉
March 21, 2012 at 11:22 am
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
Learn to play, play to learn !
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[/url]
- 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
March 26, 2012 at 4:11 am
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: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
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
March 28, 2012 at 10:57 am
Perry Whittle (3/21/2012)
Calvo's link is fairly detailed, you may also see my article at this link[/url], 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!!!
May 9, 2013 at 2:30 pm
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply