Distributed transactions are transactions that worked across the DATABASES, INSTANCES in the GIVEN session. Snapshot isolation level does not support distributed transactions.

We can explicitly start a distributed transaction USING “BEGIN DISTRIBUTED TRANSACTION <TranName>”

For example, if BEGIN DISTRIBUTED TRANSACTION is issued on ServerA, the session calls a stored procedure on ServerB and another stored procedure on ServerC. The stored procedure on ServerC executes a distributed query against ServerD, and then all four computers are involved in the distributed transaction. The instance of the Database Engine on ServerA is the originating controlling instance for the transaction.

When a distributed query is executed in a local transaction, the transaction is automatically promoted to a distributed transaction if the target OLE DB data source supports ITransactionLocal. If the target OLE DB data source does not support ITransactionLocal, only read-only operations are allowed in the distributed query.

In order to work with these transactions, make SURE below settings are done.

1. MSDTC must be running on all supported instances
2. Choose the option “No authentication required” from MSDTC properties
3. Turn on random options at linked server properties like “RPC”, “RPC Out”, “Data Access” etc.