Client configuration is a step that might be needed to complete your client installation. It primarily consists of configuring the client's connection so that it can communicate with the server.
For most clients, the default network configuration installed with SQL Server client tools can be used without modification. These clients need only to specify the network name of the SQL Server computer to which they want to connect.
For other clients, the process can be more involved. This includes situations where connections to multiple servers running different network protocols is required. This section explores these different scenarios, the network considerations, and the configuration tools available.
Before you look at client configuration, you must have already installed the two core communication components for SQL Server. First, a pair of matching Net-Libraries (one on the server and one on the client) must be installed. Second, the correct network protocols on both the client and server must be in place.
For the most part, these prerequisites are satisfied as part of a normal SQL Server client setup. As discussed earlier, the Net-Libraries are installed as part of the SQL Server 2000 Client Tools setup. The network protocols are not installed by the SQL Server setup, but they are typically installed as part of the Microsoft Windows setup. They can also be installed via the Network applet in the Control Panel.
After you have satisfied the prerequisites, the client software is commonly configured in two different ways:
Choice of network protocols used by the client software to connect to SQL Server. You define the names of the servers available on your network, which are used by both DB-Library and ODBC-based applications.
The creation of ODBC datasources, which allow access to a specific server using a datasource name (DSN).
The Client Network Utility, discussed next, is the tool used for the first configuration option. The setup of ODBC and the ODBC datasources is discussed in the "Installing ODBC" section later in this chapter.
The SQL Server Client Network Utility screens have changed since SQL Server 7.0, but most of the core functionality is the same. This utility allows you to do the following:
Change the default network protocol
Create network protocol connections to specific servers with special configuration requirements (specific Net-Library choices, ports, names, and so on)
Search for and display information about all the network libraries currently installed on a system
Display the DB-Library version currently installed on the system and set defaults for DB-Library options
The network protocols, including the default, can be set on the first screen that is displayed after launching the Client Network Utility (see Figure 9.3).
The list box on the left side of the screen displays the network protocols that are not enabled. The client cannot communicate with SQL Server using the protocols that are listed here. Protocols can be enabled and disabled using the buttons in the center of the screen.
The right side of the screen displays those network protocols that have been enabled. The defaults, as shown, are the TCP/IP and named pipes protocols. These protocols are listed in priority order with the first item in the list being the default network protocol. The client will first attempt to connect to the server using the default protocol. If a connection cannot be made with it, then a connection is attempted using the next protocol on the list. This continues until a connection is established or the list of entries has been exhausted. This order can be changed using the up and down arrows found below the list box.
In most instances, no change will need to be made on the General tab of the Client Network screen. By default, clients running Microsoft Windows 2000, Windows NT 4.0, Windows 98, Windows XP, or Windows 95 use the client TCP/IP protocol. The exception is when protocols, other than the defaults, are enabled on the server. In this case, you must ensure that the client machines have the same protocols enabled as the server for a connection to be established.
The SQL Server error log is a good place to look to determine the protocols that SQL Server is listening on. You can view the error logs via the Management node in Enterprise Manager, or you can open the error log file using any text editor. The latest error log file is found by default in Program Files\Microsoft SQL Server\Mssql\Log\Errorlog. If you open the error log file with a text editor and find the word Listening in the error log text, you will find the relevant messages. The Server Network utility will show you what has been enabled, but there are instances when the server is unable to listen on the enabled protocol at startup.
In addition to defining the network protocols used by SQL Server, you can also define a server alias based on a specific network protocol. This can be useful when an instance of SQL Server has been configured to listen on an alternate network address or when connecting to a named instance of SQL Server 2000 using a SQL Server 7.0 client.
Figure 9.4 displays the tab where you define an alias in the Client Network Utility. Each network protocol shown on the alias definition screen has settings that correspond to that specific protocol. Each of these is discussed in more detail in the "Network Protocol Support" section that follows.
The alias name that you specify will be the name that you use to connect to SQL Server. It can be different from the computer name of the database server, and SQL Server will still be able to resolve the server location based on the connection information that you provide. SQL Server utilizes the configuration information and the alias names as follows:
If the server name used to connect to SQL Server matches the name of an alias, then the client connects with the network protocol and parameters for that alias.
If the server name does not match the name of an alias, then the default protocol is used.
If no default protocol has been defined, then TCP/IP is used.
The importance of the Alias tab in the Client Network Utility has increased with the advent of SQL Server 2000. Named instances, which have a two-part name (server_name\instance_name) are not supported by several network protocols and some client applications, but an alias that maps to that instance can be used instead.
For example, in Figure 9.4, the alias name of INSTANCE2 is used when adding a new server alias via the Add Network Library Configuration screen. The actual name of the instance, in this example, is Win2000svr\INSTANCE2, but the addition of the alias will allow clients to reference the server using the abbreviated INSTANCE2 name instead.
The multiprotocol, Banyan VINES, and AppleTalk Net-Libraries do not support named instances for SQL Server 2000. You can connect to the default instance using the computer name of the server, but any named instance on that server will not be accessible by default using the two-part instance name.
Another SQL Server 2000 consideration that also relates to named instances is the use of ports to differentiate the network address for each instance running on the server machine. The default SQL Server instance uses port 1433, which is the same port number used in prior versions. For subsequent named instances on the same server, a different port is required for each. SQL Server does not reserve other port numbers for use by named instances. Instead, you can specify a port number of 0 (the default) during the installation of the instance that allows SQL Server to dynamically determine an available port number.
A list of port numbers registered for use can be found on the Internet at http://www.iana.org/assignments/port-numbers. You can look here to determine which port numbers not to use when setting up your alias for a SQL Server instance.
This brief review of named instances is relevant to the Client Network Utility because the alias creation screen allows you to specify a port number to connect to a particular SQL Server instance when using the TCP/IP protocol. This is required for some clients to connect to a named instance and is an easy way to specify an abbreviated server name for your named instance.
For instance, if you refer to the prior example that is displayed in Figure 9.4, you will see that a port number of 1438 was used to define an alias for INSTANCE2. The other server instance listed in this example (INSTANCE1) used port number 1439. The check box Dynamically Determine Port must be unchecked so you can enter a specific port number as in these examples. If this check box is enabled, SQL Server will dynamically determine the port number for you.