For this question lets assume there are two servers, ServerA and ServerB, and there is a linked server setup on ServerA to ServerB. Let’s also assume I have
REMOTE_PROC_TRANSACTIONS set to
ON for both servers.
According to Microsoft’s BOL for
The local transaction started by the BEGIN TRANSACTION statement is escalated to a distributed transaction if the following actions are performed before the statement is committed or rolled back:
An INSERT, DELETE, or UPDATE statement that references a remote table on a linked server is executed. The INSERT, UPDATE, or DELETE
statement fails if the OLE DB provider used to access the linked
server does not support the ITransactionJoin interface.
A call is made to a remote stored procedure when the REMOTE_PROC_TRANSACTIONS option is set to ON.
Given the above, are there any cases where it makes sense or is mandatory to explicitly use
BEGIN DISTRIBUTED TRANSACTION?
Specifically, I’m planning to call a stored procedure (that executes DML queries) on ServerB via the linked server from ServerA with the same transaction wrapped around some DML statements executed on ServerA as well. My goal is to achieve transactional consistency for both sets of DML queries happening between the two servers.