Types of services in SQL Server

Each service in SQL Server represents a process or a set of processes to manage authentication of SQL Server operations with Windows. This topic describes the default configuration of services in this release of SQL Server, and configuration options for SQL Server services that you can set during and after SQL Server installation. Most services and their properties can be configured by using SQL Server Configuration Manager.

SQL Server Version and its last versions when Windows is installed on the C:\ drive.

VersionPath
SQL Server 2019C:\Windows\SysWOW64\SQLServerManager15.msc
SQL Server 2017C:\Windows\SysWOW64\SQLServerManager14.msc
SQL Server 2016C:\Windows\SysWOW64\SQLServerManager13.msc
SQL Server 2014 (12.x)C:\Windows\SysWOW64\SQLServerManager12.msc
SQL Server 2012 (11.x)C:\Windows\SysWOW64\SQLServerManager11.msc
SQL Server 2008C:\Windows\SysWOW64\SQLServerManager10.msc

Services Installed by SQL Server

ServicesDescription
SQL Server Database ServicesThe service for the SQL Server relational Database Engine. The executable file is \MSSQL\Binn\sqlservr.exe.
SQL Server AgentExecutes jobs, monitors SQL Server, fires alerts, and enables automation of some administrative tasks. The SQL Server Agent service is present but disabled on instances of SQL Server Express. The executable file is \MSSQL\Binn\sqlagent.exe.
Analysis ServicesProvides online analytical processing (OLAP) and data mining functionality for business intelligence applications. The executable file is \OLAP\Bin\msmdsrv.exe.
Reporting ServicesManages, executes, creates, schedules, and delivers reports. The executable file is \Reporting Services\ReportServer\Bin\ReportingServicesService.exe.
Integration ServicesProvides management support for Integration Services package storage and execution. The executable path is \130\DTS\Binn\MsDtsSrvr.exe. Integration Services may include additional services for scale out deployments.
SQL Server BrowserThe name resolution service that provides SQL Server connection information for client computers. The executable path is c:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe
Full-text searchQuickly creates full-text indexes on content and properties of structured and semistructured data to provide document filtering and word-breaking for SQL Server.
SQL WriterAllows backup and restore applications to operate in the Volume Shadow Copy Service (VSS) framework.
SQL Server Distributed Replay ControllerProvides trace replay orchestration across multiple Distributed Replay client computers.
SQL Server Distributed Replay ClientOne or more Distributed Replay client computers that work together with a Distributed Replay controller to simulate concurrent workloads against an instance of the SQL Server Database Engine.
SQL Server LaunchpadA trusted service that hosts external executables that are provided by Microsoft, such as the R or Python runtimes installed as part of R Services or Machine Learning Services. Satellite processes can be launched by the Launchpad process but will be resource governed based on the configuration of the individual instance. The Launchpad service runs under its own user account, and each satellite process for a specific, registered runtime will inherit the user account of the Launchpad.
SQL Server PolyBase EngineProvides distributed query capabilities to external data sources.
SQL Server PolyBase Data Movement ServiceEnables data movement between SQL Server and External Data Sources and between SQL nodes in PolyBase Scaleout Groups.

Start, stop, pause, resume, restart SQL Server services via Windows

SQL Server Services can be managed through Windows Services (services.msc) or SQL Server Configuration Manager or SQL Server Management Studio (SSMS), net commands from a command prompt, Transact-SQL, or PowerShell.

Windows Services (services.msc)

STEP 1 – Go to Run -> type services.msc -> Click Ok.

SQL Server Services
SQL Server Services

STEP 2 – Right click on the respective services to perform Start, Stop, Pause, Resume and Restart.

SQL Server Configuration Manager

STEP 1 – Start -> All Programs -> Microsoft SQL Server -> Configuration Tools -> SQL Server Configuration Manager.

SQL Server Configuration Manager
SQL Server Configuration Manager

STEP 2 – Right click on the respective services to perform Start, Stop, Pause, Resume and Restart.

SQL Server Management Studio (SSMS)

STEP 1 – Start -> All Programs -> MS SQL Server 2012 -> SQL Server Management Studio.

SQL Server Management Studio
SQL Server Management Studio

STEP 2 – In Object Explorer, connect to the instance of the Database Engine, right-click the instance of the Database Engine you want to start, and then click Start, Stop, Pause, Resume, or Restart. Or, in Registered Servers, right-click the instance of the Database Engine you want to start, point to Service Control, and then click Start, Stop, Pause, Resume, or Restart.

Command Prompt Window using net Commands

The Microsoft SQL Server services can be started, stopped, or paused by using Microsoft Windows net commands.

To start the default instance of the Database Engine from a command prompt, enter one of the following commands:

net start "SQL Server (MSSQLSERVER)"
-or-
net start MSSQLSERVER

To start a named instance of the Database Engine. From a command prompt, enter one of the following commands. Replace instancename with the name of the instance you want to manage.

net start "SQL Server (instancename)"
-or-
net start MSSQL$ instancename

To pause or stop services from the Command Prompt window

  • To pause or stop services, modify the commands in the following ways.
  • To pause a service, replace net start with net pause.
  • To stop a service, replace net start with use net stop.

Transact-SQL

To wait for currently running Transact-SQL statements and stored procedures to finish, and then stop the Database Engine, execute the following statement.

SHUTDOWN;

To stop the Database Engine immediately, execute the following statement.

SHUTDOWN WITH NOWAIT;

PowerShell

STEP 1 – In a Command Prompt window, start SQL Server PowerShell by executing the following command.

sqlps

STEP 2 – At a SQL Server PowerShell command prompt, by executing the following command. Replace computername with the name of your computer.

# Get a reference to the ManagedComputer class.
CD SQLSERVER:\SQL\computername
$Wmi = (get-item .).ManagedComputer

STEP 3 – Identify the service that you want to stop or start. Pick one of the following lines. Replace instancename with the name of the named instance.


# default instance of the Database Engine.
$DfltInstance = $Wmi.Services['MSSQLSERVER']
# named instance of the Database Engine.
$DfltInstance = $Wmi.Services['MSSQL$instancename']

STEP 4 – To start and then stop the selected service.

# Start the service.
$DfltInstance.Start();
# Stop the service.
$DfltInstance.Stop();
# Refresh the cache.
$DfltInstance.Refresh();

Manage the SQL Server service on Linux

To start, stop, or restart an instance of the SQL Server Database Engine.

Check the status of the SQL Server service using this command:

sudo systemctl status mssql-server

You can stop, start, or restart the SQL Server service as needed using the following commands:

# Stop the service.
sudo systemctl stop mssql-server
# Start the service.
sudo systemctl start mssql-server
# Restart the service.
sudo systemctl restart mssql-server

 

Published by Abdul Samad

Having 17+ years of extensive experience in IT industry, enabled to enhance the team performance and maximize customer satisfaction by strategically managing calls and implementing process improvements. Demonstrated ability to solve problems, meets challenging goals, and expedites delivery. Skilled MSSQL administrator guide team during the crisis situation. Apply Creative thoughts process in re-designing the workflow system to eliminate duplication of effort and increase productivity.

Leave a Reply