Oracle Technical Track

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

Murilo Miranda
by Murilo Miranda
9 min read
Jan 10, 2014

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: https://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: https://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: https://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
Previous story
← Disabling Triggers in Oracle 11.2.0.4
Next story
Script to Collect Database Information Quickly →
Data classification with Microsoft SQL Server and Azure SQL DB (GDPR, PII, HIPPA...etc)
Oracle

Data classification with Microsoft SQL Server and Azure SQL DB (GDPR, PII, HIPPA...etc)

May 29, 2018 5 min read
Benchmarking Google Cloud SQL instances
Oracle

Benchmarking Google Cloud SQL instances

Dec 10, 2015 3 min read
Windows containers: installing SQL server
Microsoft Sql Server

Windows containers: installing SQL server

Sep 21, 2015 8 min read

Get Email Notifications

No Comments Yet

Let us know what you think

Follow us
Love Your Data
Pythian Logo

© 2024 All rights reserved.