Create and Manage Distributed Transactions in SQL Server

A distributed transaction is a single logical unit of work that spans more than one transactional resource—most commonly multiple SQL Server instances (often via linked servers), but sometimes other transactional systems (for example, message queues). The goal is straightforward: either every participant commits, or every participant rolls back, even when network latency and partial failures are possible.

In SQL Server, distributed transactions are typically coordinated using Microsoft Distributed Transaction Coordinator (MS DTC). In modern environments, you should treat distributed transactions as a capability to use when required—not as a default design—because they add operational complexity and can reduce throughput compared to local transactions.

When a transaction crosses server boundaries, SQL Server enlists MS DTC to coordinate commit/rollback across participants. MS DTC must be available and correctly configured on the participating servers (and permitted through firewalls) for the transaction to complete reliably.

SQL Server uses a two-phase commit (2PC) pattern for distributed transactions:

1. Prepare phase: Each participant verifies it can commit, writes the necessary log records, and reports “ready” status to the coordinator.
2. Commit phase: If all participants are ready, the coordinator instructs them to commit. If any participant cannot commit, the coordinator instructs a rollback.

2PC is what delivers the “all-or-nothing” guarantee, but it also means the system must tolerate failures such as network timeouts and “in-doubt” outcomes.

Distributed transactions can start in two ways:

1. Implicit promotion (automatic escalation): You begin a normal local transaction, but SQL Server automatically promotes it to a distributed transaction when you perform work that requires coordination across servers (commonly: a distributed query against a linked server, or a remote stored procedure call that is configured to promote).
2. Explicit distributed transaction: You deliberately start a distributed transaction using BEGIN DISTRIBUTED TRANSACTION.

Syntax

BEGIN DISTRIBUTED { TRAN | TRANSACTION }
    [ transaction_name | @tran_name_variable ];

This example shows a single unit of work that changes data locally and on a remote server. In production, the remote reference is typically a linked server name (for example, RemoteServer) plus a four-part object name.

SET XACT_ABORT ON;
BEGIN TRY
    BEGIN DISTRIBUTED TRANSACTION;

    -- Local change
    UPDATE dbo.Account
      SET Balance = Balance - 50.00
      WHERE AccountId = 1001;

    -- Remote change (via linked server)
    UPDATE RemoteServer.BankDb.dbo.Account
      SET Balance = Balance + 50.00
      WHERE AccountId = 2002;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0
        ROLLBACK TRANSACTION;

    -- Bubble up an actionable error
    THROW;
END CATCH;

• SET XACT_ABORT ON helps ensure runtime errors trigger rollback behavior that is easier to reason about.
• XACT_STATE() is a safer way to decide whether rollback is possible in the CATCH block.
• Keep the transaction body minimal: do not place user interaction, long-running reporting queries, or application “think time” inside it.

Distributed transactions are more failure-prone than local ones because they depend on network connectivity and cross-machine coordination. Typical issues include:

• MS DTC not running on one or more participating servers.
• Network DTC access disabled (Windows Component Services configuration).
• Firewall/RPC restrictions preventing coordinator-to-participant communication.
• Long transaction duration increasing lock contention, blocking, and timeout probability.
• Schema or permission mismatches between local and remote operations.

Use distributed transactions when you truly need atomic commit across servers and cannot redesign the boundary. Otherwise, prefer simpler approaches:

• Consolidate the write boundary (single database/instance) when feasible.
• Use application-level consistency patterns (outbox pattern, idempotent retries, compensating actions) for multi-service workflows.
• Keep transactions short and access objects in a consistent order to reduce deadlock risk.
• Avoid unnecessary promotion: if a workflow can be read-only on the remote side, keep it read-only and commit locally.

The key trade-off is simple: distributed transactions increase correctness guarantees, but they cost performance and operational complexity.

A well-designed distributed transaction in SQL Server is deliberate, short, and observable. Start with clear boundaries, verify DTC readiness, write defensive error handling, and monitor for blocking, timeouts, and “in-doubt” outcomes. With those practices, you can use distributed transactions safely when the business requirement truly demands cross-server atomicity.