Configuring Linked Servers

A linked server configuration allows Microsoft® SQL Server™ to execute commands against OLE DB data sources on different servers. Linked servers offer these advantages:

• Remote server access.
  
  
• 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.

Linked Server Components

A linked server definition specifies an OLE DB provider and an OLE DB data source.

An OLE DB provider is a dynamic-link library (DLL) that manages and interacts with a specific data source. An OLE DB data source identifies the specific database accessible through OLE DB. Although data sources queried through linked server definitions are usually databases, OLE DB providers exist for a wide variety of files and file formats, including text files, spreadsheet data, and the results of full-text content searches. The following table shows examples of the most common OLE DB providers and data sources for SQL Server.

Note  SQL Server has been tested only against the Microsoft OLE DB Provider for SQL Server, Microsoft OLE DB Provider for Jet, Microsoft OLE DB Provider for Oracle, Microsoft OLE DB Provider for Indexing Service, and the Microsoft OLE DB Provider for ODBC. However, SQL Server distributed queries are designed to work with any OLE DB provider that implements the requisite OLE DB interfaces.

For a data source to return data through a linked server, the OLE DB provider (DLL) for that data source must be present on the same server as SQL Server.

Linked Server Details

This illustration shows the basics of how a linked server configuration functions.

Typically, linked servers are used to handle distributed queries. When a client application executes a distributed query through a linked server, SQL Server breaks down the command and sends rowset requests to OLE DB. The rowset request may be in the form of executing a query against the provider or opening a base table from the provider.

Managing a Linked Server Definition

When setting up a linked server, register the connection information and data source information with SQL Server. After registration is accomplished, that data source can always be referred to with a single logical name.

You can create or delete a linked server definition with stored procedures or through SQL Server Enterprise Manager.

• With stored procedures:
  • Create a linked server definition using sp_addlinkedserver. To view information about the linked servers defined in a given instance of SQL Server, use sp_linkedservers. For more information, see sp_addlinkedserver and sp_linkedservers.
    
    
  • Delete a linked server definition using sp_dropserver. You can also use this stored procedure to remove a remote server. For more information, see sp_dropserver.
• With SQL Server Enterprise Manager:
  • Create a linked server definition using the SQL Server Enterprise Manager console tree and the Linked Servers node (under the Security folder). Define the name, provider properties, server options, and security options for the linked server. For more information about the various ways a linked server can be set up for different OLE DB data sources and the parameter values to be used, see sp_addlinkedserver.
    
    
  • Edit a linked server definition by right-clicking the linked server and clicking Properties.
    
    
  • Delete a linked server definition by right-clicking the linked server and clicking Delete.

When executing a distributed query against a linked server, 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. For more information, see Distributed Queries.

See Also

Identifying a Data Source Using a Linked Server Name

OLE DB Providers Tested with SQL Server

Using Transactions with Distributed Queries