Fix SQL Server DTC Errors on Duplicated Hyper-V VMs

Problem Description

We had two VMs as the database servers in our web farm. Both had Windows Server 2008 R2 and SQL Server 2008 R2 installed. We configured one as the Linked Server to the other and used Distributed Transaction in updating query. We did all necessary setting steps but still got errors when using Distributed Transaction. The error information was as follows:

"The operation could not be performed because OLE DB provider "SQLNCLI10" 
for linked server "MyLinkedServer" was unable to begin a distributed transaction." 
"OLE DB provider "SQLNCLI10" for linked server "MyLinkedServer" 
returned message "No transaction is active".  

Root Cause

After analyzing the Event Viewer, we found it was because CID was duplicated since the two VMs were created from the same template.

Solutions

To resolve the CID duplication problem, we did the following steps:

1. Run "msdtc -uninstall" in a command prompt as administrator.
2. Run "msdtc -install" in the same command prompt.
3. Start the Distributed Transaction Coordinator service and set its start-up type to Automatic (Delayed start).
4. Restart all SQL Server services.

Step 3 is required because the previous two steps will stop the service and change its start-up type to Manual. Without Step 3, you will get another error as the below:

"MSDTC on server 'MyServer' is unavailable."    

More for Linked Server

To enable querying a remote Linked Server, don't forget to run the following SQL scripts on the consumer server:

EXEC sp_serveroption @server=@serverName, @optname='rpc', @optvalue='true'
EXEC sp_serveroption @server=@serverName, @optname='rpc out', @optvalue='true'

@serverName should be replaced by the alias of your remote Linked Server. Actually, it is the srvname field of the master..sysservers table.