A service is a program that runs in the background that does not require any user interaction. Some examples of services are the drivers for your keyboard and mouse, your antivirus software, and the SQL Server Service.
The SQL Server Service is the executable process that IS the SQL Server Database Engine. The Database Engine can be the default instance which will be listed as: SQL Server (MSSQLSERVER) (limit one per computer), or the Database Engine can be one of many named instances listed as: SQL Server (instance_name). The SQL Server Service is not dependent on any other services to run. SQL Services can be configured to run as a domain user, local user, managed service accounts, virtual accounts, or built-in system account. For instances that do not require access to external resources, Microsoft recommends configuring SQL Server Service and SQL Server Agent to use the default virtual account, otherwise, it is recommended to use a Managed Service Account with the least privileges required. However, since only one instance of SQL Server Browser can run per server, it runs under a Local Service account by default. For more information about account permission and best practices, please see the following article: Configure Windows Service Accounts and Permissions.
Example of SQL Server Configuration Manager showing the installed SQL Services and differentiating between the default SQL instance MSSQLSERVER and a named instance DEVELOPMENT
The SQL Agent Service is responsible for running scheduled tasks and jobs. By default, SQL Agent Service is set to start manually. However, since it is often relied upon by scheduled maintenance, backup, and monitoring tasks, it is recommended that this service is set to start automatically. The SQL Agent Service is dependant on the SQL Server Service and therefore cannot run if the SQL Server Service is stopped. If the SQL Server Service is stopped manually, SQL Agent Service will not automatically start when SQL Server Service is manually started. In this specific scenario, SQL Agent Service will also need to be started manually.
SQL Browser Service listens to incoming client requests for SQL resources and provides connection information to the default and any additional named instances that are running on a particular machine. The SQL Browser Service acts like a DNS Server for SQL. It allows incoming client connections to request information on UDP port 1434 and then the Browser Service provides the specific TCP/IP port or named pipe information back to the client. Because the SQL Browser Service resolves issues that are caused by a variety of scenarios like multiple network cards, statically assigning a port to a SQL Server only to have a different service request that port first, or having dynamically assigned ports, for customized port configurations or servers running multiple instances, I highly recommend keeping this service enabled and set to start automatically.
Unlike the SQL Server Service which will have services running for each instance, only one SQL Browser Service is required to provide the necessary connection information for all of the instances on a machine. However, the SQL Browser Service is only able to provide information about instances running on its particular machine. It can not provide information about other SQL Servers that are running on different machines within the same network.
Three different ways to change the status of SQL Services
Below are examples of how to Start, Pause, Stop, and Restart the SQL Services using SQL Server Configuration Manager, the Command Prompt, and SQL Server Management Studio. While these tasks can be completed with SQL PowerShell and T-SQL statements, I chose the prior three methods because I felt they were easier to use when it came to applying these commands to named instances.
(Warning: While it is possible to start and stop the services using the services.msc, it is against best practices and may lead to database corruption.)
Local server administrators group membership is required by default to Start, Pause, Stop, or Restart services using SQL Server Configuration Manager, SQL Server Management Studio, or the Command Prompt using net commands. In situations where admin access to the OS has been lost, or granting server administrator privileges is not preferred, please consult you Systems Administrator and consider the alternatives presented in the following article: How to Start or Stop SQL Services without OS Admin Rights.
Using SQL Server Configuration Manager
Best Practice: Use the SQL Server Configuration Manager when making any changes to the SQL Services. It ensures that all of the changes made to the SQL Services get propagated to all of the necessary registry entries and applies any necessary permissions when changing the account the service is running under. SQL Server Configuration Manager also provides a validation check if changes are made to the service account used to start SQL Server Service. This prevents an invalid account being assigned to the SQL Server Service which will block the service from starting.
To use the SQL Server Configuration Manager, select the appropriate file for your SQL Server version and Run as Administrator:
SQL Server 2016 C:\Windows\SysWOW64\SQLServerManager13.msc
SQL Server 2014 C:\Windows\SysWOW64\SQLServerManager12.msc
SQL Server 2012 C:\Windows\SysWOW64\SQLServerManager11.msc
SQL Server 2008 C:\Windows\SysWOW64\SQLServerManager10.msc
Example of SQL Server Configuration Manager with a default instance (MSSQLSERVER) and a named instance (Development).
Using the Command Prompt with net Commands
Caution: While it is possible to change the service’s status through the command prompt, it is best practices to use the SQL Server Configuration Manager.
Run the Command Prompt as the local administrator
For Named instances
Use the same Command Prompt commands, but replace MSSQLSERVER with the instance name (i.e. net start “SQL Server (Development)” ).
Using SQL Server Management Studio
Example of SQL Server Management Studio connected to the default instance and the “Development” named instance.
There are some limitations to using SSMS for managing the state of the SQL Services, so again I highly recommend using SQL Server Configuration Manager when making changes.
Starting
SQL Server Configuration Manager
- Right-click the instance you want to start and select “Start”.
- Note: If you manually start the SQL Server service, you will need to independently start the SQL Server Agent. Hint: Since SQL Server Agent is dependent on SQL Server Service, starting SQL Server Agent will start both services.
Command Prompt with net Commands
- To start the SQL Server Service enter the following command :
- net start “SQL Server (MSSQLSERVER)”
- To start the SQL Agent Service enter the following command:
- net start “SQL Server Agent (MSSQLSERVER)”
SQL Server Management Studio
NOTE: The only way to start a SQL Server instance within SSMS is if SSMS was connected to the instance prior to it being stopped.
- Right-click the instance you want to start and select “Start”
- Click yes on the pop-up message to confirm that you want to Start the SQL Server Service
- After the SQL Server Service is started, right-click the SQL Server Agent and select “Start”
- Click yes on the pop-up message to confirm that you want to Start the SQL Server Agent Service.
Pausing
A special note about Pausing the SQL Server Service
Pausing the SQL Server Service allows current connections to stay active, but it prevents new connections. This is beneficial when you want connected users to be able to finish their tasks prior to stopping the SQL Server Service.
SQL Server Configuration Manager
- Right-click the instance you want to pause and select “Pause”.
Command Prompt with net Commands
- net pause “SQL Server (MSSQLSERVER)”
SQL Server Management Studio
- Right-click on the instance and select “Pause”.
- Click yes on the pop-up message to confirm that you want to Pause the SQL Server Service.
Resuming
SQL Server Configuration Manager
- Right-click on the instance and select “Resume”.
Command Prompt with net Commands
- net continue “SQL Server (MSSQLSERVER)”
SQL Server Management Studio
- To Resume SQL Server Service, right-click the instance and select “Resume”.
Stopping
SQL Server Configuration Manager
- Right-click the instance you want to stop and select “Stop”.
- Note: Stopping the SQL Server service will also stop the corresponding SQL Server Agent.
Command Prompt with net Commands
- net stop “SQL Server (MSSQLSERVER)”
- You will be notified that SQL Server Agent will also be stopped and prompted to continue. To continue enter: y
- To stop just the SQL Agent Service enter the following command:
- net stop “SQL Server Agent (MSSQLSERVER)”
SQL Server Management Studio
- Right-click on the instance and select “Stop”.
- Click yes on the pop-up message to confirm that you want to Stop the SQL Server Service.
- Click yes on the pop-up message to confirm that you want to stop the SQL Server Agent Service.
- Note: SQL Server Agent Service can be stopped independently from SQL Server Service by right-clicking SQL Server Agent and selecting “Stop”.
- Click yes on the pop-up message to confirm that you want to stop the SQL Server Agent Service.
Restarting
SQL Server Configuration Manager
- Right-click the instance you want to restart and select “Restart”.
- Restarting the SQL Server service will also restart the SQL Server Agent.
Command Prompt with net Commands
(Stopping SQL Server Service using the Command Prompt will stop the SQL Agent Service. For the purposes of restarting SQL, both the SQL Server Service and SQL Agent Service will have to be individually started.)
For the Default Instance
- net stop mssqlserver – enter ‘y’ to acknowledge that SQL Agent Service will be shut down.
- net start sqlserveragent (This will start both the SQL Server Service and the SQL Agent Service.)
- (To start just the SQL Server Service use: net start mssqlserver)
For Named Instances
- net stop MSSQL$Named_Instance_Here
- Net start SQLAgent$Named_Instance_Here
- (To start just the SQL Server Service use: net start MSSQL$Named_Instance_Here)
SQL Server Management Studio
- Right-click on the instance and select “Restart”.
- Click yes on the pop-up message to confirm that you want to restart the SQL Server Agent Service.
- Click Yes to acknowledge that SQL Server Agent Service will be restarted as well.