MSDTC in SQL Server 2016 in Failover Cluster

Question

I've build on a cluster as below
OS : windows 2012,
SQL : SQL Server 2016

My question are :

1. Do i need MSDTC? Someone's blogs are said to be unnecessary and some are said to be necessary when using linked servers. Of course, our DB is using a Linked Server.
2. If i need MSDTC, is my configuration correct? (I did not configuration component services, I haven't made any other settings.)

Thank you

Answer 1

Hi @hotmail.com bangu00 ,

Welcome to Microsoft Q&A!
You may need MSDTC if you want to use one of belows.

1. Linked Servers
2. OPENROWSET
3. OPENQUERY
4. OPENDATASOURCE
5. RPC (Remote Procedure Calls)

Please refer to this article about configuration: https://dbtut.com/index.php/2018/05/25/how-to-configure-msdtc-for-sql-server/

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

to be necessary when using linked servers.

MSDTC = MS Distributed Transaction Controller; yes, it's mandatory if you run distributed transaction.

is my configuration correct?

How to say without knowing the configuration; not mentioned in your post? On the other hand there is not much to setup.

Answer 3

Dear OlafHelper and SeeyaXi-msft

First of all, thank you for reply.

My SQL SERVER Failover cluster is configured as Active-Standby.
In my case, SQL Server is always running on only one node, so I don't think MSDTC is needed.
Of course, my DB has a Linked server set up so that I can connect to other DB.

If my thinking is wrong and I am using a Linked server for my DB, as a result I have to configure MSDTC, my current setting is I simply created the SQL Server role and the MSDTC role in the cluster role. No additional settings have been made.

In this state, can I open the firewall after setting the security of the component service on each node by referring to the blog that SeeyaXi-msft informed me? Is this the end?

Thank you.

Answer 4

Please see "What are the different possible MSDTC Configurations and the Benefits of each?" (the link in the table of contents does not work) :

https://techcommunity.microsoft.com/t5/sql-server-support-blog/msdtc-recommendations-on-sql-failover-cluster/ba-p/318037

Answer 5

Thank you for the answered.
This is help for me.