Linked Servers: Configuration and Application
- Configuring Linked Servers
- Four-Part Naming
- Using OPENQUERY to Access Large Remote Objects
- Calling Remote Stored Procedures
- Summary
SQL Server 7 and 2000 offer many ways for multiple databases to communicate with each other. One of the best ways is via the linked servers feature; in fact, linked servers are required for a distributed SQL Server database system to work. They allow SQL Server to communicate with any other database that can be accessed by OLE or ODBC connectivity. This means that data can be shared efficiently between SQL Server and Oracle, Excel, FoxPro, Access, Sybase, Informix, and so on.
Unlike remote servers, which only allow stored procedures to be called, the linked servers feature allows SQL Server databases to exchange data either through stored procedure calls or through direct SQL transactions against the tables and views permitted by the security implementation. Linked servers also can be set up on single instances of SQL Server, while remote servers require "paired" configurations, meaning that both databases must acknowledge each other as remote servers. Remote servers are present in SQL Server 2000 only for backward compatibility and should not be used in new database implementations. Linked servers are the solution going forward; remote servers will most likely be phased out.
Since there are obviously many ways in which linked servers can be implemented to aid and enhance a variety of systems, this article doesn't attempt to cover them all, but instead focuses on configuring linked servers based on system needs and on some of the best ways to utilize linked servers.
Configuring Linked Servers
As with most SQL Server objects, there are two ways to set up linked servers up: using Enterprise Manager (EM) or by calling system stored procedures. Using EM is generally the easiest method, because it requires no specific knowledge of syntax or memorization of the various options involved. In the case of linked servers, there are actually more options available to the user through EM than with system stored procedures.
To create a linked server, follow these steps:
Open the Enterprise Manager (EM).
Find the server group that requires the linked server and then click the database server for that group.
Expand the database server to display a list of folders such as Databases, Data Transformation Services, Management, Replication, and so on.
Expand the Security group. Listed under Security should be Logins, Server Roles, Linked Servers, and Remote Servers.
-
Right-click Linked Servers and select New Linked Server from the pop-up menu (see Figure 1). The Linked Server Properties – New Linked Server dialog box opens.
Figure 1 Selecting a new linked server.
The General tab, displayed by default, allows you to specify the type of connectivity (OLE, ODBC, Jet, etc), the name of the linked server, and then specific connection information such as product name (SQL Server, Excel, etc.), data source, provider string, location, and catalog to connect to (if applicable). Let's walk through an example of creating a linked server that will connect to another SQL Server instance on another box.
In the Linked Server text box, type a name for the linked server. This name should be the server name, but you can use any name—although it's best to avoid including spaces, the IP address, or periods in the name. (Periods ruin the naming convention discussed later in this article.)
For this example, let's create a linked server called TARGET, residing on IP 123.123.123.123 (see Figure 2).
Figure 2 Configuring the general options of a linked server.
The Security tab includes several options that must be configured to implement the link successfully. You can map multiple logins to remote login IDs, or make a connection using a single login.
-
For this example, we'll use a single login ID for all traffic across the link (see Figure 3).
Figure 3 Setting the security options for a linked server.
Options on the Server Options tab can be turned on, turned off, or provided with specific settings. If you want to invoke stored procedures across the link, select RPC and RPC Out. To get the best performance from your database links, stored procedures are the best way to move data, so these options should always be turned on. Connection Timeout and Query Timeout settings of 0 translate to infinite time; it's generally best to limit these to 60 seconds or less.
-
For this example, use the setup shown in Figure 4 and click OK to create the linked server.
Figure 4 Configuring RPC options for the linked server.
){kind=link}
NOTE
Selecting the SQL Server option in the Server Type section of the dialog box deactivates all the other options. With this setting, the name of the linked server must correspond to the server name or IP of the database. If you can use only IP, not hostnames, or network issues prevent you from making the linked server's name identical to that of the server or IP of the database, don't select this option, since having to use an IP for a linked server name will not work due to the SQL Server naming conventions.
){kind=link}
CAUTION
If you force the link to use a particular login, anyone with access to the database can use the link; this is a security risk if many users are hitting the database directly. If only a few users are directly accessing the database, however, such as a database administrator and a developer, the risk may be minimal. It's generally more secure to have multiple logins mapped to a single remote login. Access to remote databases should be restricted, and having one login to administer on the remote box makes problems easier to track.
){kind=link}
){kind=link}
You can modify the security context and the server options later, but the data source, name, provider string, and so on cannot be altered. If any of the data entered on the General tab is incorrect, the linked server must be dropped and reconfigured from scratch.