SQL Server Distributed Transactions Guide with sqlcmd and DbSchema

A price change has to land in two databases on two instances, and half of it landing is worse than none of it landing. BEGIN DISTRIBUTED TRANSACTION is the statement for that case: it starts a transaction that spans instances, and on SQL Server the Microsoft Distributed Transaction Coordinator, MS DTC, drives it to a single outcome for every server involved.

Who controls the outcome

The instance that runs BEGIN DISTRIBUTED TRANSACTION is the transaction originator, and it controls how the transaction completes. When the session later issues COMMIT TRANSACTION or ROLLBACK TRANSACTION, that controlling instance asks MS DTC to manage the completion across every instance that joined. The statement takes an optional name of up to 32 characters, which is how the transaction shows up in the MS DTC utilities.

Without MS DTC installed on the machine running the Database Engine, the statement produces an error message, so the coordinator is the first thing to check when a script that works on one server fails on another.

How a second instance joins

The main way a remote instance is enlisted is a distributed query against a linked server, executed by a session that is already inside the distributed transaction. A remote stored procedure call does the same thing. The enlistment travels: if the originator calls a procedure on a second server and that procedure runs a distributed query against a third, all of them are in the transaction, with the originator still in charge.

There is no transaction object to hand around. A session cannot pass its distributed transaction to another session, so being the target of a distributed query or a remote procedure call is the only way in.

Promotion is the other route. A distributed query running inside an ordinary local transaction is promoted automatically to a distributed transaction when the target OLE DB data source supports ITransactionLocal, and when it does not, only read-only operations are allowed in that query. For remote procedure calls, the sp_configure remote proc trans option decides whether a local transaction is promoted, and the connection-level SET REMOTE_PROC_TRANSACTIONS overrides the instance default. With that option on, existing applications get MS DTC protection without being rewritten to issue BEGIN DISTRIBUTED TRANSACTION themselves.

A flat transaction across two instances

The examples run on SQL Server 2022. The local instance holds a product catalog, and a linked server named reporting01 holds an archive of sales totals:

CREATE TABLE dbo.Products (
    ProductID int PRIMARY KEY,
    Category  nvarchar(30) NOT NULL,
    Price     decimal(10, 2) NOT NULL
);

INSERT INTO dbo.Products VALUES
    (1, 'Electronics', 100.00),
    (2, 'Apparel', 50.00),
    (3, 'Electronics', 200.00);

CREATE TABLE dbo.Sales (
    SalesID         int PRIMARY KEY,
    ProductCategory nvarchar(30) NOT NULL,
    TotalAmount     decimal(10, 2) NOT NULL
);

INSERT INTO dbo.Sales VALUES
    (1, 'Electronics', 100.00),
    (2, 'Apparel', 50.00),
    (3, 'Electronics', 200.00);

Raising electronics prices by ten percent has to happen in both places or in neither. The second UPDATE uses the four-part name of the remote table, which is the distributed query that pulls reporting01 into the transaction:

BEGIN DISTRIBUTED TRANSACTION;

UPDATE dbo.Products
SET Price = Price * 1.10
WHERE Category = 'Electronics';

UPDATE reporting01.archive.dbo.Sales
SET TotalAmount = TotalAmount * 1.10
WHERE ProductCategory = 'Electronics';

COMMIT TRANSACTION;

After the commit, the catalog reads:

SELECT ProductID, Category, Price FROM dbo.Products ORDER BY ProductID;

And the archive on the other instance matches it row for row:

SELECT SalesID, ProductCategory, TotalAmount FROM reporting01.archive.dbo.Sales ORDER BY SalesID;

The apparel row is untouched on both sides, and had the second UPDATE failed, MS DTC would have rolled the first one back as well.

Savepoints and nesting, and why neither helps here

Inside an ordinary transaction, SAVE TRANSACTION marks a point you can roll back to without abandoning everything since BEGIN. Inside a distributed transaction it is not available: the documentation states that SAVE TRANSACTION is not supported in distributed transactions, whether they were started with BEGIN DISTRIBUTED TRANSACTION or promoted from a local transaction. A script like this one belongs to the local case only:

BEGIN TRANSACTION;

SAVE TRANSACTION sp1;
UPDATE dbo.Products SET Price = Price * 1.10 WHERE Category = 'Electronics';

ROLLBACK TRANSACTION sp1;
COMMIT TRANSACTION;

Nesting offers no way around it. The Database Engine has no independently manageable nested transactions: committing an inner transaction decrements @@TRANCOUNT and does nothing else, and rolling an inner transaction back always rolls back the outer one unless the ROLLBACK names a savepoint, which is the very thing a distributed transaction lacks. Partial undo across instances therefore has to be designed into the statements, by writing compensating updates and deciding on their outcome before the commit.

The permissions, and the one isolation level that refuses

Running the statement takes very little: BEGIN DISTRIBUTED TRANSACTION requires membership in the public role, and so does SAVE TRANSACTION. What each statement inside the transaction touches is governed by the usual permissions on those tables, on both instances.

One restriction is worth writing on the wall: transaction-level snapshot isolation does not support distributed transactions. A session that ran SET TRANSACTION ISOLATION LEVEL SNAPSHOT cannot span instances, so a workload that relies on snapshot isolation needs a different plan for its cross-instance writes.

The whole block in one sqlcmd batch

Open a session on the instance that originates the transaction. Leave -P out and sqlcmd asks for the password as it connects:

sqlcmd -S localhost -d catalog -U sa

Paste the whole block, from BEGIN DISTRIBUTED TRANSACTION to COMMIT TRANSACTION, then type GO on a line of its own. Sending it as one batch matters: the transaction lives in the session, so a batch that ends after the first UPDATE leaves the transaction open and the rows locked on both instances until you commit or roll back.

Running the block as a script in DbSchema

DbSchema runs the block as a script and shows both result sets in one pane. Open Connect to Database, choose SQL Server, enter the host, port, database and credentials, and click Connect. DbSchema reverse-engineers the catalog database and draws Products and its neighbors on a diagram, so the column you are about to multiply by 1.10 is in front of you while you write the statement.

Open the SQL Editor from the Editors menu and paste the whole block. Use Run Script rather than Execute Query: Execute Query runs the statement at the cursor, while Run Script executes the entire editor content and shows every result set together. Leave Ignore Errors off in the Run Script dropdown, because with it off the script stops at the first error instead of walking past a failed UPDATE into the commit. The statements reach the connected SQL Server database and, through the linked server, the second instance; the editor and the diagram are saved in the DbSchema model file on your computer.

Distributed transactions are the least forgiving thing in a two-instance setup, and the cheapest way to keep them honest is to see both schemas at once. Download DbSchema at https://dbschema.com/download.html and open a connection to each instance: connecting, reverse-engineering, the diagrams and the SQL Editor are in the free Community Edition.

Sources

1. BEGIN DISTRIBUTED TRANSACTION (Transact-SQL)
2. SAVE TRANSACTION (Transact-SQL)
3. sqlcmd utility
4. DbSchema SQL Editor