Monday, August 10, 2009

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:


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



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

You can manage 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.