Linked Servers

                                  

Introduction

Configure a linked server to enable the SQL Server Database Engine to execute commands against OLE DB data sources outside of the instance of SQL Server.

Advantages

·         The ability to access data from outside of SQL Server.

·         The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.

·         The ability to address diverse data sources similarly.

Configuring Linked Server

You can configure a linked server by using SQL Server Management Studio or by using the sp_addlinkedserver (Transact-SQL) statement. 

Linked Server Components

·         An OLE DB provider

·         An OLE DB data source

An OLE DB provider is a DLL that manages and interacts with a specific data source. An OLE DB data source identifies the specific database that can be accessed through OLE DB. 

The Microsoft SQL Server Native Client OLE DB Provider (PROGID: SQLNCLI11) is the official OLE DB provider for SQL Server.

Linked Server Details

How to Add a Linked Server:

To add a linked server using SSMS (SQL Server Management Studio), open the server you want to create a link from in object explorer.

1.   In SSMS, Expand Server Objects -> Linked Servers -> (Right click on the Linked Server Folder and select “New Linked Server”)

Add New Linked Server

2.The “New Linked Server” Dialog appears.  (see below).

Linked Server Settings

1.   For “Server Type” make sure “Other Data Source” is selected.  (The SQL Server option will force you to specify the literal SQL Server Name)

2.   Type in a friendly name that describes your linked server (without spaces). I use AccountingServer.

3.   Provider – Select “Microsoft OLE DB Provider for SQL Server”

4.   Product Name – type: SQLSERVER (with no spaces)

5.   Datasource – type the actual server name, and instance name using this convention: SERVERNAMEINSTANCENAME

6.   ProviderString – Blank

7.   Catalog – Optional (If entered use the default database you will be using)

8.   Prior to exiting, continue to the next section (defining security)

Define the Linked Server Security

1. Within the same Dialog on the left menu under “Select a Page”, select Security

2. Enter the security option of your choice.

3.      Click OK, and the new linked server is created