6

I am making a 2 node SQL Server 2012 Failover Cluster; do I need to install the MSDTC component?

If yes, can both be installed on single shared disk?

3 Answers 3

4

MSDTC is not required for a SQL Server 2012 fail over cluster. However, if you plan to use Linked Servers, then you will need to create a clustered MSDTC resource. The good news is that can be setup after the cluster is already built and after SQL Server has been installed.

1
  • 2
    Actually, you only need to install the DTC if you plan on doing distributed transactions that encompass a linked server or use OPENQUERY
    – Hannah Vernon
    Mar 4, 2016 at 18:05
4

Microsoft Distributed Transaction Coordinator (MSDTC) provides a mechanism for ensuring atomicity and consistency across multiple data sources. It is required if you plan on performing explicitly transacted operations across multiple data sources, or if you are installing SQL Server 2005.

This applies equally to both clustered and non-clustered SQL Server installations.

If you don't plan on using distributed transactions, you don't need the MSDTC installed.

The following shows an example of what a distributed transaction might look like:

SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRANSACTION
    SELECT *
    FROM LinkedServerName.master.sys.sysobjects so;
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 
    BEGIN
        ROLLBACK TRANSACTION
    END
END CATCH

If you do plan on running T-SQL like this on a clustered instance, you'll need to install the MSDTC onto a separate LUN that is configured as part of the same cluster resource group as the SQL Server instance itself. Do not install it onto a disk used by SQL Server since those disks may go away during a failover before the MSDTC service is stopped.

You'll need to configure the security settings for the Distributed Transaction Coordinator to allow it to talk on the network, using the dcomcnfg utility:

enter image description here

See this MSDN blog for more information.

-1

Microsoft purposefully makes MSDTC an optional installation.

SQL 2005 does require MSDTC for setup, since it uses a transactions to control setup on multiple nodes. However, SQL Server 2008/2008R2/2012 and SQL 2014 setup does NOT require MSDTC to install SQL.

One would need MSDTC "only" if they are planning to use distributed transactions. For SQL Server in simple plain vanilla usage (most SQL Server databases I have seen) don't bother with the MSDTC.

See http://blogs.msdn.com/b/alwaysonpro/archive/2014/01/15/msdtc-recommendations-on-sql-failover-cluster.aspx for further details.

1
  • 1
    Hi Rakesh, welcome to the site! Could you add a little more bulk to your answer and maybe quote the relevant parts of the article you link so that people understand what your answer is supposed to mean when the source link disappears?
    – jcolebrand
    Feb 6, 2016 at 2:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.