We had implemented SQL Server High Availability on two physical servers over the VPN network. SQL Server 2012, Windows Server 2012 R2. It was noted that, Distributed Transaction was working perfectly with NODE1 and NODE2 switched on and also NODE1 ON and NODE2 OFF. We tested, failover switching off NODE1 and NODE2 one at a time and vice versa. It was experienced that, MSDTC was not working after the failover. It was noted in the below link also, But, in the Failover cluster manager I could spot - SQL Server MSDTC role which is available for addition. Would that role added resolve the issue? Er Diagram Question And Solution Pdf Writer here.

Or is it necessary that we need to roll back to Normal Failover cluster installation with SAN storage for this to take place? Another, features noted was, when both the nodes were switched on NODE2 was available in read-only mode. Is it normal? The issue has been resolved. It is impossible to implement MSDTC services while using SQL Server 2012/2012R2/2014, high availability platforms. You will not able to add, MSDTC Services to your Cluster services role. I reverted the whole system to classical Clustring services - SQLNODE1,SQLNODE2,SAN Drive [we installed Buffallo Tera Byte solution].

Installation was done directly on the SAN Drive. Please chose your platform, SAN Solution and interlocks based on your requirement and future integrations or uses in mind.

Jul 19, 2016. Windows 8.1, Windows Server 2008, Windows Server 2008 R2, Windows Server 2012. How To Configure MSDTC to Support Oracle Transactions.

MSDTC is a separate Windows service which coordinates distributed transactions across SQL Server instances. When deploying SQL Server in a highly available environment like Windows Failover Clustering, there are certain best practices that can make the MSDTC service’s behavior more predictable. What is MSDTC? • MSDTC is an acronym for Microsoft Distributed Transaction Coordinator. The Microsoft Distributed Transaction Coordinator service (MSDTC) tracks all parts of the transactions process, even over multiple resource managers on multiple computers.

This helps ensure that the transaction is committed, if every part of the transaction succeeds, or is rolled back, if any part of the transaction process fails. Do we need MSDTC for SQL 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. • MSDTC is only needed for transactions etc. After installation.

Is MSDTC Supported when using AlwaysON Availability Groups? • When the topic of cross-database and/or DTC transaction support, under an Availability Group, comes up the quick response is NOT SUPPORTED! • More information at Do I Need to Cluster MSDTC after SQL installation? • With Windows 2008 Failover cluster and later you do not need to cluster MSDTC to utilize the functionality of the MSDTC service. This is because MSDTC was re-designed in Windows 2008 and unlike Windows 2003 if Windows Failover Cluster was installed you had to cluster MSDTC. This is no longer the case when using Windows 2008, since by default MSDTC service is running locally, even with Failover Clustering installed. • Currently there is some documentation that states you need to have a MSDTC Resource in your cluster.

I want to start out by emphasizing that the statement 'You must install MSDTC is not 100 percent accurate.' The statement does not take into account the OS and the changes with MSDTC in Windows 2008 and later. Why did I need to Cluster MSDTC in Windows 2003? • You only need to create the MSDTC resource in the Failover Cluster if you are installing on Windows 2003 Failover Cluster. • The reason you needed to create a MSDTC resource was because of the design of MSDTC in Windows 2003. • In 2003 when the MSDTC service starts as local service it checks to see if cluster is installed. If Cluster is installed the MSDTC Service would not allow you to run it outside of the Cluster Server Service.

So it would shut down and to get the service to start or you need to ensure that the MSDTC was started by the cluster service via the Distributed Coordinator Clustered Resource. • This is why you needed to create an MSDTC resource in a Windows 2003 Cluster to use the MSDTC functionality. What is the downside in clustering MSDTC in Windows 2003? • The down side to this functionality is that in Windows 2003 Cluster you can only have one MSDTC resource for your entire cluster which can cause a performance bottle neck. For example: Let's assume you have a 2 (or More Node) Windows 2003 Failover Cluster installation. You have created a MSDTC resource, Network Name, IP Address resource and a Disk resource set as dependent resources all contained in its own group, which is owned by Node 1.

Now in addition you have 1 or more SQL Server installations each in their own group. In this case one of the SQL Failover Cluster Groups is running on Node 2. Current Configuration To use the functionality of the MSDTC Service for an application, in this case SQL that is running on Node 2, the SQL Server must communicate with a MSDTC proxy agent, which reroutes the request to Node 1 where the MSDTC service is running so the MSDTC service on that node can facilitate your request. To appreciate the impact, say there are four nodes with three SQL Server instances. MSDTC is running on Node 1 and the SQL Server instances are divided on all the nodes (Nodes 2, 3 and 4), they must all talk to Node 1 for MSDTC functionality. This can create a bottleneck.

For this reason (and several other reasons) MSDTC was completely re-written in Windows 2008 and above so that by default the MSDTC service is allowed to run locally on each node. In addition it allows for multiple MSDTC resources in the cluster instead of just the one. Again I want to put emphasis on the fact that for Windows Server 2008 and later it is NOT required to cluster MSDTC to utilize the features of the MSDTC service. What is the Simple recommendation for MSDTC with Windows 2008 and later? • In Windows 2008 and later you either Create a clustered instance of the MSDTC resource for EVERY SQL Server instance /Group that requires its functionality or DO NOT CLUSTER MSDTC at all.

What will SQL do if I do not Cluster MSDTC in Windows 2008 and above? • If there is no MSDTC resource in the cluster then SQL Server will use the MSDTC service that is running locally on the node. What if I only create one MSDTC Resource in my cluster?

• If you create only one MSDTC resource then it becomes the cluster default and revert to the Windows 2003 functionality listed above, which is not recommended. How do I change the Cluster Default MSDTC that is used by Applications? • On Windows Server 2008 or later Failover Cluster setup, you can install multiple instances of MSDTC on a single failover cluster. The first instance of MSDTC that is installed will be the cluster default instance of MSDTC. This can be changed via the Component Services Management Console (dcomcnfg). For the purpose of this FAQ I have a Cluster with 2 MSDTC (Microsoft Distributed Transaction Coordinator) • First MSDTC Resource is in a Group MSDTC-ONE • Second MSDTC Resource is in a Group MSDTC-TWO • To modify this, perform the following actions: 1. On the Start menu, click Run, type dcomcnfg and then press ENTER to launch the Component Services Management Console.

Expand Computers, and then right-click My Computer. Click Properties, click the MSDTC tab, and then select the default coordinator for your cluster.

Do I need to Tell SQL Server which MSDTC Instance to use (Clustered or Local)? • SQL Server 2008 and later will take advantage of an instance of MSDTC installed to the SQL Server’s local cluster resource group by automatically using the instance of MSDTC in the SQL Server's Cluster Group.

This is a feature of SQL 2005 and above and does not hold true for other applications. • When using SQL 2005 or other applications they can be mapped to the MSDTC Resource in the same group. • Note: You can Map any application including SQL 2008 and later to any specific instance of MSDTC that is any group on the cluster. • For more information about this, see “How to Mapping an Instance of SQL Server 2008 to an Instance of MSDTC” below.

• The list below shows the order in which SQL Server will choose which instance of MSDTC will be used. • Note: The First Option 'Use MSDTC instance installed to the local group ONLY applies to SQL 2008 and later only. For all other Versions of SQL or Other Applications will start with 'Use the mapped instance of MSDTC' • The order of precedence is as follows: • Note: This is figured out as part of the SQL Service Startup, Changes made to this configuration after SQL is started will require you to re-start SQL. What happens if the MSDTC Resource that was picked at SQL Service startup fails?

• If the MSDTC instance that is being used by a specific SQL Server Group fails, SQL Server does not automatically attempt to use the next available MSDTC Resource such as the default cluster instance or the local machine instance of MSDTC. • You would need to completely remove the failed instance of MSDTC from the SQL Server group to use another instance of MSDTC. Torrent Tangled Ever After Full Movie here.

Likewise, if you create a mapping for SQL Server and the mapped instance of MSDTC fails, your distributed transactions will also fail. If you want SQL Server to use a different instance of MSDTC, you must either add an instance of MSDTC to the local cluster group of the SQL Server or delete the mapping. Where can I find more information on creating an MSDTC Resource in Windows Server 2008 Failover Clustering? • For more information about creating an MSDTC resource for Windows Server 2008 failover clustering, see Checklist: • How do I enable Network Access for MSDTC? • After creating the MSDTC resource(s) for your cluster, you must enable network access for the MSDTC resource. • This is done from the Component Services Management Console.

In the Component Services Management Console, perform the following actions: 1. Expand Component Services, expand Computers, expand My Computer, expand Distributed Transaction Coordinator, and then expand. (Because I have each of the MSDTC Resources Depend on SQL Network Name you will see the SQL NetBIOS name for the MSDTC Name in component services. Example SQLAG1, SQLAG2) 2.

Right-click the instance that you want to configure, and then click properties. Under Security Settings, select the Network DTC Access, Allow Inbound, and Allow Outbound check boxes, and then click OK to complete the configuration.

• These configuration options enable MSDTC to access resources on the network and allow applications to access this instance of MSDTC from remote machines including other cluster nodes. • It is not necessary to perform these actions on each node of the cluster because the changes will propagate to all nodes of the cluster for a clustered instance of MSDTC. How do I map a specific instance of SQL Server to a specific instance of MSDTC? • To map a SQL Server instance to an instance of MSDTC, open an administrative command prompt and use the msdtc.exe command. • The following command is used to create a mapping between an instance of SQL Server and an instance of MSDTC.

• msdtc -tmMappingSet -name -service -ClusterResourceName • • Is an arbitrary name that you choose to identify this mapping. • • Is the service name for your SQL Server instance. If this is the default instance, use MSSQLServer as your service name.

If this is a named instance, use MSSQL$ as your service name. • • Is the resource name for the instance of MSDTC to which you want to map SQL Server. Note: If you create an incorrect mapping, the MSDTC command still succeeds, but your mapping will not work correctly. How do I View existing MSDTC mappings across the entire cluster? • The following command is used to view all MSDTC mappings across the entire cluster.

• msdtc -tmMappingView * How do I delete an existing MSDTC mapping or incorrect mapping? • The following command is used to delete and existing MSDTC mapping. • msdtc -tmMappingClear -name • • Is an arbitrary name that you choose to identify this mapping. How can I test MSDTC functionality? • You can test access by executing BEGIN DISTRIBUTED TRAN from a SQL Server Management Studio query window.

• If it fails to enlist the transaction, you can retrieve the error from the SQL Server error log. • For example: • An error of XACT_E_CONNECTION_DOWN at this point generally indicates that your MSDTC instance is not running, your mapping is incorrect, or you are being blocked by a firewall.

What is the Best Practices for MSDTC Configuration with SQL Server 2008/2008R2/2012? • As we mentioned briefly above you should either create a MSDTC resource for each SQL Instance or do not cluster MSDTC at all.

Why would I create a MSDTC instance for each SQL Server Instance? • Dedicated instances of MSDTC for each SQL Server or application can help to prevent failures of one instance of MSDTC from affecting distributed transactions for other applications. • This configuration can add complexity to your Windows Failover configuration, in addition will have much higher resource requirements for your cluster.

• This is because each MSDTC Clustered instance requires a Physical Disk, IP and Network Name Resource. Note: When you add a MSDTC resource to a SQL Server Group you can use one of the SQL Server Disks and SQL IP and Network Name for the MSDTC resource dependencies. However, when configuring MSDTC for an instance of SQL Server 2008 or later, the specific needs of your environment—in terms of performance, availability, or manageability—will determine the best practice for your MSDTC configuration. What are the different possible MSDTC Configurations and the Benefits of each? MSDTC in Each SQL Server Resource Group • This option provides the best performance. It guarantees that your instance of MSDTC will always run on the same physical node as the SQL Server, which reduces communications overhead.

• It is suitable for the widest array of requirements. • It does not require mappings. SQL Server 2008 automatically uses the local cluster group MSDTC resource by default. • This configuration conserves drive letters for clusters that have a limited number of drive letters available. • You can make use of mount points to keep disk I/O separate from other SQL Server files without using up a drive letter on the cluster. Caution: When you use this configuration, you must determine the correct setting for the 'If restart is unsuccessful' action.

By default a failure will failover all resources in this service or application setting of the MSDTC resource. If the function of MSDTC is critical to your environment, you set MSDTC to restart is unsuccessful and fail over all resources in this service or application or put MSDTC in its own resource group and create a mapping that directs SQL Server to that instance of MSDTC. In most cases, you will not want to set If restart is unsuccessful action, to not fail over all resources in this service or application (Or Affect the Group, depending on the version of your Windows Failover Cluster. The setting enables MSDTC to be restarted on the same node if it fails, but if it cannot be restarted, it will not cause a failover of the entire SQL Server resource group. Note: As mentioned previously, if this local resource is offline or failed, distributed transactions will fail for this instance of SQL Server, and you will need to delete or move the MSDTC resource to make use of another instance of MSDTC on the cluster. Dedicated Group for Each MSDTC Instance • This configuration is recommended only if the primary concern is maximum availability of SQL Server and MSDTC and if the performance of remote MSDTC has been verified to be acceptable for the application and load. • MSDTC requires a dedicated physical disk resource in this configuration.

If the cluster hosts many instances of SQL Server, you may not have enough disk resources to give every instance of SQL Server a dedicated MSDTC in a separate resource group. • Manageability can be a challenge if you have many SQL Server-to-MSDTC mappings.

Note: Since MSDTC is running in a separate group there is no way to Guarantee that the SQL group using this instance MSDTC are going to be hosted on the same node. The performance of distributed transactions might be better when SQL Server and the MSDTC resource to which it is mapped are running on the same physical node. Be sure to consider this factor when testing application performance, and test with MSDTC on remote nodes.

Single Default Cluster MSDTC Instance • Windows Server 2003 failover clusters can only have a single clustered instance of MSDTC. It is recommended that you install this instance to a dedicated resource group. This configuration prevents failures of MSDTC from affecting other applications. • On Windows Server 2008 failover clusters, the first MSDTC Resource becomes the default MSDTC. It is recommended for this configuration that you have the default MSDTC instance installed to a dedicated resource group.

Note: The Cluster default MSDTC instance will provide MSDTC services for all applications that are not specifically mapped to another instance. • SQL Server instances that rarely use MSDTC can use the default MSDTC instance on an ad-hoc basis. Local MSDTC Instance • This option provides no high availability for MSDTC in the event of a failure, because the MSDTC log is stored on a local disk instead of a cluster disk. • Even though no mappings exist for the local MSDTC instance, it is still required to have the local MSDTC instance running.

Note: During application/MSDTC initialization, the local MSDTC instance is used to determine the identity of the correct instance of MSDTC for this application (local, cluster default, or a specifically mapped instance). It is also used for non-cluster-aware applications on this node.

• This configuration is recommended if you chose not to create an MSDTC instance for every SQL Server. Written by: Shon Hauck. Thanks, Many practical informations here. I was always looking for such an article!

Could you ensure me with following statements/questions; • If MSDTC fails in 'MSDTC in Each SQL Server Resource Group' scenario, it can cause the whole group to failover, so SQL and MSDTC will be started on another node. It is also possible to configure it so, that MSDTC after failed restart will stay on the same node (without failover), so SQL will not be able to create distributed transactions. In case both SQL and MSDTC have failed over to another node, no manual reconfiguration is requred for SQL to start using MSDTC on that node. • If MSDTC fails in 'Dedicated Group for Each MSDTC Instance' scenario, only the MSDTC group will fail over to the second node. SQL will automatically (withut manual work) start talking to the MSDTC on another node (where it failed over). This can guide to performance problems. Mappings are required here, because we need to decide which MSDTC shall be used by which SQL instance.

• In 'Single Default Cluster MSDTC Instance' no need for mappings exists, because we have ONLY ONE MSDTC group. If that group fails over, SQL server will start to talk to the new node. No manual reconfiguration is requeried. • In 'Local MSDTC Instance' scenario in case of MSDTC failure, SQL server has trouble, because it cannot start/commit distributed transactions. Manual restart of MSDTC on local node will be needed or manual failover of SQL server to next node will help it. Your acknowledgement or showing me errors in my points will help me to make sure, I have understood the topic. Thanks in advance!

We have a two node SQL 2014 Std WFSC configuration running on W2012R2. The SQL WSFC Role includes an MSMQ resource.

When setting up this configuration I was unable to get DTC working with SQL talking to MSMQ without clustering DTC and ended up clustering DTC in a separate role. It now works. However when SQL and DTC run on different nodes distributed transactions fails if Windows Firewall is enabled. I have configured the Windows Firewall to allow all DTC rules, both incoming and outgoing on both nodes but no go. If I turn of Windows Firewall it works.