Linked Servers

Linked servers are often-untapped resources that let you use distributed queries to query any server as if it were local. Distributed queries let you keep your inventory data in a DB2 database, for example, and your accounting data in a SQL Server database and query both sets of data as if they were in the same database, eliminating the cost and hassle of merging the two systems. SQL Server lets you execute such queries on any OLE DB-compliant data source. This article shows you how to set up, query, and gather meta data about linked servers.

About Linked Servers
Linked servers are descendants of remote servers, which you can use to execute replication stored procedures in SQL Server. However, Microsoft recommends that you use linked servers instead of remote servers in SQL Server 2000 or SQL Server 7.0 because linked servers have much more functionality, such as the ability to run ad hoc queries. With linked servers, you begin by establishing a connection in SQL Server to a compatible remote OLE DB provider. SQL Server clients connect to the linked server, then SQL Server connects to the remote provider on the client's behalf, as Figure 1 shows. The linked server acts as a middleman, taking an order from the consumer, passing it to the source, then passing it back to the consumer.

Linked servers are especially useful when configuring the OLE DB data source on each client computer is too time-consuming. In addition, stabilizing connections to other types of servers, such as DB2, can be tricky; linked servers minimize this complexity because you need to configure the connection only once. Clients need to connect only to the standard SQL Server provider; they don't need to have an OLE DB provider for DB2, for example, on their workstations.

Linked servers are also the core technology in SQL Server 2000 distributed partitioned views. In a distributed partitioned view, you can make several uniformly distributed tables appear as one table and distribute the load of large queries among many servers. (For more information about distributed partitioned views, see Kalen Delaney and Itzik Ben-Gan, "Distributed Partitioned Views," page 32.) To make this new SQL Server 2000 feature work, however, you need to inform each node of the other nodes' existence by adding a linked server for each participating node in each SQL Server system.

Setting Up a Linked Server
Most of the following examples show you how to link one SQL Server machine to another. I also explain briefly how to connect to DB2 and Oracle. Linked servers are new beginning in SQL Server 7.0, so avoid bugs by installing the latest service pack. If you haven't already done so, I recommend that you install Service Pack 2 (SP2), which contains quite a few fixes for linked servers. Some of the most dangerous bugs include access violation (AV) errors that occur when you use linked servers. For more information about AVs, see the Microsoft articles "FIX: Queries Using Linked Servers May Cause Access Violation and CPU Spin" (http://support.microsoft .com/support/kb/articles/q246/0/31.asp) and "FIX: Insert from Linked Server Into Table with BIT Field Having a DEFAULT Constraint Causes AV" (http://support.microsoft.com/support/kb/articles/q240/7/98.asp).

You can add a linked server through Enterprise Manager. From the Security menu, right-click the linked server icon and select New Linked Server. Or you can script the process in T-SQL by using the sp_addlinkedserver stored procedure:

sp_addlinkedserver [@server =] 'logical name of server' 
[, [@srvproduct =] 'product_name']
[, [@provider =] 'provider_name'] 
[, [@datasrc =] 'data_source']
[, [@location =] 'location'] 
[, [@provstr =] 'provider_string']
[, [@catalog =] 'catalog']

Table 1, page 42, describes each of these parameters, which also exist in the Enterprise Manager screens. The following sp_addlinkedserver example adds a linked server named LINKEDSERVER, which connects to a SQL Server named BKNIGHT:

EXEC sp_addlinkedserver
   @server=LINKEDSERVER,
   @srvproduct = 'SQLServer OLEDB Provider',
   @provider = 'SQLOLEDB',
   @datasrc = 'BKNIGHT'