Distributed Transactions

With the increasing proliferation of distributed systems is a need to access and modify data that is often in separate physical locations and in varying types of datasources. In addition, it is extremely important that these transactions be one logical transaction (one Unit of Work, or UOW). You need a way to ensure that the distributed transaction operates in the same way that a local transaction does, and that it adheres to the same ACID properties of a local transaction, across multiple servers.

Microsoft has implemented its distributed transaction processing capabilities based on the industry standard two-phase commit protocol. It utilizes the Distributed Transaction Coordinator service (MS DTC) as the controller of this capability. This ensures the consistency of all parts of any distributed transaction passing through MS SQL Server and any referenced linked servers. The variation on the ACID test is that all sites (servers) depend on one another for completion of an update, and they must give up the site autonomy part of ACID.

After you decide to do distributed transaction processing, you must know and understand the MS DTC architecture thoroughly.