3.4.3 MxDB for SQL Server Installation and Administration Guide

Appendix B: Host MS DTC on a Remote Node 123

exec sp_serveroption 'linkedsrv','rpc out','true'

5. Invoke a query that requires a valid DTC (remotely hosted in this case)

to succeed. A rowcnt value should be returned from the query:

begin tran

select *

into Northwind.dbo.o1

from linkedsrv.Northwind.dbo.Orders

select @@rowcount as [rowcnt]

commit tran

6. Clean up the test by dropping the linked server:

exec sp_dropserver 'linkedsrv','droplogins'

Consideration for Highly Available Remote

MSDTC

In some situations, it can be useful to make the remote hosted MSDTC

highly available. To do this, install and create a dummy PolyServe Virtual

SQL Server with MSDTC enabled across two or more remote nodes. If a

remote node fails, the Virtual SQL Server will failover onto the next

available remote node and become the hosting MSDTC.

It is also possible to host a remote MSDTC on a MSCS cluster. This

configuration will also provide high availability for remote hosted

MSTDC and is supported on Matrix Server.

Summary

DTC is quite important in a distributed environment. It is the middle man

ensuring that all parties that participated in a transaction agree with the

resulting outcome. The transaction is guaranteed to be either 100%

committed or rolled back.

Hosting DTC remotely on a dedicated node is easy to set up and use.

MxDB for SQL Server works quite well in this scenario and has fewer

restrictions regarding whether the hosting node is part of the same cluster

that is using it.