SQL Server Services
Introduction:
As the time passes by, the technology also shifts its gears and travel towards a better destination. Talking about SQL server the trend is the same.
From SQL server 2005 to 2008 there were few changes in design, options and performance but something that is relatively similar are services. Though there are few additional features like support for policy based management, auditing, geo-spatial data and advance reporting and analysis services, the basic services that are required for proper functionality of SQL server remains almost the same.
Services are individual programs or executable files that allow any application to carry out its tasks more easily and efficiently. It gives user the leverage of selecting the service he wants to use and stop others so that he can maintain the performance of the system.
In this article we will be discussing about the following services used in SQL server 2005/2008:
- SQL Server Full text search service.
- SQL Server Browser Service.
- SQL Server Agent service.
- SQL Server (Database engine service).
How to view services:
There are two common ways by which you can view the installed SQL server services on your machine and their status:
- Go to start menu -> All programs -> Administrative Tools and select services as shown below:
Here you can view all the local services running on your machine, these also include the SQL services as you can see in the figure below:
- Another way to view the SQL services is through SQL server configuration manager. From here you can also manage and configure these services.
Go to Start -> All programs -> Microsoft SQL server 2005/2008 -> Configuration Tools and select SQL server configuration manager as shown in the figure below:
The SQL server configuration manager service will appear as follows in which you can clearly see the SQL services and their status.
Let’s go through these services one by one.
SQL server Full Text Search service:
Let’s understand first what full text search is. Suppose you want to search text “January” in your table, you can do it very easily, but what if you don’t want an exact match? What if you want two words near each other? Unfortunately T-SQL did not support this, Instead an operating instead an operating system component named Microsoft search service was used by SQL server for this purpose.
However the good news is that in SQL server 2008 according to Microsoft technical article the full text search functionality has been integrated with the database engine.
This feature simplifies management and improves the functionality and performance of SQL server.
Open the SQL configuration manager and you can see the full text search services in SQL services as below:
Right click on the service and you can see three major text enabled: start, stop and properties as shown in the figure below:
Start is used to start up the service and as the name suggest stop disables it. You can also pause or resume the service.
Click the properties and the following window will appear:
In the above image you can see the three tabs: logon, service and advance.
Let’s explore them one by one:
Here the local system account does not require a password; however this can restrict the service from interactivity with the other servers.
This account is a local or domain account that uses windows authentication. Microsoft recommends using domain user account with minimal rights for services.
Account name specifies domain or local account name. Then we have the password of that account.
Service Status indicates the current status of the service
Than we have the following buttons:
- Start
- Stop
- Pause
- Restart
Now let go to the service tab:
This tab contains the following properties:
Binary Path contains the location of the files used by the services.
Error Control when set to ‘1’ indicates that service is running normally. If service fails to start, the startup program logs the error and displays an error message. This value cannot be changed.
Exit Code provides the code or number of the error that occurs. This code is further used for troubleshooting.
Host Name displays the name of the computer that is running SQL server agent.
Name displays the name of the service.
Process ID displays windows process id.
SQL service type displays the service type installed in SQL server.
Start mode defines whether the service will be started manually or automatically or will be disabled.
State indicated whether the service is stopped running or disabled.
Here we go now, let’s move to our next tab i.e.
Advance Properties:
Following are the properties that we found in this tab:
Clustered tells if the service is installed as a resource of a clustered server.
Customer feedback reporting indicates whether service quality monitoring is enabled or not.
Dump Directory contains the location where memory dumps are placed.
Instance ID indicates the instances that use this service.
The full text service properties and appearance of SQL server configuration manager in SQL server 2008 are exactly the same as SQL server 2005.
SQl server Browser Service:
Before SQL server 2000, it was not possible to install more than one instances of SQL server on a single computer.
All incoming requests were listened by SQL server on 1433 Port. This port was assigned to SQl server by the official internal assigned numbers authority (IANA). This port could only be used by one instance of SQL server.
When SQL server 2000 was launched multiple instances of SQL server were introduced. For this purpose SQL server resolution protocol (SSRP) was used to listen on UDP port 1434. In this way the listeners’ service responded to clients request using the names of the instances, on the port used by the instances.
Moving on after that when SQL server 2005 was launched; Microsoft introduced SQl server browser service to overcome the limitations of SSRP. This service carried on without any change in SQL server 2008 as well.
SQL server browser service works by using TCP/IP or VIA protocols.
When SQL server starts the server is assigned a TCP/IP port. This port is used by one specific instance to exchange data with client applications. During installation TCP port 1433 is assigned to the default instance.
Well that was a brief description of the service; now let’s look at some of the properties of the service.
Right click the service and select properties:
Following window will appear:
Here again you can see three tabs:
- Logon
- Service
- Advance
Let’s explore these:
Logon:
Local System Account runs SQL server browser service in a security of the local system account.
This Account specifies a local or domain user account that uses windows authentication.
Browse: Provides browsing facility for a user or built in security principal.
Password contains the password of the account
Service status tells us whether the service is running, stopped, paused or disabled.
And finally the following buttons tells their function by name:
- Start
- Stop
- Pause
- Resume
Now click on the services tab to view its properties:
Well isn’t this a familiar screen to us? Yes it is. These are exactly the same properties as we have discussed for full text service in the previous section. So you can refer to that section for reference.
Let’s move on to the next tab i. e. the advance tab, click the advance tab and you will see the following:
Now then again you can see the properties in advance are similar to the ones we discussed in Full text search service.
One thing that I would like to mention here is if you enable Error reporting to yes this will sent try to send the error that occurs in this service to Microsoft.
You can clearly see that the configuration and properties of both the services are almost the same, its just the functionality that differs.
SQL server Agent Service:
This service is basically a windows service which has a capability to schedule administrative tasks. These tasks technically are known as jobs.
SQL Agent stores the information of the jobs in SQL server. Jobs are basically a sort of cluster of programs that contain one or more steps and each step has its own task, for example in one step a table is created and in other steps records are deleted from some table etc.
If any of the steps of a job is failed the whole job is failed and stopped.
SQL agent can record each and every event and can show it to you through Job Monitor.
SQL Agent service can be started or stopped through SQL configuration manager, Windows Services console and SQL server management studio as follows:
- Through SQL server configuration manager.
- Through windows services console
And finally in the above image through SQL server management studio.
Well now let’s dill down the SQL server agent we see in the object explorer of the management studio.
Now then you can see that there are few folders but the think that I want to show here and is in the scope of this article is the job monitor. This is a tool through with you can see the status of different jobs i. e. whether or not they are enabled, when they were run last time and if there is any error occurred.
Double click the Job monitor and you will get the following popup window:
Now then here you can see different fields let’s go through them one by one:
Name: Tells us the name of the particular job.
Enabled: Tells us whether or not the job is enabled. You can clearly see that the job ‘Clear_Backup’ is not enabled where as ‘Move_Records_Backup’ is enabled.
Status: Tells us the current status of the job. You can see currently its idle as the job will run at its specified time.
Last run output: Tells us whether the last run was successful or not.
Last Run: Tells us the date and time when the job was run last time.
Next run: tells us the Next schedule time of the job run.
Category: Tells us the category of the job which is undefined here.
Runnable: Tells us if the job is able to run or not i. e. if it contains any error it will not run.
Schedule: tells us whether the job time is scheduled or not.
Now then let’s move on to the properties of the SQL server agent service that we can see in SQL server configuration manager:
Right click on the SQL server Agent service and select properties:
Click on the services tab:
Now click on the advance tab:
Now then in all the above three images you can clearly see that the properties of SQL server agent service are the same as we have for SQL server full Text service and SQL server browser service, So refer to those section for more details. The SQL server Agent service is also unchanged from 2005 to 2008 so there is no difference in this service for both versions. For now let’s move on to our next service.
SQL server (Database engine service):
This service is one of the most important and core services of SQL for storing, processing and securing data.
All the transactions, Processes and functions etc. are carried out through database engine.
Database engine is responsible to provide a secured and controlled access and it also carries out rapid transaction on processes and queries in order to meet requirements of the most demanding database consuming applications.
It can rightly be said that database engine acts as a heart of SQL server.
The database engine also provides the foundations and fundamentals for the majority of the core database administration tasks. It is designed to provide a scalable, fast and highly available platform for data access and other components.
The database engine can be divided into two components:
- Storage engine
- Query processor
The storage engine writes and retrieves data from Storage devices. While the query processor accepts, parses and executes SQL commands.
When you type a SELECT statement of your query, actually you are asking the query processor to retrieve data that resides in data files on the disk. The files are usually stored in .mdf or .ndf file extension. In addition to the data, the data files contain indexes and other objects. The indexes of these files are copied to a server’s RAM.
The memory has several names, including buffer cache, buffer pool, and bpool. If the selected index is already present in the buffer cache, the database engine doesn’t copy the indexes from the disk again.
As all the indexes are buffered, the query processor uses the indexes to find the desired data rows in the data files.
Following are some enhancements that are done in Database engine for SQL server 2008:
Compressed backups:
SQL server 2008 supports backup compression. These backup can then be restored on any edition of SQL 2008
Instance of SQL acting as server:
This is a method to administrate multiple servers simultaneously. SQL server dedicates one of its instances to work as a configuration server.
Makes synchronization easy:
SQL Server 2008 gives leverage to the application to track user table’s incremental changes; this makes it easy to develop synchronization in applications.
CPU Addition:
You can add CPU ton SQL server 2008 on a running system. This includes hardware changes, logical changes and online changes. SQL 2008 supports all these changes in a running environment.
Now let’s view the properties of this service in SQL server configuration manager:
You can explore that the logon tab and the service tab contains the same properties as we saw for previous services.
Now let’s click on the advance tab:
Now then this one does contain some different properties. Let’s explore them:
Advance Tab:
Clustered
This indicated whether the service is installed as a resource for clusters or not.
Customer Feedback Reporting
Indicates whether Service Quality Monitoring has been enabled on this service.
Data Path
Displays the path to the SQL Server binaries for this installation of SQL Server.
Dump Directory
Contains the location where memory dumps are placed in case of an error.
Error Reporting
If Error reporting is set to yes this will sent or try to send the error that occurs in this service to Microsoft.
File Version
Shows the version of the SQL Server executable file.
Install Path
Shows the path to the SQL Server folder for this installation of SQL Server.
Instance ID
Shows the SQL Server instance that is using this service.
Language
Indicates the default language for server messages.
Registry Root
Indicates the location of the registry keys used by this application.
Service Pack Level
Indicates or contains the service pack level of this instance of SQL Server.
SKU Name
It is the product stock keeping unit (SKU), also called the product edition.
Startup Parameters
Lists any startup parameters that are used by this instance of SQL Server. Parameters are separated by semi-colons. The default parameters include the paths to the data file for the master database (master.mdf), the log file for the master database (mastlog.ldf), and the error log file.
Stock Keeping Unit
Shows the SKU number.
Version
Shows the version number of this instance of SQL Server.
Virtual Server Name this is give when SQL Server is installed on a clustered server.
Conclusion:
This article discuss in detail the basic SQL services and the difference they posses in SQl server 2005 and 2008. There isn’t much difference as far as basic services, graphical interface and properties are concerned, however some performance related measures are taken to make SQL server 2008 more stable of which few are discussed here.
