Post reply

sql server 2012 on win 2012 cluster issue

  • December 31, 2014 at 7:44 am

    #313325

    Hi all we are setting up a 2 node sql failover on windows 2012.

    We created a dtc role and a clustered sql instance. We put in the dependencies and also made changes in the dcomcnfg component services for the dtc. However when we move the sql service to the other node it fails as the dtc role does not move to the other node.

    Any suggestions ..

    Thanks in advance

    Ravi

  • December 31, 2014 at 12:15 pm

    #1767260

    put the clustered DTC resources into the SQL server instances cluster role

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • January 1, 2015 at 8:14 pm

    #1767335

    Hi

    Thanks for the reply.

    For the DTC I had followed the following steps:

    1. In the Failover Cluster Manger I right click on Role and add the Distributor Role.

    2. I create the SQL server Instance

    3. Then in cluster Resources tab I Right Click on the SQL Service and Add the Distributor to the Instance.

    What I see happening here is when I move the SQL Service to the Second Node it stops the DTC but the DTC restarts on the same node where are the SQL Service is trying to start up on the NODE 2. Which causes the cluster to be failed state. But Once I move the Role to manually to NODE2 the service starts up.

    Am I doing something wrong here ?

  • January 2, 2015 at 3:24 am

    #1767356

    create a role for your clustered sql server instance and into the role put the required shared disks for the sql instance and a shared disk for the DTC.

    Right click the new role and add a DTC resource. You'll need to supply a unique networkname and IP address. Once you've done this run the sql server installer and install the clustered instance, pointing the installer to the pre created role and disks.

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • January 2, 2015 at 4:39 am

    #1767369

    Getting MSDTC to work correctly on a cluster is complex, and it is hard to find all the information in one place.

    * Your local MSDTC must have a CID on each node that is unique in your network - have you done this

    * MSDTC Network Access must be configured - it looks like you have done this

    * MSDTC must be clustered, either in its own cluster group or as part of the SQL group - it looks like you have done this

    * A dedicated static port must be assigned for each MSDTC cluster you have - have you done this

    * Firewall rules must be enabled for MSDTC if you are using a Firewall - have you done this

    Google 'MSDTC Cluster ServerTcpPort' for details of MSDTC port requirements

    SQL FineBuild will do all this work for you as part of an automated SQL Server install. For details on how to perform the steps manually start with the following page and work forwards https://sqlserverfinebuild.codeplex.com/wikipage?title=Setup%20MSDTC%20CID

    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

  • January 4, 2015 at 8:26 pm

    #1767655

    Hi

    Thanks for the detailed reply. I need to Configure the firewall ports and the Unique CID for the DTC in my cluster. I will discuss the with my team and test the same. Just to update you ours is a 2 node cluster which will house 2 different instances on each node. I guess this does make it important to check if I have a Unique CID for my DTC's.

    I will update you again when I am able to test the scenarios.

    Thanks & regards

  • January 5, 2015 at 7:41 am

    #1767759

    Here is a great post about how to configure the firewall part.

    http://www.lewisroberts.com/2009/08/16/msdtc-through-a-firewall-to-an-sql-cluster-with-rpc/

    But the idea is that every cluster after Windows 2003, you configure the MSDTC resource inside each SQL Server Role and make SQL Server dependent on the DTC resource. Make sure that it is first in the dependency chain or you will have interesting results for your instance of SQL Server.


    Ben Miller
    Microsoft Certified Master: SQL Server, SQL MVP
    @DBAduck - http://dbaduck.com

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply