Network Protocols | Inside Microsoft SQL Server 7.0 (Mps)

If you accept the default installation option during setup, SQL Server on Windows NT installs named pipes, TCP/IP Sockets, and multiprotocol as its interprocess communication (IPC) mechanism for communication with its clients . SQL Server can simultaneously use many IPC mechanisms and networking protocols (but any specific client connection uses only one for its connection). As you learned in Chapter 3, each SQL Server networking interface is known as a Net-Library and represents a specific IPC mechanism. In addition to the above three, you can install one or more of the following Net-Libraries shown in Figure 4-2.

NWLink IPX/SPX
Appletalk ADSP
DECNet Sockets
Banyan VINES

click to view at full size.

Figure 4-2. SQL Server Net-Library interface options.

Both the Named Pipes and Multiprotocol Net-Libraries use protocol- independent IPC mechanisms (named pipes and RPC services). This means that you can use either interface with multiple underlying network protocols, including TCP/IP, NetBEUI, and NWLink IPX/SPX. All of the other choices imply not only the IPC mechanism but the specific network protocol that both the client and the server must use.

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 instance of SQL Server it might access. Now, with the sophisticated network naming services provided by Windows NT (for example, WINS, DHCP, and DNS), other Net-Libraries such as Multiprotocol and TCP/IP Sockets are almost as easy to use as Named Pipes.

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 NT Authentication when you connect to SQL Server, which lets you provide a single logon name to your users so they don't have to log on to both the Windows NT domain and the SQL Server. Windows NT Authentication for SQL Server uses the Windows NT impersonation features, which are available only with these default networking choices. Windows NT Authentication is an important feature for convenience and ease of administration as well as for making your system more secure. Windows NT Authentication is also assumed with SQL Server replication services and when Performance Monitor connects to SQL Server.

The Multiprotocol interface is built using Windows NT RPC services. It offers one important feature that none of the other networks offers encryption. All conversation between the client and server can be encrypted using the encryption services provided by Windows NT. (A 40-bit key is the maximum currently allowed for export by the U.S. government, so this is the key size used for Windows NT versions sold outside the United States. Windows NT 4, sold in the United States, uses a 128-bit key for tighter security.) Your data is secure even from someone using a hardware device such as a "network sniffer" to intercept network packets right off the wire. The encryption services work across the Internet as well.

The Cost of Encryption
Encryption services impose about a 25 percent performance overhead for network traffic. However, with use on a LAN, the network is rarely the performance bottleneck in a well-designed application and the actual performance difference is usually much less noticeable. But network performance is a bigger concern with a slow WAN or Internet application. In those cases, it can become a bottleneck. Even with slow networks, however, the performance issues usually relate to how often you make requests to the server (that is, how many network "round-trips" you make) rather than the speed of the Net-Library.

When you use the Multiprotocol interface and provide multiple underlying network protocols for it to choose from, you can explicitly choose the default binding. For example, by default the Multiprotocol network interface might, behind the scenes, use named pipes over NWLink. However, you can configure the interface to choose TCP/IP sockets instead if this is important in your network. (For more details, see the name resolution information under the Multiprotocol Clients topic in the "Administering SQL Server" section of SQL Server Books Online.) The ability to dynamically enumerate network computers that run SQL Server is not available with the Multiprotocol network interface.

Microsoft internal testing has found the TCP/IP Sockets Net-Library to be the fastest networking choice. (As stated in the above sidebar, 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 accessing SQL Server. If you are using a Novell NetWare_based network and file server but your SQL Server clients use Windows 95, Windows 98, or Windows NT, using the NWLink IPX/SPX Net-Library is unnecessary. Either Named Pipes or Multiprotocol over the underlying NWLink network protocol is probably a better choice because Windows NT Authentication is not available with the NWLink IPX/SPX Net-Library. 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 and DECNet Sockets if you interoperate with those environments. The Banyan VINES Net-Library uses StreetTalk naming services for server enumeration and name resolution. There is no support for dynamic SQL Server enumeration on DECNet. Windows NT Authentication is not an option with either of these Net-Libraries.

Use Appletalk ADSP Net-Library if you will support Apple Macintosh clients (using the Inprise formerly Visigenic ODBC driver for Macintosh) running 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. 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 followed the proper protocol of getting assigned numbers .)

Even if you will primarily use another Net-Library for SQL Server use, we recommend that you do not remove Named Pipes. If your network fails entirely, you can still access your SQL Server machine locally using named pipes as the IPC mechanism, because it is an intrinsic service of Windows NT even when the computer is not part of a network. Named pipes can provide a convenient "last chance" way to access SQL Server if, for example, your network card has failed and network access is currently unavailable. If you decide not to use the Named Pipes Net-Library, remove it after installation is complete. The installation program assumes the existence of named pipes services so that it can operate if the computer is not in a network environment.

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

TIP
If you are 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.