In the section "Managing the Connections Configuration" earlier in this chapter, you learned that SQL Server installations can be configured to allow local and remote connections. SQL Server can use several protocols, including Shared Memory, Named Pipes, TCP/IP, and VIA. These protocols all have separate server and client configurations.
The network configuration is set separately for each server instance through the SQL Server Network Configuration. The client configuration is set on a per client basis through the SQL Native Client Configuration.
Note
Any system on which you have installed the SQL Native Client is a SQL Server client. This can include systems running Windows 2000, Windows XP Professional, and Windows Server 2003.
The Shared Memory protocol is used for local connections only. If the protocol is enabled, any local client can connect to the server using this protocol. If you do not want local clients to use the Shared Memory protocol, you can disable it.
You can enable or disable the Shared Memory protocol by completing the following steps:
Start SQL Server Configuration Manager. Expand the SQL Server 2005 Network Configuration node, and then select the Protocols For ... entry for the SQL Server instance you want to work with.
Right-click Shared Memory and select Properties.
You can now use the Enabled drop-down menu to enable or disable the protocol. Select Yes to allow the protocol to be used; select No to prevent the protocol from being used.
The Named Pipes protocol is used primarily for local or remote connections by applications written for Windows NT, Windows 98 and earlier versions of the Windows operating system. When you enable Named Pipes, SQL Server 2005 uses the Named Pipes Net-Library to communicate over a standard network address: \.pipesqlquery for the default instance and \.pipeMSSQL$instancenamesqlquery for a named instance. In addition to enabling or disabling the use of Named Pipes, you can configure properties of this protocol to change the named pipe to use.
You can manage the Named Pipes network configuration by completing the following steps:
Start SQL Server Configuration Manager. Expand the SQL Server 2005 Network Configuration node, and then select the Protocols For ... entry for a SQL Server instance.
Right-click Named Pipes and select Properties.
You can now:
Use the Enabled drop-down menu to enable or disable the protocol. Select Yes to allow the protocol to be used; select No to prevent the protocol from being used.
Change the name of the default pipe by typing a new value in the Pipe Name field. (Don’t forget to update the client configuration.)
Click OK.
The TCP/IP protocol is the preferred protocol for local or remote connections to SQL Server. When you use TCP/IP, SQL Server listens on a specific TCP port and IP address for requests. By default, SQL Server listens on TCP port 1433 on all IP addresses. Each IP address on the server can be configured separately, or you can configure all IP addresses for listening.
You can disable TCP/IP by completing the following steps:
Start SQL Server Configuration Manager. Expand the SQL Server 2005 Network Configuration node, and then select the Protocols For ... entry for a SQL Server instance.
Right-click TCP/IP and select Disable.
You can manage the TCP/IP network configuration by completing the following steps:
Start SQL Server Configuration Manager. Expand the SQL Server 2005 Network Configuration node, and then select the Protocols For ... entry for a SQL Server instance.
Right-click TCP/IP and select Properties. On the IP Addresses tab of the TCP/IP Properties dialog box, you should see entries representing the IP addresses configured on the server. Individual IP address entries, in numerical order, such as IP1, IP2, IP3, and so on, are for listening on a specific IP address. The IPAll entry configures SQL Server to listen on all IP addresses on the server.
If you want SQL Server to listen on all IP addresses on the server, you should set all individual IP address entries to Active Yes and Enabled No. Then set a specific TCP listen port for IPAll. The default is 1433. To change the TCP listen port, right-click IPAll and select Properties. In the Properties dialog box, type the TCP listen port in the field provided, and then click OK.
If you want to enable listening on a specific IP address and TCP port, right-click the individual IP entry, and then select Properties. Set Active to Yes, and then set Enabled to Yes. Type the TCP listen port in the field provided and click OK.
When multiple client protocols are available and configured for use, clients use the protocols in a specified priority order. The default order is:
Shared Memory
TCP/IP
Named Pipes
Shared Memory is always the preferred local connection protocol. You can disable Shared Memory protocol to preclude its use and change the order of the other protocols by completing the following steps:
Start SQL Server Configuration Manager. Expand SQL Native Client Configuration, and then click Client Protocols.
Right-click any of the protocols listed, and then select Order. The Client Protocols Properties dialog box displays, as shown in Figure 3-9.
In the Client Protocols Properties dialog box, you can:
Change the order of an enabled protocol. First click the name of the protocol you want to move, and then use the arrow buttons to the right of the Enabled Protocols list as appropriate until the protocol is positioned where you want it in the list.
Disable or enable protocols. To disable an enabled protocol, select it, and then click the shift left button to move the name of the protocol to the Disabled Protocols list. To enable a disabled protocol, select it, and then click the shift right button to move the name of the protocol to the Enabled Protocols list.
Enable or disable Shared Memory protocol. To enable Shared Memory protocol for local client connections, select Enable Shared Memory Protocol. To disable Shared Memory protocol for local client connections, clear Enable Shared Memory Protocol.
Click OK.
The Shared Memory protocol is used for local client connections only. You can enable or disable the Shared Memory protocol for clients by completing the following steps:
Start SQL Server Configuration Manager. Expand SQL Server 2005 Network Configuration, SQL Native Client Configuration, and then click Client Protocols.
Right-click Shared Memory and then select Properties.
You can now use the Enabled drop-down menu to enable or disable the protocol. Select Yes to allow the protocol to be used; select No to prevent the protocol from being used.
The TCP/IP protocol is the preferred protocol for local or remote connections to SQL Server. When connecting to a default instance of the Database Engine using TCP/IP, the client must know the TCP port value. Thus, if a default instance has been configured to listen on a different port, you must change the client TCP/IP configuration to that port number. When connecting to a named instance of Database Engine, the client will attempt to obtain the port number from the SQL Browser Service on the server to which it is connecting. If the SQL Browser Service is not running, the TCP port number must be provided in the client configuration or as part of the connection string.
You can configure the TCP/IP client configuration by completing the following steps:
Start SQL Server Configuration Manager. Expand SQL Native Client Configuration, and then click Client Protocols.
If you want to enable or disable TCP/IP, right-click TCP/IP, and then select Enable or Disable as appropriate.
To view TCP/IP connection properties, right-click TCP/IP, and then select Properties.
To set the default port, select Properties. In the TCP/IP Properties dialog box, enter the default port for the client in the field provided.
You can also configure parameters that control whether and how the client tries to maintain idle TCP/IP connections. Two parameters are used:
Keep Alive. Controls when a client first tries to verify that an idle connection is still valid and attempts to maintain the connection. By default, the client checks a connection after it has been idle for 30,000 milliseconds (30 seconds). In most cases, a value between 30 and 60 seconds will suffice. Depending on how busy the server is and the importance of client activity, you might want to verify and maintain idle connections more quickly, which can ensure that idle connections are not terminated. For example, you could use a smaller value, such as 15,000 or 20,000 milliseconds, to ensure idle connections are validated faster.
Keep Alive Interval. Controls how frequently a client rechecks an idle connection when there is no initial response to the KEEPALIVE transmission. By default, the client retransmits the KEEPALIVE request every 1,000 milliseconds (1 second). If many clients are connecting to a busy server, you might want to lengthen the Keep Alive interval to decrease the number of KEEPALIVE retransmissions.
Click OK.
The Named Pipes protocol is used primarily for local or remote connections by applications written for Windows NT, Windows 98 or earlier versions of the Windows operating system. The default Named Pipes are \.pipesqlquery for the default instance and \.pipeMSSQL$instancenamesqlquery for a named instance. The default pipe for clients is set using an alias. The standard alias for clients is sqlquery, which refers to the default pipe, such as \.pipesqlquery or \.pipeMSSQL$instancenamesqlquery. If you changed the default pipe in the server’s network configuration, you will need to change the default pipe in the client configuration (and for all clients that will connect to SQL Server in this way). For example, if SQL Server is using \.pipesqlserverapp1 as the default pipe, then the client must use sqlserverapp1 as the Pipe Name.
You can manage the Named Pipes client configuration by completing the following steps:
Start SQL Server Configuration Manager. Expand SQL Native Client Configuration, and then click Client Protocols.
Right-click Named Pipes and select Properties. You can now:
Use the Enabled drop-down menu to enable or disable the protocol. Select Yes to allow the protocol to be used; select No to prevent the protocol from being used.
Set the default pipe. In the Named Pipes Properties dialog box, enter the default pipe for the client in the field provided, and then click OK.