Linked Servers, OPENQUERY, Distributed Queries, MSDTC, and Coupling Risks

Learning outcomes

A reporting query that joins ServiceHub to a remote inventory database can look like ordinary SQL while hiding a network call, provider boundary, remote authentication decision, certificate validation, different optimizer statistics and potentially a distributed transaction. Linked servers are useful precisely because they make remote data look integrated; that convenience is also their coupling risk. This lesson makes the remote boundary visible before deciding whether synchronous distributed SQL is acceptable.

1. A four-part name is a distributed execution request, not a local table alias

A linked server is server-level metadata that identifies an OLE DB data source and how local security identities map to the remote source. A four-part SQL Server name has the form linked_server.database.schema.object. SQL Server’s local optimizer coordinates the distributed query and asks the OLE DB provider for remote rowsets or operations. By contrast, OPENQUERY sends a literal pass-through query to a configured linked server; the remote provider executes that text and SQL Server consumes the returned rowset.

Neither mechanism eliminates latency. If a plan repeatedly requests small remote rowsets, network round trips can dominate. Pushing selective work to the remote source can help, but forcing every query into OPENQUERY is not a universal fix: the query string is static text, has an 8-KB limit, does not directly accept variables, and can reduce local optimization flexibility.

SELECT server_id, name, product, provider, data_source,       is_data_access_enabled, is_rpc_out_enabled,       is_remote_proc_transaction_promotion_enabledFROM sys.serversWHERE is_linked = 1;SELECT s.name AS linked_server,       ll.local_principal_id,       SUSER_SNAME(ll.local_principal_id) AS local_login,       ll.uses_self_credential,       ll.remote_nameFROM sys.linked_logins AS llJOIN sys.servers AS s ON s.server_id = ll.server_idWHERE s.is_linked = 1;GO

This shows mapping metadata, not passwords. Least-privilege remote identities and constrained delegation/service-account design are part of the security model. A linked server that “works only as sysadmin” is evidence of an authorization design problem, not justification for granting broad privileges.

2. SQL Server 2025 changes the linked-server encryption baseline

SQL Server 2025 uses Microsoft OLE DB Driver 19 behavior for current linked-server configurations. For SQL Server 2025 targets, the Encrypt parameter must be specified. A production design should prefer encrypted connections with a certificate whose chain and hostname can be validated. Using trustservercertificate=yes can make a lab work with a self-signed certificate, but it weakens identity validation and should not become the undocumented production default.

-- OPTIONAL: requires a second reachable SQL Server and server-level permission.-- Replace the illustrative DNS name with your lab server.EXEC master.dbo.sp_addlinkedserver     @server = N'ServiceHubInventoryLab',     @srvproduct = N'',     @provider = N'MSOLEDBSQL',     @datasrc = N'sql2025-inventory.example.test',     @provstr = N'encrypt=mandatory'; -- requires a valid trusted certificateGO-- Map a deliberately scoped identity according to your lab authentication model.-- Never paste a production password into a course script or repository.GO

3. Four-part names versus pass-through execution

-- OPTIONAL: run only if ServiceHubInventoryLab is configured.SELECT TOP (20) i.PartNo, i.OnHandFROM ServiceHubInventoryLab.Inventory.dbo.PartStock AS iWHERE i.OnHand < 5ORDER BY i.OnHand;SELECT PartNo, OnHandFROM OPENQUERY(  ServiceHubInventoryLab,  'SELECT TOP (20) PartNo, OnHand   FROM Inventory.dbo.PartStock   WHERE OnHand < 5   ORDER BY OnHand');GO

The four-part form lets the local optimizer reason about a distributed relational tree and may use remote statistics when the provider and remote permissions allow it. OPENQUERY makes the remote execution boundary explicit and often pushes predicates/joins to the remote system by construction. Measure both under representative row counts and latency; inspect the actual plan and remote operators rather than comparing syntax aesthetics.

Remote cardinality can be surprising. SQL Server can use distribution statistics on supported remote base tables when permissions permit, but remote views do not expose statistics in the same way; Microsoft documents fixed cardinality behavior for linked-server views, which can lead to bad join choices. A low-privilege login can also lack permission to retrieve useful statistics and receive a worse plan. Giving it db_owner just for better estimates is usually the wrong fix—consider a better remote interface, pass-through query, staging/ETL boundary, or a narrowly governed permission design.

USE ServiceHubLab;GOIF OBJECT_ID(N'lab18.RemoteLatencyEvidence', N'U') IS NULLBEGIN  CREATE TABLE lab18.RemoteLatencyEvidence  (    observation_id int IDENTITY PRIMARY KEY,    request_name varchar(40) NOT NULL,    started_at datetime2(3) NOT NULL,    finished_at datetime2(3) NOT NULL,    notes nvarchar(200) NOT NULL  );END;DECLARE @start datetime2(3)=SYSUTCDATETIME();WAITFOR DELAY '00:00:00.250'; -- simulate one remote round-trip for reasoning onlyINSERT lab18.RemoteLatencyEvidence(request_name,started_at,finished_at,notes)VALUES('simulated remote call',@start,SYSUTCDATETIME(),       N'This is not a linked-server benchmark; it demonstrates synchronous latency coupling.');SELECT *, DATEDIFF(millisecond,started_at,finished_at) AS elapsed_msFROM lab18.RemoteLatencyEvidenceORDER BY observation_id DESC;GO

The simulation is intentionally labeled: it does not claim linked-server performance. It demonstrates the architectural point that synchronous request latency enters the caller’s critical path. Real benchmarks must report remote server load, network RTT, row counts, provider version, encryption, query plan and cache state.

4. Remote writes and MSDTC: atomicity can expand the failure domain

A transaction that modifies resources on more than one transaction manager can require promotion to a distributed transaction coordinated by Microsoft Distributed Transaction Coordinator (MSDTC), depending on the operation/provider/server options. That adds network reachability, service configuration, authentication, firewall, recovery and in-doubt transaction concerns. It can be correct when true cross-system atomicity is a hard requirement, but it is a heavy coupling decision.

SELECT name,       is_rpc_out_enabled,       is_remote_proc_transaction_promotion_enabled,       is_data_access_enabledFROM sys.serversWHERE is_linked = 1;GO-- OPTIONAL two-server failure exercise:-- 1. Begin a local transaction.-- 2. Update one local row.-- 3. Perform a deliberately scoped remote write.-- 4. Observe whether the transaction promotes and what happens when the remote path fails.-- 5. Roll back the disposable test and inspect both systems before repeating.

Do not “fix” an MSDTC failure by disabling transaction promotion unless the application’s consistency model explicitly permits independent commits. That changes correctness semantics. Equally, do not assume a distributed transaction solves all integration problems: long network stalls can hold local locks, and independent business retries can still duplicate logical operations.

5. Production judgment and bridge

Linked servers are appropriate for controlled distributed access when synchronous coupling is acceptable and the provider/security/transaction semantics are understood. They are often a poor fit for high-volume cross-system pipelines, independently deployable services or unreliable networks. Prefer staged ETL, CDC, messaging or application APIs when asynchronous ownership and replay are more important than ad-hoc distributed SQL. Lesson 5 combines the chapter’s mechanisms into an integration boundary that states source of truth, ordering, replay, deduplication, backpressure and reconciliation explicitly.

Authoritative references

• Linked Servers (Database Engine)
• Compare query remote execution options
• sp_addlinkedserver
• SQL Server 2025 Database Engine breaking changes
• Create linked server provider / statistics retrieval