Introduction

Starting or stopping SQL Server isn’t an everyday occurrence but when you need to, you have several options:

  • Right-click on the instance in the Object Explorer pane of SSMS. (easiest)
  • Open the Services app, choose Action->Connect to another computer, connect to the server hosting SQL and scroll down to get to the SQL Server service.
  • Use PowerShell.

The first two option are fine if you’re only working with one or two servers. If you’re working with more or if you need to schedule it, PowerShell is your friend.

Services on Standalone Instances

To see the status of a service run 

    
        
Get-Service -Name MSSQLServer
# if it's a named instance you may need to hunt for the name first
Get-Service | Where-Object {$_.Name -like '*SQL*'}
The dbatools module has a command for it as well that adds some convenience to the task. Get-DbaService will return only SQL related services. If you only want a particular type, use the -Type parameter. There are multiple types supported but the two I use the most are “Agent” for the SQL Agent and “Engine” for the SQL instance.

Now that you know how to find your service you need to control it. The Get commands will output a PowerShell object representing the service that then gets passed to a control command via “|”.

Using Get-Service and piping to the matching Start/Stop/Restart-Service: 

    
        
# First stop dependencies.
Get-Service -Name MSSQLServer -DependentServices | Stop-Service 
# stop SQL
Get-Service -Name MSSQLServer | Stop-Service 
# start SQL
Get-Service -Name MSSQLServer | Start-Service 
# alternatively use restart instead of stop/start
Get-Service -Name MSSQLServer | Restart-Service 
# start dependencies
Get-Service -Name MSSQLServer -DependentServices | Start-Service
Using Get-DbaService and piping to the matching Start/Stop/Restart-DbaService: 
    
        
# stop sql agent
Get-DbaService -Type Agent | Stop-DbaService
# stop SQL
Get-DbaService -Type Engine | Stop-DbaService
# Restart-DbaService is an option
# start SQL
Get-DbaService -Type Engine | Start-DbaService
# start sql agent
Get-DbaService -Type Agent | Start-DbaService
An important note here is that Get-Service and Get-DbaService return different types of PowerShell objects so they are not mix and match. The dbatools command returns a CimInstance object and the builtin Get-Service returns a ServiceController object. This means you CANNOT run Get-Service -Name MSSQLServer | Stop-DbaService etc. What you CAN do is something like this Get-Service -Name (Get-DbaService -Type Engine).ServiceName

Services on Clusters

If you are wanting to stop SQL on a cluster with out triggering a failover use the Get-ClusterResource command. 

    
        
# stop sql agent
Get-ClusterResource |Where-Object { $_.ResourceType -eq "SQL Server Agent" }| Stop-ClusterResource;
# stop sql
Get-ClusterResource | Where-Object { $_.ResourceType -eq "SQL Server" }| Stop-ClusterResource;

# start sql
Get-ClusterResource | Where-Object { $_.ResourceType -eq "SQL Server" }| Start-ClusterResource;
# start sql agent
Get-ClusterResource |Where-Object { $_.ResourceType -eq "SQL Server Agent" }| Start-ClusterResource;

Summary

Hopefully you can see a way to use some combination of these commands in your environment to automate your workflow. I recently used these commands to shutdown SQL and SQL Agent on around 100 server and clusters for a maintenance window in just a few minutes.

Thanks for reading.