SQL Server 2014 - Linked Servers

SQL Server provides the option of linking to another server. This is often used to link to other SQL Server databases, but it can also be used to link to a Microsoft Access database. This is made possible via the Linked Servers node.

The Linked Servers node allows you to connect to another database, often/usually running on a different machine, perhaps remotely in a different city/country. This can be useful if you need to perform distributed queries (query a remote database). Setting up a linked server is quite straight forward in SQL Server Management Studio. All you need is details of the remote server, and the database that you need to query.

Creating a Linked Server

To create a linked server:

  1. In the Object Explorer, open Server Objects and navigate to Linked Servers
  2. Right click on Linked Servers and select New Linked Server...:
    Creating a linked server in SQL Server Management Studio - 1
  3. Complete the details for the linked server. In this case, I am linking to SQL Server on a remote server called Parrot that has a database called Paywiz. The linked server name is PAYROLL:
    Creating a linked server in SQL Server Management Studio - 2
  4. Under the Security option, you have the ability to map local users to a user on the remote machine. Here, the local SQL Server user Zoidberg is mapped to the Web_User account on the remote machine. The password provided here must be the password of the Web_User account on the remote machine. We also have a local Windows domain authenticated user mapped to the same user on the remote machine, and set to Impersonate. This Windows domain authenticated user is also on the remote machine, so the local user can simply impersonate the remote user:
    Creating a linked server in SQL Server Management Studio - 3
  5. And the Server Options allows you to make further configurations for the linked server. Here I've changed one setting. I set RPC Out to True. This option enables users to run stored procedures against the linked server. Note that the RPC option enables stored procedures to be called from the remote server to the local machine. In this case we've left that at False:
    Creating a linked server in SQL Server Management Studio - 4
  6. When you're happy with the configuration, click OK to create the linked server.

You should now see your new linked server sitting under the Linked Server node of the Object Explorer.

Screenshot of linked server in Object Explorer

Distributed Queries

Once you have configured your linked server, you will be able to run queries etc against it. When you run a query against a linked server, it is referred to as a distributed query.

When you execute a distributed query against a linked server, you must include a fully qualified, four-part table name for each data source to query. This four-part name should be in the form linked_server_name.catalog.schema.object_name.

Here's an example:

This example is based on the linked server example above. It assumes that the remote database has a schema called "dbo" and a table called "Employees".