.NET Hawa

Understanding Sql Server Configuration Manager

on September 14, 2013

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 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.

sql server configuration manager

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:

 

  1. Sql Server Integration Services
  2. Sql Server Analysis Services
  3. Sql Server (Instance Name)
  4. Sql Server Browser
  5. Sql Server Agent (Instance Name)
  6. Sql Server Reporting Services (Instance Name)
  7. Sql Server Full – text Filter Daemon Lanucher (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 this services SSRS will not run.

Sql Server Full – text Filter Daemon Lanucher

  • 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:

  1. Shared Memory – Shared memory protocol is a simplest protocol. Useful for clients who wants you to  access  Sql serverinstance running on the same computer.
  2. Named Pipes – Named Pipes protocols are used in LAN.
  3. TCP/IP – These protocols are used across the machines connected through internet.
  4. VIA – Virtual Interface Adapter (VIA) is an old protocol used to connect with VIA Hardware (Cisco Virtual Interface Card etc.,). In 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 effects the client unless until client is using SQL Server Client tools.  We are able to see two sub sections under Sql Server Native Client Configuration.

  • Client Protocols
  • Aliases – An Alias is an alternate name for connection.

Sql Server Native Client Configuration

How to Enable Protocols:

  1. Select a Protocol in the list right-click on it.
  2. Check Enable/Disable.

Properties

*References:

 

  1. 1.      www.technet.microsoft.com
  2. 2.      www.myTechMantra.com/LearnSQLServer/Overview_of_SQL_Server_Configuration_Manager.html
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: