Is MSDTC required for SQL Server 2012 Fail Over Cluster?

Question

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?

Answer 1

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.

Answer 2

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:

See this MSDN blog for more information.

Answer 3

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.