Network Protocols | Inside Microsoft SQL Server 2000

If you accept the default installation option during setup, SQL Server on Windows NT and Windows 2000 installs named pipes, shared memory, and TCP/IP sockets as its interprocess communication (IPC) mechanisms for communication with clients. SQL Server can simultaneously use many IPC mechanisms and networking protocols, but any specific client connection uses only one for its connection. You learned in Chapter 3 that each SQL Server networking interface is known as a Net-Library and represents a specific IPC mechanism. In addition to the Named Pipes, Shared Memory, and TCP/IP Sockets Net-Libraries, you can install one or more of the following:

Multiprotocol
NWLink IPX/SPX
AppleTalk ADSP
Banyan VINES

Although the Named Pipes Net-Library remains a good choice, its use as a default is mostly historical. Named pipes was the first, and for a while the only, IPC mechanism used by the early versions of SQL Server. Later, even when TCP/IP sockets and IPX/SPX were supported, those protocols were more difficult to configure than named pipes, requiring configuration at each client. For example, using TCP/IP required that the administrator configure an arcane IP address at every client workstation for every installation of SQL Server it might access. Now, with the sophisticated network naming services provided by Windows NT and Windows 2000 (such as WINS, DHCP, and DNS), TCP/IP sockets are almost as easy to use as named pipes.

In addition, SQL Server 2000 allows the Shared Memory Net-Library to be used to communicate between SQL Server and local clients. The Network Libraries dialog box that appears during a custom installation of SQL Server 2000 does not give you the option of specifying or not specifying this library. SQL Server always enables it. You can determine whether you actually want shared memory to be used by local clients by using the Client Network Utility. Figure 4-2 shows the dialog box for determining which protocols will be used by a client and in which order the protocols will be tried. A check box at the lower left allows you to specify whether to use shared memory when communicating with local SQL Server instances.

click to view at full size.

Figure 4-2. Using the Client Network Utility to determine which protocols the local clients will use.

Unless you have a compelling reason to choose a different network interface (the most compelling, of course, is if your existing network or network standards dictate some other network choice), use the defaults because they provide the most functionality. They are protocol-independent and allow the use of Windows Authentication Mode when you connect to SQL Server; you can thus provide a single logon name to your users so they don't have to log on to both the Windows NT or Windows 2000 domain and SQL Server. Windows Authentication Mode is an important feature for convenience and ease of administration as well as for making your system more secure. Windows Authentication Mode is also assumed with SQL Server replication services and when Performance Monitor connects to SQL Server.

The Multiprotocol Net-Library is built using the operating system's remote procedure call (RPC) services. Prior to SQL Server 2000, it was one of the only ways to get encryption over your network connection. Now, you should use Multiprotocol only for compatibility with existing systems. Secure Sockets Layer (SSL) encryption can be enabled over any Net-Library in SQL Server 2000 using the Server Network Utility. Multiprotocol is not supported on machines running Windows 98 or for named instances of SQL Server.

Microsoft internal testing has found the TCP/IP Sockets Net-Library to be the fastest networking choice, although in a typical LAN you rarely see the network as a performance bottleneck. In a low-speed WAN, however, this can be an important issue. Some network administrators have also been concerned about potentially routing NetBIOS traffic across their LANs and WANs. However, if SQL Server is not using the Named Pipes Net-Library and the Multiprotocol Net-Library is not using named pipes under the covers for IPC, SQL Server is not using NetBIOS at all and this is not a concern.

The NWLink IPX/SPX Net-Library is of most interest to those running Novell networking software on their clients that access SQL Server. If you are using a Novell NetWare-based network and file server but your SQL Server clients use Windows 98 or Windows NT and Windows 2000, using the NWLink IPX/SPX Net-Library is unnecessary. If your clients use networking software provided by Novell, NWLink IPX/SPX is probably your best choice. Server enumeration is available with NWLink IPX/SPX using the NetWare Bindery services.

Choose Banyan VINES if you interoperate with that environment. The Banyan VINES Net-Library uses StreetTalk naming services for server enumeration and name resolution. Use the AppleTalk ADSP Net-Library if you will support Apple Macintosh clients (using the Inprise—formerly Visigenic—ODBC driver for Macintosh) that run only AppleTalk, not TCP/IP.

During installation, you must supply additional information for any of the network options you have selected—for example, the port number or network name on which the server running SQL Server will "listen" for new connections or broadcast its existence to a network naming service. In most cases, you should accept the default unless you have a compelling reason not to. In the case of TCP/IP sockets, you should accept the default port number of 1433 for the default instance only. This number is reserved for use with SQL Server by the Internet Assigned Numbers Authority (IANA), and as such it should not conflict with a port used by any other server application on your computer. (This assumes that the developers of other applications also follow the proper protocol of getting assigned numbers.) Additional named instances of SQL Server installed on the same machine will need to use a different port number, which can be dynamically assigned. We'll discuss named instances in detail later in this chapter.

SQL Server on Windows 98 doesn't support the server Named Pipes, Multiprotocol, Banyan VINES, and AppleTalk Net-Libraries. SQL Server does support the client side of Named Pipes and Banyan VINES Net-Libraries on Windows 98, so Windows 98 clients can use them to connect to SQL Server installations on Windows NT and Windows 2000.

TIP
If you're new to networking and don't know your IP from your DHCP, don't fret. Accept the defaults, and configure your networking later as your understanding improves (or get your network administrator to help you). Although it's a good idea to understand your networking choices before installing SQL Server, you can easily change the networking options later without disturbing your SQL Server environment.