Chapter 19. Managing Linked and Remote Servers

by Ray Rankins and Paul Bertucci

IN THIS CHAPTER

• Remote Servers

• Linked Servers

• Adding, Dropping, and Configuring Linked Servers

• Mapping Local Logins to Logins on Linked Servers

• Obtaining General Information About Linked Servers

• Executing a Stored Procedure Via a Linked Server

• Setting Up Linked Servers Through Enterprise Manager

As your databases grow in size, complexity, or geographic distribution, you might find it necessary to spread out your data across multiple servers. SQL Server has long had the ability to perform server-to-server communication. In versions of SQL Server prior to 7.0, this was done using remote procedure calls (RPCs). A remote procedure call is the execution of a stored procedure on a local server that actually resides on a remote server. This capability allows you to retrieve or modify data that resides on a different SQL Server. The main drawback, however, is that you cannot join between tables residing on more than one server using remote procedure calls, nor can you selectively choose the columns of information you want to retrieve from the remote server. You get whatever the stored procedure on the remote server is defined to return.

If you need to join information together across servers, SQL Server 2000 provides the ability to link servers together. In addition to providing the capability to perform remote procedure calls, you can also access remote tables as if they were defined locally. The remote tables can be on another SQL Server or any datasource with an OLE DB provider. You can also define distributed partitioned views that can pull data together from multiple servers into a single view. For your end user queries, it appears as if the data is coming from a single table. For more information on distributed partitioned views, see Chapter 27, "Creating and Managing Views in SQL Server."

This chapter provides an overview of linked servers in SQL Server 2000 along with a brief discussion of remote servers, which are the predecessor to linked servers. Remote servers and remote procedure calls are legacy features that are still supported for backward com-patibility. In other words, you can still set up and use remote servers and remote procedure calls, but linked servers are much easier to set up and provide greater functionality.