Distributed Transactions
As you learned on Chapter 13, "Maintaining Data Consistency: Transactions and Locks," you can consider a group of Transact-SQL statements as part of the same transaction. If the data affected by a transaction is spread across different servers, you need to create a distributed transaction.
To create a distributed transaction, you must start the Microsoft Distributed Transaction Coordinator service (MS-DTC), and the connection must use the SET_XACT_ABORT ON setting.
MS-DTC implements a two-phase commit mechanism to guarantee transaction consistency across different servers. This process can be described as follows:
You connect to a SQL Server instance and start a distributed transaction, using the SET XACT_ABORT ON ...
Get Microsoft® SQL Server™ 2000 Programming by Example now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
