DBMS Guide: Understanding SQL Server Configuration Manager

SQL Server Configuration Manager is a tool to configure the network protocols used by the SQL Server, and to manage network connectivity.
We will find SQL Server Configuration Manager API in the below path:
Start -> Microsoft SQL Server 2008 R2 -> Configuration Tools -> SQL Server Configuration Manager
SQL Server Configuration Manager gets information for WMI (Windows Management Instrumentation) Provider which takes information for “SQLServerManager10.msc” which is located in “C:\Windows\System32\” path. Generally SQL Server Configuration Manager will use to Start, Stop, resume and Configure services of SQL Server.

Left pane has list of configurations we can do for SQL Server.

SQL Server Services
SQL Server Network Configuration
SQL Server Native Client Configuration

SQL Server Services

SQL Server Integration Services
SQL Server Analysis Services
SQL Server (Instance Name)
SQL Server Browser
SQL Server Agent (Instance Name)
SQL Server Reporting Services (Instance Name)
SQL Server Full – text Filter Daemon Launcher (Instance Name)

SQL Server Integration Services

A Windows service for managing Integration Services packages
Starting remote and locally stored packages
Stopping remote and locally running packages
Monitoring remote and locally running packages
Importing and exporting packages
Managing package storage
Customizing storage folders
Stopping running packages when the service is stopped
Viewing the Windows Event log
Connecting to multiple Integration Services servers

SQL Server Analysis Services

A Windows services to run SSAS
Each instance of Analysis Services runs as Windows service, Msmdsrv.exe, in the security context of a defined logon account.
The service name of default instance of Analysis Services is MSSQLServerOLAPService.
The service name of each named instance of Analysis Services is MSOLAP$InstanceName.

SQL Server (Instance Name)

A Windows Services to run SQL Server Instance.
Every instance will have different service to run.
Without running the service, SQL Server will not allow you to program.

SQL Server Browser

SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer. SQL Server Browser contributes to the following actions:

Browsing a list of available servers
Connecting to the correct server instance
Connecting to dedicated administrator connection (DAC) endpoints

SQL Server Browser can be configured during setup or by using SQL Server Configuration Manager. By default, the SQL Server Browser service starts automatically:

When upgrading an installation.
When installing side-by-side with an instance of SQL Server 2000.
When installing on a cluster.
When installing a named instance of the SQL Server Database Engine including all instances of SQL Server Express.
When installing a named instance of Analysis Services.

SQL Server Agent

SQL Server Agent executes jobs from SSIS.
SQL Server Agent uses SQL Server to store job information.
SQL Server Agent can run a job on a schedule, in response to a specific event, or on demand.

SQL Server Reporting Services

This will be used by SSRS of the instance.
Without running these services SSRS will not run.

SQL Server Full – text Filter Daemon Launcher

This Service is used by Full –text Search in SQL Server.
This service must be running to use full-text search.

SQL Server Network Configuration

SQL Server Network Configuration gives you, privilege to configure protocols for client and server. Protocols supports by SQL Server Network Configuration are listed below:

Shared Memory – Shared memory protocol is a simplest protocol. Useful for clients who want you to access SQL server instance running on the same computer.
Named Pipes – Named Pipes protocols are used in LAN.
TCP/IP – These protocols are used across the machines connected through internet.
VIA – Virtual Interface Adapter (VIA) is an old protocol used to connect with VIA Hardware (Cisco Virtual Interface Card etc.,). In the near future, development of SQL Server, Microsoft wants to deprecate this feature in developer perspective.

TCP/IP VS Named Pipes

For named pipes, network communications are typically more interactive. A peer does not send data until another peer asks for it using a read command. A network read typically involves a series of peek named pipes messages before it starts to read the data. These can be very costly in a slow network and cause excessive network traffic, which in turn affects other network clients.
For TCP/IP Sockets, data transmissions are more streamlined and have less overhead. Data transmissions can also take advantage of TCP/IP Sockets performance enhancement mechanisms such as windowing, delayed acknowledgements, and so on. This can be very helpful in a slow network. Depending on the type of applications, such performance differences can be significant.

Generally, TCP/IP is preferred in a slow LAN, WAN, or dial-up network, whereas named pipes can be a better choice when network speed is not the issue, as it offers more functionality, ease of use, and configuration options.

SQL Server Native Client Configuration

SQL Server Native Client is used on the computer to run the client programs like ODBC Connections. These settings will not effect the client unless and until the client is using SQL Server Client tools. We are able to see two sub sections under SQL Server Native Client Configuration.