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.
isql /Usa /Psa_password /Sservername
where
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.
sp_addserver server_name [, local]
where
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.