Q1.14: Server naming and renaming

There are three totally separate places where SQL Server names reside, causing much confusion.

SQL Server Host Machine interfaces File

A master entry in here for server TEST will provide the network information that the server is expected to listen on. The -S parameter to the dataserver executable tells the server which entry to look for, so in the RUN_TEST file, -STEST will tell the dataserver to look for the entry under TEST in the interfaces file and listen on any network parameters specified by 'master' entries.

TEST
        master tcp ether hpsrv1 1200
        query tcp ether hpsrv1 1200

Note that preceding the master/query entries there's a tab.

This is as far as the name TEST is used. Without further configuration the server does not know its name is TEST, nor do any client applications. Typically there will also be query entries under TEST in the local interfaces file, and client programs running on the same machine as the server will pick this connection information up. However, there is nothing to stop the query entry being duplicated under another name entirely in the same interfaces file.

ARTHUR
        query tcp ether hpsrv1 1200

isql -STEST or isql -SARTHUR will connect to the same server. The name is simply a search parameter into the interfaces file.

Client Machine interfaces File

Again, as the server name specified to the client is simply a search parameter for Open Client into the interfaces file, SQL.INI or WIN.INI the name is largely irrelevant. It is often set to something that means something to the users, especially where they might have a choice of servers to connect to. Also multiple query entries can be set to point to the same server, possibly using different network protocols. Eg. if TEST has the following master entries on the host machine:

TEST
       master tli spx /dev/nspx/ \xC12082580000000000012110
       master tcp ether hpsrv1 1200

Then the client can have a meaningful name:

ACCOUNTS_TEST_SERVER
        query tcp ether hpsrv1 1200

or alternative protocols:

TEST_IP
        query tcp ether hpsrv1 1200
TEST_SPX
        query tli spx /dev/nspx/ \xC12082580000000000012110

sysservers

This system table holds information about remote SQL Servers that local one might want to connect to, and also provides a method of naming the local server.

Entries are added using the sp_addserver system procedure - add a remote server with this format:

        sp_addserver server_name, null, network_name

server_name is any name you wish to refer to a remote server by, but network_name must be the name of the remote server as referenced in the interfaces file local to your local server. It normally makes sense to make the server_name the same as the network_name, but you can easily do:

        sp_addserver LIVE, null, ACCTS_LIVE

When you execute for example, exec LIVE.master..sp_helpdb the local SQL Server will translate LIVE to ACCTS_LIVE and try and talk to ACCTS_LIVE via the ACCTS_LIVE entry in the local interfaces file.

Finally, a variation on the sp_addserver command:

        sp_addserver LOCALSRVNAME, local

names the local server (after a restart). This is the name the server reports in the errorlog at startup, the value returned by @@SERVERNAME, and the value placed in Open Client server messages. It can be completely different from the names in RUN_SRVNAME or in local or remote interfaces - it has no bearing on connectivity matters.