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?
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 only takes a minute to sign up.Sign up to join this community
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.
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.