Few words about SQL Server services

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

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 canselect the serviceforwhich you wantthe service to work. The choiceremainsdedicatedaccount(recommended option) or the built-in account. Also, withthis tab,we havethe ability to run or stop the service.

 

Servicetaballows you topreview thedetails of theserviceas shown below:

Service manage dialog box

At this point it is possible to set the service startup (Start Mode). With the option to do is 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.

 

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