Inside Microsoft SQL Server 2005: The Storage Engine (Solid Quality Learning)

Configuration Manager is a tool for managing the services associated with SQL Server, configuring the network protocols used by SQL Server, and managing the network connectivity configuration from SQL Server client computers. It is installed as part of SQL Server. Configuration Manager is available from the Start menu by right-clicking the registered server in SQL Server Management Studio, or you can add it to any other Microsoft Management Console display. Configuration Manager combines the functionality of three SQL Server 2000 tools: Server Network Utility, Client Network Utility, and Service Manager.

Configuring Network Protocols

A specific protocol must be enabled on both the client and server for the client to connect and communicate with the server. SQL Server can listen for requests on all enabled protocols at once. The underlying operating system network protocols (such as Transmission Control Protocol/Internet Protocol [TCP/IP]) should already be installed on the client and the server. Network protocols are typically installed during Microsoft Windows setup; they are not part of SQL Server setup. A SQL Server Net-Library will not work unless its corresponding network protocol is installed on both the client and the server.

On the client computer, the SQL Native Client must be installed and configured to use a network protocol enabled on the server; this is usually done during SQL Server Tools setup. The SQL Native Client is new in SQL Server 2005; it is a stand-alone data access API used for both Object Linking and Embedding Database (OLE-DB) and Open Database Connectivity (ODBC). If the SQL Native Client is available, any network protocol can be configured for use with a particular SQL Server client. You can use SQL Server Configuration Manager to enable a single protocol or to enable multiple protocols and specify an order in which they should be attempted. If the Shared Memory Protocol setting is enabled, that protocol is always tried first, but, as mentioned in Chapter 2, it is available for communication only when the client and the server are on the same machine.

The following query returns the protocol used for the current connection, using the DMV sys.dm_exec_connections:

SELECT net_transport FROM sys.dm_exec_connections WHERE session_id = @@SPID;

Default Network Configuration

The network protocols that can be used to communicate with SQL Server 2005 from another computer are not all enabled for SQL Server during installation. To connect from a particular client computer, you might need to enable the desired protocol. The shared memory protocol is enabled by default on all installations, but it can be used to connect to the SQL Server Database Engine only from a client application on the same computer.

TCP/IP connectivity to SQL Server 2005 is disabled for new installations of the Developer, Evaluation, and SQL Express editions. OLE-DB applications connecting with MDAC 2.8 cannot connect to the default instance on a local server using ".", "(local)", or (<blank>) as the server name. To resolve this, supply the server name or enable TCP/IP on the server. Connections to local named instances are not affected, nor are connections using the SQL Native Client. Installations in which a previous installation of SQL Server is present might not be affected.

Table 3-1 describes the default network configuration settings.

Managing Services

You can use Configuration Manager to start, pause, resume, or stop SQL Serverrelated services. The services available will depend on the specific components of SQL Server you have installed, but you should always include the SQL Server service itself and the SQL Server Agent service. Other services might include the SQL Server Full-Text Search service and SQL Server Integration Services (SSIS). You can also use Configuration Manager to view the current properties of the services, such as whether the service is set to start automatically. Configuration Manager is the preferred tool for changing service properties, rather than using Windows service management tools. When you use a SQL Server tool such as Configuration Manager to change the account used by either the SQL Server or SQL Server Agent service, the SQL Server tool automatically makes additional configurations such as setting permissions in the Windows Registry so that the new account can read the SQL Server settings. Passwords changed using Configuration Manager take effect immediately without your needing to restart the service.