Linked server is configured to enable the SQL Server Database Engine to execute commands against OLE DB data sources outside of the instance of SQL Server.
Typically linked servers are configured to enable the Database Engine to execute a Transact-SQL statement that includes tables in another instance of SQL Server, or another database product such as Oracle.
Many types OLE DB data sources can be configured as linked servers, including Microsoft Access and Excel. Linked servers offer the following advantages:
1. The ability to access data from outside of SQL Server.
2. The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
3. The ability to address diverse data sources similarly.
Managing Linked Server Definitions
When you are setting up a linked server, register the connection information and data source information with SQL Server. After being registered, that data source can be referred to with a single logical name.
You can use stored procedures and catalog views to manage linked server definitions:
- Create a linked server definition by running sp_addlinkedserver.
- View information about the linked servers defined in a specific instance of SQL Server by running a query against the sys.servers system catalog views.
- Delete a linked server definition by running sp_dropserver. You can also use this stored procedure to remove a remote server.
- You can also define linked servers by using SQL Server Management Studio. In the Object Explorer, right-click Server Objects, select New, and select Linked Server. You can delete a linked server definition by right-clicking the linked server name and selecting Delete.
- When you execute 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.
Linked Server Details
Linked servers are used to handle the distributed queries. When a client application executes a distributed query through a linked server, SQL Server parses the command and sends 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.
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 the instance of SQL Server.
When a third-party OLE DB provider is used, the account under which the SQL Server service runs must have read and execute permissions for the directory, and all subdirectories, in which the provider is installed.
Linked Server Components
A linked server definition specifies the following objects:
• An OLE DB provider
• An OLE DB data source
OLE DB provider
OLE DB provider is a DLL that manages and interacts with a specific data source. OLE DB providers exist for a variety of files and file formats. These include text files, spreadsheet data, and the results of full-text content searches.
OLE DB data source
An OLE DB data source identifies the specific database that can be accessed through OLE DB. Although data sources queried through linked server definitions are ordinarily databases.
The Microsoft SQL Server Native Client OLE DB Provider (PROGID: SQLNCLI11) is the official OLE DB provider for SQL Server.
NOTE: SQL Server distributed queries are designed to work with any OLE DB provider that implements the required OLE DB interfaces. However, SQL Server has been tested against only the SQL Server Native Client OLE DB Provider and certain other providers.
How to Create a Linked Server
Prerequisites
• You must be logged on with an account that is a member of the SQL Server sysadmin fixed server role to perform this procedure.
• A local SQL Server login is required to be mapped to a login on the SQL Server you will link to. This local login should exist before you begin this procedure.
To create a linked server (SQL Server 2005 and SQL Server 2008)
1. Connect SQL Server Management Studio.
2. In the Connect to Server dialog box, specify the name of the appropriate SQL Server è Click Connect.
3. In SQL Server Management Studio è Double-click Server Objects è Right-click Linked Servers è New Linked Server.
4. In the New Linked Server dialog box è General page, in Linked server è Type the full network name of the SQL Server you want to link to.
NOTE: This procedure often refers to the server you are linking to as the remote server. This is for convenience only, to indicate the relationship of the linked (“remote”) server to the local server. Do not confuse this usage with the obsolete remote server functionality in SQL Server.
5. Under Server type è Click SQL Server.
6. In the left pane of the New Linked Server dialog è Under Select a page è choose Security.
7. You will need to map a local server login to a remote server login. On the right side of the Security page è Click Add button.
8. Under Local Login è Select local login account to connect to the remote server. Check Impersonate if the local login also exists on the remote server. Alternatively, if the local login will be mapped to a remote SQL Server login you must supply the Remote User name and Remote Password for the remote server login.
9. In the left pane of the New Linked Server dialog è Under Select a page è Choose Server Options è Set the Rpc and Rpc Out parameters to True è Click OK.
