What is a Linked Server and How to set it up?

Sometimes an application may need data stored in another database on a different instance of SQL Server. That different instance might be on the same physical machine or might be on another machine altogether. So what do you do in this situation? Your options depend on your data requirements, like how up to date does the data need to be. Also, the network/computer topology might be a factor in what you can and cannot do. To discuss all the different possible options would make this article quite lengthy so let me narrow down the scope a little bit. For the purpose of this article, I will be discussing how a linked server can be used to seamlessly provide an application access to data on a different instance of SQL Server.

 

Basic Linked Sever Architecture

Before I get into how to setup a linked server, let me discuss the basic architecture of a linked server. A linked server is a mechanism that allows a query to be submitted on one server and then have all or part of the query redirected and processed on another SQL Server instance, and eventually have the results set sent back to the original server to be returned to the client. To better show how this works look at the following diagram:

 

In this diagram, a “Client” can connect to either an “Application Server” or directly to SQL Server to submit a query. If the “Client” or application running on the “Application Server” submits a query to “SERVER1” that needs to retrieve data from a database housed on “SERVER2”, then this kind of query is known as a distributed query. Defining a linked server definition for “SERVER2” on “SERVER1” allows for a client or an application to submit these kinds of distributed queries. A distributed query that runs against “SERVER2” from a linked server defined on “SERVER1” would look something like this:

SELECT name "DBs on SERVER2" FROM SERVER2.master.sys.databases

Here I identify the object I want to reference on my linked server by using a four part naming convention. In my example, I wanted to return the names of all the databases on “SERVER2”. Therefore, I used a four part naming which consisted of <linked server>.<database>.<schema>. <object>, or in my case “SERVER2.master.sys.databases”. “SERVER2” is the name of the linked server, which is defined on “SERVER1”.

How to Define a Linked Server

To create or list the available linked servers already defined you look under the “Server Objects” folder within SQL Server Management Studio (SSMS). You can also use “sp_addlinkedserver” system stored procedure to add a linked server, or “sp_helpserver” to list linked servers.

To create linked “SERVER2” in my above example in SSMS, I would first expand the “Server Objects” folder, and then right click on the “Linked Servers” item. This would display the following window:

 

 

Explanation of the Server Options

 

According to  technet

 

 

Collation Compatible
Affects Distributed Query execution against linked servers. If this option is set to true, Microsoft SQL Server assumes that all characters in the linked server are compatible with the local server, with regard to character set and collation sequence (or sort order). This enables SQL Server to send comparisons on character columns to the provider. If this option is not set, SQL Server always evaluates comparisons on character columns locally.

This option should be set only if it is certain that the data source corresponding to the linked server has the same character set and sort order as the local server.

 

Data Access
Enables and disables a linked server for distributed query access.

 

RPC
Enables RPC from the specified server.

 

RPC Out
Enables RPC to the specified server.

 

Use Remote Collation
Determines whether the collation of a remote column or of a local server will be used.

If true, the collation of remote columns is used for SQL Server data sources, and the collation specified in collation name is used for non-SQL Server data sources.

If false, distributed queries will always use the default collation of the local server, while collation name and the collation of remote columns are ignored. The default is false.

 

Collation Name
Specifies the name of the collation used by the remote data source if use remote collation is true and the data source is not a SQL Server data source. The name must be one of the collations supported by SQL Server.

Use this option when accessing an OLE DB data source other than SQL Server, but whose collation matches one of the SQL Server collations.

The linked server must support a single collation to be used for all columns in that server. Do not set this option if the linked server supports multiple collations within a single data source, or if the linked server’s collation cannot be determined to match one of the SQL Server collations.

 

Connection Timeout
Time-out value in seconds for connecting to a linked server.

If 0, use the sp_configure defaultremote query timeout Option value.

 

Query Timeout
Time-out value in seconds for queries against a linked server.

If 0, use the sp_configure default query wait Option value.

 

Enable Promotion of Distributed Transactions
Use this option to protect the actions of a server-to-server procedure through a Microsoft Distributed Transaction Coordinator (MS DTC) transaction. When this option is TRUE, calling a remote stored procedure starts a distributed transaction and enlists the transaction with MS DTC. For more information, see sp_serveroption (Transact-SQL).

 

 

Below is also an option to use TSQL to create the Linked Server.

 

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N’Archive_Server’, @srvproduct=N”, @provider=N’SQLNCLI’, @datasrc=N’servername’
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’Archive_Server’,@useself=N’False’,@locallogin=NULL,@rmtuser=N’Link_Environment_User’,@rmtpassword=’########’

GO

EXEC master.dbo.sp_serveroption @server=N’Archive_Server’, @optname=N’collation compatible’, @optvalue=N’false’
GO

EXEC master.dbo.sp_serveroption @server=N’Archive_Server’, @optname=N’data access’, @optvalue=N’true’
GO

EXEC master.dbo.sp_serveroption @server=N’Archive_Server’, @optname=N’dist’, @optvalue=N’false’
GO

EXEC master.dbo.sp_serveroption @server=N’Archive_Server’, @optname=N’pub’, @optvalue=N’false’
GO

EXEC master.dbo.sp_serveroption @server=N’Archive_Server’, @optname=N’rpc’, @optvalue=N’false’
GO

EXEC master.dbo.sp_serveroption @server=N’Archive_Server’, @optname=N’rpc out’, @optvalue=N’true’
GO

EXEC master.dbo.sp_serveroption @server=N’Archive_Server’, @optname=N’sub’, @optvalue=N’false’
GO

EXEC master.dbo.sp_serveroption @server=N’Archive_Server’, @optname=N’connect timeout’, @optvalue=N’0′
GO

EXEC master.dbo.sp_serveroption @server=N’Archive_Server’, @optname=N’collation name’, @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N’Archive_Server’, @optname=N’lazy schema validation’, @optvalue=N’false’
GO

EXEC master.dbo.sp_serveroption @server=N’Archive_Server’, @optname=N’query timeout’, @optvalue=N’0′
GO

EXEC master.dbo.sp_serveroption @server=N’Archive_Server’, @optname=N’use remote collation’, @optvalue=N’true’
GO

EXEC master.dbo.sp_serveroption @server=N’Archive_Server’, @optname=N’remote proc transaction promotion’, @optvalue=N’true’
GO