Adding Remote Servers

To add a remote server, you must set up both the local server and the remote server. On the local server, you must add the server name of the remote server. On the remote server, you must add the server name of the local server. To do this, you use the sp_addserver system procedure to add entries to the sysservers system table.

After you have added the remote server, you must reset certain configuration options on both the local and the remote server as described in Setting Configuration Options for Remote Servers.

Before you add a remote server, be sure that you can access the remote server as a client using isql.

    To check that you can access the remote server

• On both the local and remote servers, type:

  isql /Usa /Psa_password /Sservername

  where

  sa_ password

  Is the system administrator's password.

  servername

  Specifies the remote server to connect to.

If you have difficulty connecting to the remote server, you can use the SQL Client Configuration Utility to configure access to the server. For more information about using the SQL Client Configuration Utility, see Chapter 4, Configuring Clients.

After you have verified that you can connect to the remote servers as a client, you can set up the servers for remote access. You must execute sp_addserver on both the local server and the remote server.

    To add a remote server

• Type:

  sp_addserver server_name [, local]

  where

  server_name

  Is the name of the server.

  local

  Sets server_name as the @@SERVERNAME global variable. For Windows NT - based servers, the @@SERVERNAME global variable is set at installation to the computer name, so you will usually not need to use this option, (unless you decide you want to change the server name).

  One reason to use the local option occurs when you are running both SQL Server 4.2 and SQL Server 6.0 servers at the same time on the same computer. In order to be supported by the graphical tools the servers must use different names. For example, if you want to use SQL Transfer Manager to transfer data between the two, each must use a different server name.

For example, to add EXPENSES as a remote server for the ACCOUNTS server, from ACCOUNTS, type:

sp_addserver expenses

From the remote server EXPENSES, type:

sp_addserver accounts

When you add a new entry with sp_addserver, the srvname and srvnetname are entered in the sysservers table. The srvname is the name that users must supply when executing calls to remote procedures. The srvnetname is the server's network name. The srvname must be unique on each server; the srvnetname does not have to be unique. When you add a new server, the srvname and the srvnetname store the same value.

In the sysservers table, a srvstatus of 0 indicates that the server is the local server, and 1 indicates the server is a remote server.