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.
Launch PowerShell and execute the following command to start the services:
PS C:\WINDOWS\system32> Start-Service MSSQLSERVER
To 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 MSSQLSERVER
Finally, the command to restart the SQL Server service is as follows:
PS C:\WINDOWS\system32> Restart-Service -Force MSSQLSERVER
Manage 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 -i
Run the below command to stop the service:
root@LinuxSQL01:~# systemctl stop mssql-server.service
We 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. service
The following command starts the SQL Services:
root@LinuxSQL01:~# systemctl start mssql-server.service
To view the status, run the below command:
root@LinuxSQL01:~# systemctl status mssql-server.service
The following command is used to restart the service:
root@LinuxSQL01:~# systemctl restart mssql-server.service
The 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:
This article presented five different methods to manage SQL Server services using different means and in different environments.
- Create a Database in SQL Server Using SQL Server Management Studio (SSMS) - January 15, 2021
- Understanding SQL Server ALTER TABLE ADD COLUMN Statement - January 11, 2021
- Understanding MySQL TRUNCATE TABLE by Practical Examples - January 5, 2021