Distributed Transactions

Typically, transaction management controls only the data modifications made within a single SQL Server. However, with the increasing interest and implementation of distributed systems is a need to access and modify distributed data within a single unit of work. What if in the banking example the checking accounts reside on one server, and the savings accounts on another? To move money from one account to another would require updates to two separate servers. How do you modify data on two different servers and still treat it as a single unit of work? You need some way to ensure that the distributed transaction retains the same ACID properties as a local transaction.

To provide this capability, SQL Server ships with the Microsoft Distributed Transaction Coordinator (MS DTC) service, which provides the ability to control and manage the integrity of multiserver transactions. MS DTC uses the industry standard two-phase commit protocol to ensure the consistency of all parts of any distributed transaction passing through MS SQL Server and any referenced Linked Servers.

The process for configuring your servers and writing your SQL code to support distributed transactions is covered in the next chapter.