Create and manage Distributed Transactions in SQL-Server
A distributed transaction is simply a transaction distributed across multiple SQL Servers. SQL Server actually does most of the work through the use of the
Distributed Transaction Coordinator (aka) DTC
The DTC is a service that must be running for distributed transactions to function.
The DTC is started through the Service Manager, as shown in the following illustration:
Although distributed transactions are handled through your Transact-SQL statements much the same way as local transactions, they are not handled the same way by the server. The server must consider the possibility of network failure even if there are no locking conflicts or Transact-SQL problems.
To solve the potential network issue, SQL Server employs a commit of the distributed transaction in two separate phases, called a two-phase commit (abbreviated 2PC).
The first phase in 2PC is the prepare phase.
This basically alerts all SQL Servers involved in the distributed transaction that a commit request has been issued. The individual SQL Severs then prepare by writing all buffers to disk and returning a status to the DTC.
The second phase in 2PC is to actually process the commit. All SQL Servers involved in the distributed transaction actually perform a commit and report back a status. If any of the transactions do not commit successfully, the DTC rolls back the transaction.
Implicit or explicit Transactions
Distributed transactions can be either implicit or explicit. An implicit distributed transaction is one that is automatically escalated from a local transaction. This can happen if:
A remote store procedure is called from within the local transaction
A distributed query is issued from within the local transaction
An explicit distributed transaction is started in much the same way as a local transaction by using the BEGIN DISTRIBUTED TRANSACTION Transact-SQL statement. It follows this general syntax:
To commit or rollback a distributed transaction, use the same keywords as those used for a local transaction.
The next lesson shows you how to handle the errors that can occur within your transactions.
Distributed Transaction :A transaction distributed across multiple SQL Servers.