Managing SQL Server Services with PowerShell

Introduction

PowerShell provides a command-line shell and scripting language (built in the .NET Framework) especially designed for administrative task automation and configuration management. PowerShell includes commands (called cmdlets) to administer SQL Server along with having cmdlets for administering other Microsoft server products. This gives administrators a common scripting language across servers’ platforms.

As PowerShell provides many more capabilities than simply administering SQL Server, it’s not possible to cover everything in one article. This article is going to talk about how, with the help of PowerShell, you can manage Windows Services related to SQL Server either on a local machine or remote machine.

Identifying SQL Server Services

Get-Service is PowerShell cmdlet that retrieves a list of Windows services either from a local machine or from a remote machine.

Please ensure the PowerShell command prompt or PowerShell ISE tool is launched with Administrator elevated permissions to execute these cmdlets in order to access Windows services and manage it. Also you need to be an administrator on the target machine to manage Windows services.

# Returns all windows services   on local machine
Get-service
# Returns all windows services   on "MySQLBox" remote machine
Get-service -computername MySQLBox
# Returns all windows services   on local machine which are in running state
Get-service | where-object {$_.Status -eq "Running"}    

You can apply filters with Get-Service cmdlets to retrieve only services that contain some specific words in its name. For example, the script below produces a list of Windows services from the local machine, which contain “SQL” in its name (all the services related to SQL Server).

# Returns all windows services   on local machine which contains SQL word in its display name 
Get-service *SQL*
# Returns all windows services   on local machine which contains SQL word in its display name and which are in   running state
Get-service *SQL* | Where-Object {$_.status -eq   "Running"} 

Starting and Stopping SQL Server Services

You can use Start-Service PowerShell cmdlets to start a Windows service on a local or remote machine. With this script you can start SQL Server related services.

# Start SQL Server Database   engine service (default instance)
Start-Service -Name 'MSSQLSERVER' 
# Start SQL Server Database   engine service (named instance CONTOSO)
Start-Service -Name 'MSSQL$CONTOSO' 
 
# Start SQL Server Integration   Services on SQL Server 2012 box
Start-Service -Name 'MsDtsServer110' 
# Start SQL Server Integration   Services on SQL Server 2008 and 2008 R2 box
Start-Service -Name 'MsDtsServer100' 
 
# Start SQL Server Analysis   services engine service (default instance)
Start-Service -Name 'MSSQLServerOLAPService'   
# Start SQL Server Analysis   services engine service (named instance CONTOSO)
Start-Service -Name 'MSOLAP$CONTOSO' 
 
# Start SQL Server Reporting   Server service (default instance)
Start-Service -Name 'ReportServer' 
# Start SQL Server Reporting   Server service (named instance CONTOSO)
Start-Service -Name 'ReportServer$CONTOSO' 
 
# Start SQL Server SQL Server   Agent service (default instance)
Start-Service -Name 'SQLSERVERAGENT'
# Start SQL Server SQL Server   Agent service (named instance CONTOSO)
Start-Service -Name 'SQLAgent$CONTOSO' 

Likewise, you can use Stop-Service PowerShell cmdlets to stop a running Windows service on a local or remote machine.

# Stop SQL Server Database   engine service (default instance)
Stop-Service -Name 'MSSQLSERVER' 
# Stop SQL Server Database   engine service (named instance CONTOSO)
Stop-Service -Name 'MSSQL$CONTOSO' 
 
# Stop SQL Server Integration   Services on SQL Server 2012 box
Stop-Service -Name 'MsDtsServer110' 
# Stop SQL Server Integration   Services on SQL Server 2008 and 2008 R2 box
Stop-Service -Name 'MsDtsServer100' 
 
# Stop SQL Server Analysis   services engine service (default instance)
Stop-Service -Name 'MSSQLServerOLAPService'   
# Stop SQL Server Analysis   services engine service (named instance CONTOSO)
Stop-Service -Name 'MSOLAP$CONTOSO' 
 
# Stop SQL Server Reporting   Server service (default instance)
Stop-Service -Name 'ReportServer' 
# Stop SQL Server Reporting   Server service (named instance CONTOSO)
Stop-Service -Name 'ReportServer$CONTOSO' 
 
# Stop SQL Server SQL Server   Agent service (default instance)
Stop-Service -Name 'SQLSERVERAGENT'
# Stop SQL Server SQL Server   Agent service (named instance CONTOSO)
Stop-Service -Name 'SQLAgent$CONTOSO' 

Stop-Service cmdlets might fail if you try to stop a service on which another service is dependent. For example, SQL Server Agent service is dependent on SQL Server database engine service and hence if you try stopping SQL Server database engine service without first stopping SQL Server Agent service the command will fail. In this case, either you first need to stop SQL Server Agent service then stop SQL Server database engine service or else use –Force parameter to forcefully stop all the dependent services.

# Stop SQL Server Database   engine service (default instance) along with dependent service
Stop-Service -Name 'MSSQLSERVER' -Force
# Stop SQL Server Database   engine service (named instance CONTOSO) along with dependent service
Stop-Service -Name 'MSSQL$CONTOSO' -Force 

Please note, based on the SQL Server installation type (default or named) the name of the service may vary and hence you need to provide the right service name when starting or stopping the service name. For example, with default installation service name for SQL Server database engine is MSSQLSERVER but suppose you have a named instance called CONTOSO the service name will be MSSQL$CONTOSO. You can find details about SQL Server services here.

Managing SQL Server Services on a Remote Machine

As an administrator you might be responsible for maintaining several SQL Server machines. Now consider a scenario where you are required to change the domain user account for SQL Server related services on all of these machines or if you want to update the domain user account or its password for all of these services (on the local or on the remote). Do you really need to log on to each machine and make changes? Not exactly, with the help of PowerShell scripting you not only can start and stop the services but can also change other properties like log-on user account or password, startup type, etc.

With the script below I am first creating a remote session with MySQLBox machine and then executing commands to change the log-on user account name and password.

#Create a new remote PowerShell   session and pass in the scrip block to be executed
$session = New-PSSession -ComputerName MySQLBox   -Credential Domain01User01   
$UserName = "" # specify user Name here
$Password = "" # specify Password here 
 
Invoke-Command -Session $session -ArgumentList $UserName, $Password   -Scriptblock { 
       param($UserName,   $Password)
    # Start SQL   Server Database engine service (default instance)
       $Svc   = Get-WmiObject   win32_service -filter   "name='MSSQLSERVER'"
       $Svc.Change($Null, $Null, $Null, $Null, $Null, $Null, $UserName, $Password)
       Stop-Service   -Name 'MSSQLSERVER'   -Force
    Start-Service   'MSSQLSERVER'
    # Start SQL   Server Integration Services on SQL Server 2012 box
       $Svc   = Get-WmiObject   win32_service -filter   "name='MsDtsServer110'"
       $Svc.Change($Null, $Null, $Null, $Null, $Null, $Null, $UserName, $Password)
       Stop-Service   -Name 'MsDtsServer110'   -Force
    Start-Service   'MsDtsServer110'
    # Start SQL   Server Analysis services engine service (default instance)
       $Svc   = Get-WmiObject   win32_service -filter   "name='MSSQLServerOLAPService'"
       $Svc.Change($Null, $Null, $Null, $Null, $Null, $Null, $UserName, $Password)
       Stop-Service   -Name 'MSSQLServerOLAPService'   -Force
    Start-Service   'MSSQLServerOLAPService'
    # Start SQL   Server Reporting Server service (default instance)
       $Svc   = Get-WmiObject   win32_service -filter   "name='ReportServer'"
       $Svc.Change($Null, $Null, $Null, $Null, $Null, $Null, $UserName, $Password)
       Stop-Service   -Name 'ReportServer'   -Force
    Start-Service   'ReportServer'
    # Start SQL   Server SQL Server Agent service (default instance)
       $Svc   = Get-WmiObject   win32_service -filter   "name='SQLSERVERAGENT'"
       $Svc.Change($Null, $Null, $Null, $Null, $Null, $Null, $UserName, $Password)
       Stop-Service   -Name 'SQLSERVERAGENT'   -Force
    Start-Service   'SQLSERVERAGENT'
} 
 

After changing the log-on user account name and password you need to stop and start or restart the service in order for the change to take effect.

Conclusion

PowerShell provides a command-line shell and scripting language (built in the .NET Framework) especially designed for administrative task automation and configuration management. In this article, I demonstrated how, with the help of PowerShell, you can manage Windows services related to SQL Server either on a local machine or on a remote machine.

Resources

Getting Started with Windows PowerShell

SQL Server PowerShell

See all articles by Arshad Ali

Arshad Ali
Arshad Ali
Arshad Ali works with Microsoft India R&D Pvt Ltd. He has 8+ years of experience, mostly on Microsoft Technologies. Most recently, as a SQL Developer and BI Developer he has been working on a Data Warehousing project. Arshad is an MCSD, MCITP: Business Intelligence, MCITP: Database Developer 2008 and MCITP: Database Administrator 2008 certified and has presented at several technical events including SQL-School. On an educational front, he has an MCA (Master in Computer Applications) and MBA-IT. Disclaimer : I work for Microsoft and help people and businesses make better use of technology to realize their full potential. The opinions mentioned herein are solely mine and do not reflect those of my current employer or previous employers.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles