Introducing various SQL Server Services. Most businesses today need software such as SQL Server to store their data and information. So it can be said that learning how to work with this kind of software will bring you a good career future.
In this article, we will introduce the most important Sql server services and explain the differences between them.
Choose your perfect Windows Virtual Private Server Packages in Eldernode
Table of Contents
1- Engine or main SQL Server service
This service is the most important service or Feature of SQL Server that even other services depend on it.
This service handles the core of data processing (tasks such as adding, editing, deleting, updating and optimizing).
2- Agent service
Agent is another SQL Server service that is responsible for executing jobs, alerts and sending emails.
Jobs are also one of the SQL Server objects that automatically perform operations such as backup and rebuild indexes, etc. at different time intervals.
For example, you define a job that automatically gets a Full Backup every night at 11pm. This service is part of Instance Features services. This means that for each instance, a SQL Server Agent service will be installed on your machine.
3- Browse service
Another SQL Server service is the SQL Server Browser service.
This service allows you to find out on which machines SQL Server is installed. This service is one of the Shared Features services. This means that for all instances, a SQL Server Browser service will be installed on your machine.
4- Full Text Search service
SQL Full-Text Search service is another SQL Server service that allows you to index and search textual data based on keywords in SQL Server.
This service is part of Instance Features services and is installed once per instance.
Note: The Full Text Index is used to store words and their location in a column.
5- SSIS service
SSIS is one of the Shared Services and the most powerful tools in the field of ETL.
SQL Server Integration Services is a tool used to perform ETL operations such as extracting, transforming and loading data.
Note: ETL stands for Extract Transform and Load.
During the ETL process, data is extracted from required information sources in the organization or outside it, such as databases, text files, legacy systems, and spread sheets, and converted into information that is compatible with a particular format.
They are then stored in a repository, often a data warehouse.
6- SSAS service
Another SQL Server service is SQL Server Analysis Services.
SQL Server includes a very powerful engine for building multidimensional data structures that allows you to sort, aggregate, and analyze data.
This service is part of Instance Features services and is installed once per instance.
7- Reporting Service (SSRS)
Another SQL Server service is SQL Server Reporting Services.
This service allows users and managers of organizations and companies to design reports and create very accurate reports and very complex management dashboards.
Note that in SQL Server 2017 and later versions, this service will be installed separately.
Important Note: When installing SQL Server as shown below, you can install the required services according to your needs. Unfortunately, some users click on the Select All button to select the required services and install all the services. Due to the fact that these services reduce the performance of your system, in other words, it imposes additional load on the server, so select and install them according to your needs. Of course, some users install all the services, but in the end, they activate only the ones they really need and disable the rest.
In the figure above, if you just select the Database Service Engines option on your machine or computer, three services called SQL Server Service, SQL Server Agent Service and SQL Server Browser Service will be activated.
How to view SQL Server services on a computer
There are two ways to view SQL Server services:
1- Enter the Services environment.
To do this, first enter the Run window and there enter the Services.msc command and click the OK button. In this case, you will enter the Services environment.
Note that all SQL Server services start with the word SQL.
It should be noted that every SQL Server that you install on a machine is called an Instance. You can even install fifty SQL Servers on one machine according to your needs.
For example, install SQL Server 20014 as an Instance for a specific task on your machine. Then install SQL Server 2016 on the same machine for another task as another Instance. Then install another SQL Server 2016 on the same machine. You can repeat this up to 50 times.
So far we have installed three Instances on one machine, namely one SQL Server 2014 and two SQL Server 2016.
2- Using the SQL Server Configuration Manager window, you can see only SQL Server services. In the Services.msc window, you see both Windows services and SQL Server services.
To see the SQL Server services, just enter the SQLServerManager15.msc command in the Run window and click the OK button.
If SQL Server 2016 was installed on your machine, then you need to enter the command SQLServerManager13.msc. In other words, that number goes back to your SQL Server version.
In this case, a window will appear as shown below:
*
What are Default Instance and Named Instance?
The first SQL Server software you install on your machine is called the Default Instance. After that, the rest of the instances that you install on your device will be called Named Instance.
How to distinguish Default Instance and Named Instance from each other is that in the Services window, if the word MSSQLSERVER is written in front of the SQL Server service and in parentheses, this service will be presented as Default Instance.
If there is another name in front of the SQL Server service and in parentheses, then that service is installed as Named Instance on that machine.
How to connect to Default Instance and Named Instance
When you want to run SQL Server, after you enter the SSMS command in the Run window and click OK, SQL Server Management Studio will run. The Connect to Server window then appears.
Open the Connect to Server window to connect to the Default Instance. In the Server name field, enter the name of that machine or dot character and click the Connect button.
To connect to Named Instance, just enter the name of that machine or the IP of that machine, then the character (\) and finally the name of Named Instance as shown below:
To find out if SQL Server is installed on a machine, having a Management Studio program is not a sufficient condition for having SQL Server service on a system.
The correct way to ensure the existence of SQL Server on a machine or computer or system is to check it from the Services window.
In the following window, check if the SQL Server service is installed or not:
Note: Before running SQL Server, make sure that the main SQL Server service is Start.
As you can see in the figure below, the main SQL Server service has been stopped.
Just right-click on it and select Start.
Also, see:
Tutorial Install SQL Server 2019 Developer Edition on Windows Server
How to Migrate SQL Databases with Command Line
Dear user, we hope you would enjoy this tutorial, you can ask questions about this training in the comments section, or to solve other problems in the field of Eldernode training, refer to the Ask page section and raise your problem in it as soon as possible. Make time for other users and experts to answer your questions.
Goodluck.
