One transaction changes data on two SQL Server machines. MSDTC coordinates the decision to commit or roll back across those participants. The linked-server connection alone doesn't provide that distributed transaction coordination.
Separate Linked-Server Connectivity From MSDTC Coordination
A linked server can read or modify a remote object when its provider and permissions support the request. A transaction involving local and remote writes needs an additional coordination path. The Distributed Transaction Coordinator service, MSDTC, manages the distributed outcome.
SQL Server and the remote participant must enlist and communicate. Windows service, authentication, and network configuration support that coordination.
I test connectivity and distributed coordination separately. A successful remote SELECT doesn't prove that a distributed write can begin. The example requires two SQL Server hosts, a configured linked server, suitable provider support, and an approved MSDTC setup.
It cannot be fully rehearsed with one unconfigured local instance. State those prerequisites plainly before presenting the transaction script as something ready for an isolated query window.
Build Matching Test Tables on Both Servers
Use dedicated test databases on each participant. Create the same simple balance table and harmless input separately on each server. Create a test database named DtcLab on each server first.
Don't point this rehearsal at production balances. The transaction's local and remote target identities need to be unmistakable. A four-part remote name includes the linked-server name, database, schema, and table in that order.
The setup block below runs locally on each participating server's test database. It creates ordinary sample rows. The later transaction uses REMOTE_SQL as the already configured linked-server placeholder. Confirm that mapping before execution.
A familiar alias can lead to the wrong machine. Read server identity and destination configuration during preflight. Don't rely on the alias typed into an old script.
USE DtcLab;
GO
CREATE TABLE dbo.DtcBalanceDemo
(AccountId int PRIMARY KEY,Amount decimal(19,4) NOT NULL);
INSERT dbo.DtcBalanceDemo VALUES (1,100);Rehearse an Explicit Distributed Transaction Under MSDTC
BEGIN DISTRIBUTED TRANSACTION asks for distributed coordination directly. The sample updates a local row and a remote row, then rolls back deliberately. Check the affected count for each write and preserve errors.
SET XACT_ABORT and TRY/CATCH support controlled error handling. They don't configure the coordinator or guarantee that an unavailable participant suddenly becomes reachable. Prerequisites must be satisfied before this test can establish the intended behavior.
The rollback is the rehearsal's planned outcome, so verify both tables afterward through authorized connections. A failure to begin tells you something different from a failure after remote work started. Keep the error number and stage with the diagnostic record.
Don't retry ambiguous business writes without understanding the distributed state. A repeated request needs a defined idempotency rule beyond transaction syntax.
SET XACT_ABORT ON;
BEGIN TRY
BEGIN DISTRIBUTED TRANSACTION;
UPDATE dbo.DtcBalanceDemo SET Amount = Amount - 10 WHERE AccountId = 1;
IF @@ROWCOUNT <> 1 THROW 50011,'Local test row missing.',1;
UPDATE [REMOTE_SQL].[DtcLab].[dbo].[DtcBalanceDemo]
SET Amount = Amount + 10 WHERE AccountId = 1;
IF @@ROWCOUNT <> 1 THROW 50012,'Remote test row missing.',1;
ROLLBACK;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0 ROLLBACK;
THROW;
END CATCH;
Read the Remote Procedure Promotion Setting
Remote proc transaction promotion controls the linked-server RPC path. It decides whether that call promotes a local transaction to distributed coordination. It applies to that RPC path.
It isn't a universal switch disabling distributed coordination for every remote statement. Explicit distributed transactions and distributed queries need their own interpretation. Read the option before comparing two applications that use different remote-access forms.
I inspect the setting rather than toggling it as a first response to an error. Setting it false changes the atomicity relationship of the remote call and local transaction. The remote work can commit independently.
That can be intentional for a designed workflow, but it isn't a repair for a transaction requiring one shared outcome. The read-only query below exposes the configured behavior.
SELECT name,is_rpc_out_enabled,is_remote_proc_transaction_promotion_enabled
FROM sys.servers WHERE name = N'REMOTE_SQL';
-- A reviewed RPC-only design can change this separately:
-- EXEC sys.sp_serveroption N'REMOTE_SQL',N'remote proc transaction promotion',N'false';Diagnose MSDTC Configuration Errors Precisely
Error 8501 points to an unavailable coordinator for the transaction request. Error 7391 reports that the provider cannot begin a distributed transaction. Read the complete messages and Windows event evidence.
Service state, network DTC access, authentication compatibility, firewall rules, name resolution, and provider support all deserve checks. A provider error number alone doesn't identify which host configuration needs attention.
Follow the supported setup for the actual Windows deployment and approved network policy. Don't weaken authentication or open broad firewall access to make a test pass. The requirement is working supported coordination between the participants.
Record what was checked on each host. A local service marked running doesn't establish the remote host's configuration or the return communication needed by the coordination process.
Consider a Workflow Without Distributed Writes
A single database transaction is simpler when the data can share one authoritative database boundary. When systems must remain separate, a durable work item and an idempotent remote operation can support asynchronous coordination. That design accepts a period of partial progress.
It needs retries, reconciliation, and clear state. It isn't the same consistency promise as one distributed commit and should be described accurately.
Which business rule truly requires simultaneous atomic changes? If eventual completion is acceptable, keep local intent durable and process remote work with a unique request identity. If atomicity is required, operate the coordinator and test its failure paths.
Avoid pretending that turning off promotion supplies both simplicity and the original guarantee. The coordinator is useful precisely because the shared decision has a real cost.
Verify Recovery and Ownership Together
Test success, rollback, a remote failure, and an interruption in an isolated environment. Coordinate with the Windows and application owners so each component has a support path. Preserve participant identities and diagnostic evidence.
The business needs a way to distinguish completed work from work needing reconciliation. A distributed transaction is an operational feature, not a command you can detach from the infrastructure keeping its decision durable.
Use MSDTC when the transaction really spans supported participants and requires one outcome. Keep RPC promotion rules explicit and diagnose errors by stage. Choose a simpler workflow when its consistency contract fits the business.
Two machines can cooperate, but they need a coordinator or an agreed handoff protocol. A linked-server alias doesn't volunteer to be either one merely because the query compiled.
Related reading on this blog: Linked Server Error: Msg 3910: Transaction Context In Use By Another Session and Linked Servers and What Goes Wrong With Them.
A distributed transaction is not remote connectivity, it is a coordinated decision across participants.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
