How to Install a Clustered SQL Server 2012 Instance – Part 4

Jan 10, 2014 / By Murilo Miranda

Tags: , ,

We have reached the last article of this series. To close this series out, we will talk about Distributed Transaction Coordinator, or simply DTC. I’ll try to do some simple explanation. After that, I’ll demonstrate how to prepare the DTC for a clustered instance.

What is DTC (MS DTC)?
The MS DTC is a OS level service, which comes automatically installed and running under the Network Service account. Its role is to ensure that a distributed transaction is consistent, even with failures.
Those transactions might be initiated when a transaction is dealing with data on multiple computers via network or when the transaction is dealing with multiple processes in a single computer.

All the participants of a distributed transaction works in sync with the other participants (computers) involved in a transaction, looking for the right moment to commit or abort its work. For this reason, we need to make sure that the computers can reach each other.

Do I need to configure MS DTC on my environment?
The answer for this question is the standard for almost everything involved with SQL Server; It depends. You need to understand whether or not you will perform distributed transactions. If you have more than one instance in the same computer (without aditional componentes installed), you won’t need the DTC. On the other hand, if you have a two nodes cluster with two clustered instances communicating with each other, you will need the DTC – the instances could be in different nodes. Another possible scenario is when you have the database engine and SSIS installed, in this case you will need to configure the DTC.

For more information, check this link: http://msdn.microsoft.com/en-us/library/ms189910#MSDTC

How to create a clustered MS DTC?

Since Windows 2008, we are allowed to have more than one instance of MS DTC in a server/cluster. So, for clustered SQL Server installations is a best practice to have a Role exclusively for the DTC and a dedicated DTC for each SQL Server Role.

As documented per Microsoft, the SQL Server follow this path to choose the MS DTC instance to use:

  • Use MS DTC installed to the local group, else
    • Use the mapped instance of MS DTC, else
      • Use the cluster’s default instance of MS DTC, else
        • Use the local machine’s installed instance of MS DTC

To configure a DTC in cluster, we will need a disk and a hostname.

To configure a Role exclusively for the DTC, follow the steps:

  1. Right-click on Roles and pick the “Configure Role” option.
    Screen Shot 2014-01-02 at 14.18.43
  2. A new window will open. Click “next”.Screen Shot 2014-01-02 at 14.18.52
  3. Choose the option “Distributed Transaction Coordinator (DTC)” from the list. Click  “Next”.Screen Shot 2014-01-02 at 14.19.11
  4. Fill the hostname in the “Name” field and the IP in the “Network” section. Click “Next”.Screen Shot 2014-01-02 at 14.20.33
  5. Pick up the disk to be used. Click “Next”.Screen Shot 2014-01-02 at 15.14.42
  6. Review the configurations and click “Next”.Screen Shot 2014-01-02 at 15.14.57
  7. The installation will run and in the last step you will see a report. Click “Finish”.Screen Shot 2014-01-02 at 15.15.11
  8. Now you will be able to see a new Role created in the cluster, with all the indicated resources.
Screen Shot 2014-01-02 at 15.16.51

To add a DTC resource into the SQL Server Role, follow the steps:

  1. Right-click the Role, go to “Add Resource”->”More Resources” -> “Distributed Transaction Coordinator”.Screen Shot 2014-01-02 at 15.30.50
  2. The resource will be created in the selected Role, now we need to configure it. Right-click the “New Distributed Transaction Coordinator” and click on “Properties”.Screen Shot 2014-01-02 at 15.31.20
  3. As referred early on this article, the DTC needs a hostname and a disk to work. On dependencies you can pick up those items as shown, and click “ok”.Screen Shot 2014-01-02 at 15.32.44
  4. Now, let’s bring it online.Screen Shot 2014-01-02 at 15.32.55

How to configure the network for distributed transactions?

Note: On clustered environments,you just need to perform the following steps one time.

  1. On “Server Manager” go to “Tools”->”Component Services” or run the command “dcomcnfg”.Screen Shot 2014-01-02 at 15.33.55
  2. Expand the tree, right-click the desired DTC and choose “Properties”.Screen Shot 2014-01-03 at 11.46.28
  3. Go to the “Security” tab and check “Network DTC Acess” as well as “Allow Inbound” and “Allow Outbound”, as shown bellow. Click Ok.Screen Shot 2014-01-03 at 11.49.02
  • Let’s briefly describe the some of the options on this window:
    • Network DTC Access“: Enable/Disable the network access.
    • Allow inbound“:  Permit a distributed transaction originated from another computer to run on the current computer.
    • Allow outbound“:  Permit a distributed transaction initiated in the current computer to run on a remote computer.
    • Enable XA transactions” and “Enable SNA LU 6.2 Transactions“: Enables/Disable those particular specifications for distributed transactions.

Troubleshooting DTC

There’s a tool called DTC Ping which can help us to verify if the DTC is working correctly on all the computers that should be involved in a transaction.

You can download this tool here: http://www.microsoft.com/en-us/download/details.aspx?id=2868

I recommend the reading of this article, to learn hos to use this tool, as well as troubleshoot the possible errors: Troubleshooting MSDTC issues with the DTCPing tool.

Another great tool is the DTC Tester. You can simulate a distributed transaction on SQL Server:

Screen Shot 2014-01-03 at 14.15.25

To download and get more info about this tool, check this link: http://support.microsoft.com/kb/293799 .

The End

This way we finish this series about how to install a clustered instance. We still have too many details to cover and I will try to create separated articles with best practices, configuration alternatives, etc.

I hope you enjoyed this series and as always, if you have any doubts, contact me! Thank you for reading! If you want to check the other parts of this series, here are the links:

 


Pythian is a global leader in data consulting and managed services. We specialize in optimizing and managing mission-critical data systems, combining the world’s leading data experts with advanced, secure service delivery. Learn more about Pythian’s Microsoft SQL Server expertise or check out some more SQL Server-related blog posts

21 Responses to “How to Install a Clustered SQL Server 2012 Instance – Part 4”

  • Lia says:

    The links for the previous parts do not work

  • dion says:

    wow, good tutorial, very helpfull

  • This is very helpful tutorial. very well explained.

  • wahid khan says:

    Thanks for sharing……..Very good tutorial, Very well explained.

  • oyunaa khish says:

    This is very good tutorial. well explained. Please insert 3 node failover cluster

    • Murilo Miranda says:

      Hi! For a 2+ nodes, you just need to replicate the process of adding the second node. Is the same.

  • Shann says:

    Hi Murilo,

    Your steps are easy to follow and clear. May I asked what is the diff between configure a Role exclusively for the DTC and a dedicated for each SQL? How does they work together?

    • Murilo Miranda says:

      Doing the referred configuration you will have a dedicated DTC instance for the SQL Server.
      If you have more than one DTC instance configured, it will behave normally, as there’s an hierarchy to call the proper dtc instance:

      SQL Server will…
      …use MS DTC installed to the local group if available, else
      Use the mapped instance of MS DTC if available, else
      Use the cluster’s default instance of MS DTC if available, else
      Use the local machine’s installed instance of MS DTC

  • shruthi says:

    Thank you.My first cluster is built succesfully. :)

  • Raja says:

    basic question excuse , im trying to understand the cluster installation

    While configuring the 2nd node do we need to log in to the 2nd node and follow the steps … like setup etc …?

    is there any step we need to do here for active active / acive passive….?

    i mean if i wanted to have a setup active active / active passive where i need to configure…?

    • Murilo Miranda says:

      Yes, you meed to connect to all the nodes and run the setup in order to add a node to the clustered instance.

      Regarding active-active cluster, in my opinion this is a wring designation, as a clustered instance is only active in one node per time.

      When people say active-active, they are usually talking about a two nodes cluster, with two clustered instances installed and each one active in a different node than the other one.

  • elamurugu@gmail.com says:

    Hi Murilo Miranda,

    It was an excellent article. Thanks for sharing the knowledge. I have a question for you.
    SQL Server Data tools is can be installed on clustered nodes ? its comes under Shared tools ?

  • Chyke McFarlane says:

    Very well written article. Easy to follow and easy to replicate. Thank you very much.

  • Kangah says:

    Very good articles. But although I could connect to the instance ( SQL01\DB)through odbc datasource, I not could do same fron any client computers. Is there any extra steps required to connect the clients to aa FCI?

  • Kangah says:

    Very good articles. But although I could connect to the instance ( SQL01\DB)through odbc datasource from the cluster nodes, I could not do same fron any client computers. Is there any extra steps required to connect the clients to aa FCI?

  • Gaurav Shrivastava says:

    This is very helpful for beginners, keep posting !!!!

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>