Controlling Windows and SQL Server Services using Windows PowerShell

Windows services are programs that run in the background to perform specific functions. Many services start when the Windows operating system is booted to perform their operation. We normally manage Windows services by using the service management console, which can be launched by selecting Start > Run > and typing services.msc. Once you press enter a service management console will appear with a list of services on your server. This is the place from where you generally manage Windows services.

NOTE:-Always run PowerShell prompt in "Run As Administrator" mode for administrative tasks.

Getting a List of Windows Services

Get-Service: We can use this cmdlet to lists all Windows services on your local system or remote system.

--List all windows services on local machine.
Get-service


--list all windows services of a remote computer.
Get-Service -computername SERVERNAME

Filtering Windows or SQL Server services

We can apply filters as well to display a specific service. Suppose we want to see all services whose name begin with "SQL" or a specific service like MSSQLSERVER. We can use the -displayname parameter to display all services whose display name contains "SQL" And we will use -Name parameter to get a specific service like MSSQLSERVER.

--Display all services whose name contains SQL.
Get-service -displayname "*SQL*"


--Display a specific service.
Get-Service -Name MSSQLSERVER

We can also filter the output of these cmdlets. We can use another cmdlet where-object to filter any logs. Suppose we want to check all SQL server services which are in running state. We can add both cmdlets with the help of a pipe and filter as shown below. The output of the first cmdlet (before pipe) is saved in $ and we can use $ to access the output.

--Display all services whose status is in running state.
Get-service | Where-Object {$_.status -eq "Running"}


--Display all SQL Server services which are in running state.
Get-service -displayname *SQL* | Where-Object {$_.status -eq "Running"}


--Display all SQL Server Services which are in stopped state.
Get-service -displayname *SQL* | Where-Object {$_.status -eq "stopped"}

As we can see in the above screenshot, we can get all services which are in stopped state in the same way. I did not include a screenshot for the first set of code because the list was too large.

Starting and Stopping Services

To stop a service we use the Stop-Service cmdlet.

Suppose we want to stop one of the SQL Server services. First run get-service to list SQL services to check the status then stop it as shown below. If you want to shutdown all SQL Server services then we can also do this with the help of filters.

--Check the status of a SQL Server service.
Get-Service -Name SQLserveragent


--Stop above service.
Stop-Service -Name Sqlserveragent


--Stop all SQL Server services in one go.
Stop-Service -displayname *SQL* -force

Sometimes a few services will fail to stop by normal Stop-service cmdlet with the below error.

Stop-Service : Cannot stop service 'SQL Server (MANVENDRA) (MSSQL$MANVENDRA)' because it has dependent services. It can
 only be stopped if the Force flag is set.
At line:1 char:13
+ stop-service <<<<  -displayname *sql*
    + CategoryInfo          : InvalidOperation: (System.ServiceProcess.ServiceController:ServiceController) [Stop-Serv
   ice], ServiceCommandException
    + FullyQualifiedErrorId : ServiceHasDependentServices,Microsoft.PowerShell.Commands.StopServiceCommand
 

This means one or more services are dependent on this service, so to avoid this issue either we can stop all dependent services or we can use the -FORCE parameter along with Stop-Service as shown in the above screenshot.

Stopping services could create an issue, so to be safe we can add a few parameters to get a confirmation screen about what we will be performing.

Here we will add two parameters. The first is -whatif and second is -confirm. If you use -whatif it will display a preview about what you are going to do and if you are use -confirm it will ask you again to choose Y for yes and N for no to proceed. The below screenshot shows how both parameters work.

--Running cmdlet with -whatif parameter.
Stop-Service -displayname *SQL* -whatif


--Stop all SQL Server services which are in a running state with -confirm.
Get-service -displayname *SQL* | Where-object {$_.status -eq "running"} |Stop-Service -confirm

To start a service we use the Start-Service cmdlet.

Starting a service is the same as stopping a service. Just use Start-Service in place of Stop-Service and run with all parameters which we have used above.

Below are a few cmdlets which start a service.

--Start the service.
Start-service -Name SQLSERVERAGENT


--Check the status of the started service.
Get-Service -Name SQLSERVERAGENT


--Get a preview of Starting all SQL Server Services which is not started.
Get-Service -displayname *SQL* | where-object {$_.Status -eq "Stopped"} |start-service -whatif


--Starting all SQL Server Services which are not started.
Get-Service -displayname *SQL* | where-object {$_.Status -eq "Stopped"} |start-service -confirm


--Check the status.
Get-service -displayname *SQL*

I am not attaching screenshots since this is very similar to the stop-service cmdlet which were shown in the above screenshots.

We can also restart a services with the help of PowerShell cmdlets.

--Restart the services.
restart-service -name sqlserveragent


--Restart all SQL Server Services.
restart-service -displayname *SQL* -Force

Changing Configurations of Windows/SQL Server services

To change a configuration we can use Set-service.

This cmdlet will change the configuration of a service like startup types, display name, etc...

--Check the startup type of SQL Server Agent service.
 get-wmiobject win32_service | where {$_.Name -eq "sqlserveragent"}


--Change the startup type of SQL Server agent service to Automatic.
set-service sqlserveragent -startup automatic

You can again check whether the configuration is changed or not. See the below screenshot of the outputs for the above cmdlets.

Below are few more cmdlets which can be used to alter the configuration of any service. You can change the display name of a service, you can change the description of a service or you can start or stop a service with the set-servicecmdlet.

--Change the display name of SQL Server Agent service (use same method for any windows service).
set-service -name sqlserveragent -DisplayName "Microsoft Sql Server Agent"


--Change the description of SQL Server agent service.
set-service -name Sqlserveragent -description "SQL Server Agent Service to handle agent related tasks."


--You can also start or pause the services with the help of this cmdlet. 
--Suppose a SQL Server Agent service is stopped, we can start it by running.
set-service sqlserveragent -status running

Next Steps

• Try to use PowerShell cmdlets in your day to day activities. PowerShell is becoming more popular for SQL Server support on Windows core edition.
• Read more tips on PowerShell.