Technical Reviewer:
Krishnakumar Rukmangathan - Support Escalation Engineer, SQL Server BI Developer team, Microsoft
Troy Moen - Sr. Escalation Engineer
Linked server enable the SQL Server Database Engine to read data from remote data sources and execute commands against remote database servers. This particular blog will discuss how to leverage the new MSOLEDB driver to establish linked server with SQL Server.
The MSOLEDB driver is the latest OLEDB Driver from Microsoft. It supports TLS 1.2 along with connection string attributes like MultiSubnetFailover. The previous Microsoft OLE DB Provider for SQL Server (SQLOLEDB) and SQL Server Native Client OLE DB provider (SQLNCLI) are considered deprecated.
Supportability:
Establishing a Linked Server to SQL Server using the MSOLEDB driver is supported on following version of SQL Server:
SQL 2016 SP2 CU6 or higher
SQL 2017 CU15 or higher
SQL 2019 and newer
Note:
All other versions of SQL Server except those listed above will result in the following error when using the MSOLEDB driver for establishing linked server.
Msg 8522, Level 16, State 3, Line 8
Microsoft Distributed Transaction Coordinator (MS DTC) has stopped this transaction.
By default, SQL Server 2017 and earlier versions use the Microsoft OLEDB Provider for SQL Server when establishing linked server connections. Starting with SQL Server 2019, the MSOLEDB driver will be shipped as part of the installation, and therefore MSOLEDB is the default driver chosen to establish a linked server to SQL Server.
You can download the MSOLEDB Driver here. (version 18.2 was the latest version at the time of writing this blog in Sept 2019). After installing the MSOLEDB x64 driver for 64-bit SQL Servers, you can use the Management Studio Interface or T-SQL to create a new linked server to another instance of SQL Server.
Or using T-SQL:
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'<Linked Server Name>', @srvproduct=N'', @Provider=N'MSOLEDBSQL', @datasrc=N'<Target SQL Server Name'
If you are establishing a linked server to AlwaysOn Availability Group listener and the listener has multiple IP addresses spanning a different subnets(of cluster nodes), you can add MultiSubnetFailover=Yes in the Provider String, as shown below.
Author: Chirag Shah – Premier Field Engineer, Data Platform