Failover Clustering and DTC

  • 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.

  • 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.[/url] Get your answers faster.

  • 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" 😉

  • 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

  • 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

  • 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!!!

  • 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