Setting up a Linked Server for a Remote SQL Server Instance

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. Note linked
servers can also be used to access other non-SQL Server data sources, but that
notion will be outside the scope of this article. I will only be discussing
options and situations related to using linked servers to access information
stored in a SQL Server database.

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 server>...