SQL Server services

{:en}Today I would like to write few words about SQL Servier Services. This topic is quite easy but some people doesn’t know basic things about services. We can manage services with two simple administration tools:

  • Windows Services
  • SQL Server Configuration Manager

Microsoft recommend to use second option – Configuration Manager (CM) – and if we can we should use this tool but not in every situation (as I will show lately).  Why we need to use CM? Because in SQL Server 2005 Microsoft changed encryption alghoritm for storing passwords, so when we change password in services.msc instead of CM then our service will not start!

First of all we need to know that we must have appropriate credentials to manage services – administrators must plan who and why need access to CM. We can access Configuration Manager in start menu as I show in the picture below:

Sql Server Configuration Manager access

If we double click CM icon new applet should appear:

Sql server configuration manager overview

Configuration Manager has very simple interface based on five tabs.

  •       SQL Server services – here we can run and stop services and change account that every service use, how service starts and so on
  •       SQL Server Network Configuration (32 bit) – 32 bit version of network settings of out instance(s)
  •       SQL Native Client 11.0 Configuration (32 bit) – here we can setup protocols and aliases

Rest tabs are similar to above but they are 64 bit.

Sql server configuration manager

First thing that we will see in this tab is the name of the service. Most of the services are followed by name of associated instance in the brackets – only Sql Server Browser and Integration Services are services shared between instances.

Next we can get information about state of every service. It has only three possible states:

  •           Running
  •           Stopped
  •           Paused

And we can switch between this states by choose state that we want from context menu.

Another very important information for us is Start Mode. IF we choose Automatic – the service will start with OS, if Manual – we need to run service every time we want. Last two things is an account that use service and process identifier.

After entering the service properties window will appear as in the screenshot below:

service manage dialog box

On theLog Ontab,you can select the service for which you want the service to work. Our choice remains dedicated account(recommended option) or the built-in account. Also, with this tab,we have the ability to run or stop the service.

Service tab allows you to preview the details of the service as you can see below:

Service manage dialog box

At this point it is possible to set the service startup (Start Mode). In this window you can view also the Binary Path identifying the location of the executable file for the service which it runs.

Service manage dialog box advance tab

The last tab contains advanced settings, here you can find information on whether the service is part of a cluster or where service errors will be dump.

The last tab contains advanced settings, here you can find information on whether the service is part of a cluster or where service errors will be dump.

SQL Server Network Protocols tab allows you to set priorities for the use of protocols to connect to the SQL server services, available is 3 possibilities:
Shared memory – shared memory, you should set it in the first place when the client is on the same computer as the server
Named Pipes – named pipes used in the LAN
TCP / IP – the protocols used on the Internet

The last tab – the SQL Server Native Client 11.0 Configuration allows you to configure connecting protocols, client tools and aliases (listed servers and attached to them connection settings). Tab allows you to define specific settings for connections to specific servers.

Although Configuration Manager is a tool to manage services SQL Server does not contain all the services associated with SQL Server. There is also a service called Distributed Transaction Coordinator is responsible, inter alia, for transactions in Integration Services packages. It is also worth remembering sqlwriter.exe service as documented below:

Task manager sqlwriter

It is a service that supports the creation of backup and restore the database from the  Volume Shadow Copy Service framework (VSS).

That’s all for today, i hope you enjoyed this post. Next post will be about DAX language and introduction to PowerPivot – when? i hope soon – because now i have a lot things to do but i try to write few words next week.

Leave a Comment

Your email address will not be published. Required fields are marked *