Create a distributed transaction

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Important

The SQL Server Native Client (often abbreviated SNAC) has been removed from SQL Server 2022 (16.x) and SQL Server Management Studio 19 (SSMS). The SQL Server Native Client (SQLNCLI or SQLNCLI11) and the legacy Microsoft OLE DB Provider for SQL Server (SQLOLEDB) are not recommended for new application development. Switch to the new Microsoft OLE DB Driver (MSOLEDBSQL) for SQL Server or the latest Microsoft ODBC Driver for SQL Server going forward. For SQLNCLI that ships as a component of SQL Server Database Engine (versions 2012 through 2019), see this Support Lifecycle exception.

A distributed transaction can be created for different Microsoft SQL systems in different ways.

ODBC driver calls the MSDTC for SQL Server on-premises

The Microsoft Distributed Transaction Coordinator (MSDTC) allows applications to extend or distribute a transaction across two or more instances of SQL Server. The distributed transaction works even when the two instances are hosted on separate computers.

MSDTC is installed for Microsoft SQL Server on-premises, but isn't available for Microsoft's Azure SQL Database cloud service.

MSDTC is called by the SQL Server Native Client driver for Open Database Connectivity (ODBC), when your C++ program manages a distributed transaction. The Native Client ODBC driver has a transaction manager that is compliant with the Open Group Distributed Transaction Processing (DTP) XA standard. This compliance is required by MSDTC. Typically, all transaction management commands are sent through this Native Client ODBC driver. The sequence is as follows:

  1. Your C++ Native Client ODBC application starts a transaction by calling SQLSetConnectAttr, with the autocommit mode turned off.

  2. The application updates some data on SQL Server X on computer A.

  3. The application updates some data on SQL Server Y on computer B.

    • If an update on SQL Server Y fails, all the uncommitted updates on both SQL Server instances are rolled back.
  4. Finally, the application ends the transaction by calling SQLEndTran (1), with either the SQL_COMMIT or SQL_ROLLBACK option.

(1) MSDTC can be invoked without ODBC. In such a case, MSDTC becomes the transaction manager, and the application no longer uses SQLEndTran.

Only one distributed transaction

Suppose that your C++ Native Client ODBC application is enlisted in a distributed transaction. Next the application enlists in a second distributed transaction. In this case, the SQL Server Native Client ODBC driver leaves the original distributed transaction, and enlists in the new distributed transaction.

For more information, see DTC Programmer's Reference.

C# alternative for SQL Database in the cloud

MSDTC isn't supported for either Azure SQL Database or Azure Synapse Analytics.

However, a distributed transaction can be created for SQL Database by having your C# program use the .NET class System.Transactions.TransactionScope.

Other programming languages

The following other programming languages might not provide any support for distributed transactions with the SQL Database service:

  • Native C++ that use ODBC drivers
  • Linked server using Transact-SQL
  • JDBC drivers

See also

Performing Transactions (ODBC)