When we install the SQL Server, it also installs the following services:
- SQL Server database engine service to manage and access data in SQL Server.
- SQL Server Integration Service for the ETL process – we install it when including Integration services during setup.
- SQL Server Reporting Service to manage the SQL Server reports.
- The SQL Server Agent Service. Note that it is not available in the SQL Server Express edition.
In this article, we are going to learn the following methods to manage SQL Server services:
- Start, stop, and restart SQL Server services using the SQL Server configuration manager.
- Start, stop, and restart SQL Server services using Services MMC [Microsoft Management Console].
- Start, stop, and restart SQL Server services using the PowerShell script.
- Start, stop, and restart SQL Server services in Ubuntu Linux.
- Start, stop, and restart SQL Server services using the Windows server failover cluster manager.
Let us examine all these methods.
SQL Server Configuration Manager
SQL Server Configuration Manager is a tool for SQL Server services managing, included in the SQL Server software package. When we install SQL Server, this manager gets installed automatically.
To manage the services, launch the SQL Server configuration manager and Right-click on MSSQLSERVER. In a dialog box, you will see the options to start, stop, and restart the services.
If the process timeout or any other error occurs, it informs the user about the problems:
Error details are available in Event Viewer or ErrorLog of the SQL Server.
You can use SQL Server Management Studio can restart services.
Launch SSMS -> Connect to the SQL Server instance -> Right-click on Hostname.
It opens a context menu with the start, stop, pause, and restart options:
Services MMC [Microsoft Management Console]
To manage SQL Server settings with Services MMC [Microsoft Management Console], do the following:
Open Control Panel -> Administrative tools -> Click on Services -> Find MSSQLSERVER -> Right-click on it.
In a dialog box that opens after right-clicking, you get the options to start, stop, and restart the services:
Alternatively, you can run the Services.msc command to open Services MMC.
PowerShell Commands
To start and stop any service, use Start-Service and Stop-Service command.
Launch PowerShell and execute the following command to start the services:
PS C:\WINDOWS\system32> Start-Service MSSQLSERVERTo view the SQL Server service status, open SQL Server Configuration Manager and click on SQL Server Service:
The following command stops the service:
PS C:\WINDOWS\system32> Stop-Service -Force MSSQLSERVERFinally, the command to restart the SQL Server service is as follows:
PS C:\WINDOWS\system32> Restart-Service -Force MSSQLSERVERManage services in Ubuntu Linux
In Ubuntu Linux, we must use the systemctl command to manage SQL Server service. It is a Linux command for controlling the service manager and systemd system.
To illustrate this option, I have installed SQL Server 2019 on Ubuntu Linux. Note: The root user must be used to manage the services. If you log in as a different user, execute the following command in the terminal:
nisarg@LinuxSQL01:~$ sudo -iRun the below command to stop the service:
root@LinuxSQL01:~# systemctl stop mssql-server.serviceWe do not receive any feedback on the terminal to verify the service status. Thus, to check it, run the command:
root@LinuxSQL01:~# systemctl status mssql-server. serviceThe following command starts the SQL Services:
root@LinuxSQL01:~# systemctl start mssql-server.serviceTo view the status, run the below command:
root@LinuxSQL01:~# systemctl status mssql-server.serviceThe following command is used to restart the service:
root@LinuxSQL01:~# systemctl restart mssql-server.serviceThe Windows Server failover cluster manager
Managing SQL Server services on the stand-alone instance and in the failover cluster environment is different. We can use any of the above methods, but, according to Microsoft’s best practices, we should use the Windows Failover Cluster Manager.
When we install an SQL Server on a clustered environment, an SQL Server role is created. We can view this role:
Open the Failover Cluster Manager -> Click on Roles -> Right-click on the MSSQLSERVER role.
A context menu opens. Hover on Move and select Best Possible Node:
Once resources are moved to the secondary node, open the SQL Server Configuration Manager to verify that the service has been stopped on SQL01 Node. As you can see, it’s done correctly:
Summary
This article presented five different methods to manage SQL Server services using different means and in different environments.
[Visited 1,166 times, 1 visits today]
Tags: database administration, database management, sql server services Last modified: September 18, 2021Prerequisites
When you start the SQL Server you actually start the Windows service SQL Server and the corresponding process sqlservr.exe.
In addition to starting the server, you also need to start the SQL Server Agent to be able to schedule and execute jobs within the SQL Server Management Studio. Other services offered by the SQL Server are not required in the SAP environment and should therefore not be started.
Overview of SQL Server Services
SQL Server | sqlservr.exe | Always required |
SQL ServerAgent | sqlagent.exe | Always required |
Distributed Transaction Coordinator | msdtc.exe | Do not run in the SAP environment |
FDHOST Launcher | fdhost.exe | Do not run in the SAP environment |
Note
You can view a list of all installed services and their current status in the Windows Services application.
You can start the SQL Server using the SQL Server Services manager or the SQL Server Management Studio.
Procedure
- Start the SQL Server Configuration Manager.
- Select SQL Server Services.
- Select the service SQL Server [MSSQLServer].
- Choose either start or stop as required.
The server is started up or shutdown immediately.
During startup, an automatic recovery takes place.
During shutdown a checkpoint is performed in every database.
Caution
Never use Pause for a server. This option prevents any further connections being established to the server and may cause SAP errors.
- Repeat the procedure for the service SQL Server Agent [MSSQLServer].
The SQL Server Agent must be started to allow the scheduling of tasks in the SQL Server Management Studio.
Note
Optionally, SQL Server and SQL Server Agent can be started automatically whenever Windows starts. If you wish to implement this option, use the Windows Services application to specify that the services should be started together with Windows. For more information, see the Windows documentation.