Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 4 down vote accepted

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.

share|improve this answer
2  
Actually, you only need to install the DTC if you plan on doing distributed transactions that encompass a linked server or use OPENQUERY – Max Vernon Mar 4 at 18:05

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.

share|improve this answer

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.

share|improve this answer
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 at 2:28
1  
Thank you jcolebrand. i will update my response. – Ramesh K Mar 4 at 15:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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