PowerShell script to stop SQL Server service

We recently had some issues with a bunch of load balanced VMs, where one of our ESX hosts had died and the other was struggling to take up the slack. As such, I made a decision to take a bunch of SQL Server instances down to alleviate some stress. A bunch being some 40 odd instances across 20 servers.

As is becoming more and more frequent, the inclination to do all this manually wasnt particularly high, so I decided to knock up a quick powershell script to do it all for me.

Powershell has a couple ofvery handy cmdlets: stop-service & start-service. Im sure I dont really need to go into detail of whateach does!However, annoyingly they dont take a remote computer parameter, and whilst you could use them with the invoke-command cmdlet for true remoting, this isnt enabled for quite a number of our servers.

Whats reallyinteresting, however,is that theget-service cmdletdoes take the -Computer switch. So, I can create an object with the results of the get-service cmdlet and then use the .Start() and .Stop() methods on those objects. This also has the added benefit of being able to retrieve the names of the various SQL Server services, including those belong to a named instance, without knowing what the specific name was.

In fact, I decided that the easiest way to achieve my goal was simply to get all the services on the target server, and then filter the services I was interested in stopping:-

#Get all the services on the server $Services = get-service -ComputerName $Server

I then added a bunch of switches of my own, to indicate whether I wanted to stop each type of service (the SQL Engine, the SQL Agent, SSIS, SSAS etc).

For example, the code to stop the SQL Agent is below:-

if($StopAgent -eq $true) { #check the SQL Server Agent services write-verbose "Checking Agent Services" #get all named agent instances and the default instance ForEach ($SQLAgentService in $Services | where-object {$_.Name -match "SQLSERVERAGENT" -or $_.Name -like "SQLAgent$*"}) { #check the servcie running status if($SQLAgentService.status -eq "Running") { #if stopped, start the agent write-verbose "Stopping SQL Server Agent $($SQLAgentService.Name)" $SQLAgentService.Stop() } else { #write comfort message that the service is already running write-verbose "SQL Agent Service $($SQLAgentService.Name) is already stopped." } } } else { write-verbose "Skipping checking Agent services" }

The various services have similiarly configured names, so by looking at the initial get-service output I could determine what the where clause for my foreach loop needed to be quite easily. Once my stop function was created, it was a two minute job to do a find and replace on .stop() to .start(), which (after some grammatical tweaking in the comfort messages) gave me the following two functions, which I could then call in a loop for all the servers I needed to temporarily stop:-

#function to stop all the sql server services on a given server function Stop-AllSQLServerServices { [cmdletbinding()] Param([string]$Server , [bool]$StopSQL=$true , [bool]$StopAgent=$true , [bool]$StopSSRS=$true , [bool]$StopBrowser=$true , [bool]$StopSSIS=$true , [bool]$StopTextDaemon=$true , [bool]$StopSSAS=$true) #Get all the services on the server $Services = get-service -ComputerName $Server if($StopAgent -eq $true) { #check the SQL Server Agent services write-verbose "Checking Agent Services" #get all named agent instances and the default instance ForEach ($SQLAgentService in $Services | where-object {$_.Name -match "SQLSERVERAGENT" -or $_.Name -like "SQLAgent$*"}) { #check the servcie running status if($SQLAgentService.status -eq "Running") { #if stopped, start the agent write-verbose "Stopping SQL Server Agent $($SQLAgentService.Name)" $SQLAgentService.Stop() } else { #write comfort message that the service is already running write-verbose "SQL Agent Service $($SQLAgentService.Name) is already stopped." } } } else { write-verbose "Skipping checking Agent services" } if($StopSSRS -eq $true) { #check the SSRS services write-verbose "Checking SSRS Services" #get all reporting service services ForEach ($SSRSService in $Services | where-object {$_.Name -match "ReportServer"}) { #check the status of the service if($SSRSService.status -eq "Running") { #if stopped, start the agent write-verbose "Stopping SSRS Service $($SSRSService.Name)" $SSRSService.Stop() } else { #write comfort message that the service is already running write-verbose "SSRS Service $($SSRSService.Name) is already stopped." } } } else { write-verbose "Skipping checking SSRS services" } if($StopSSIS -eq $True) { #get the SSIS service (should only be one) write-verbose "Checking SSIS Service" #get all services, even though there should only be one ForEach ($SSISService in $Services | where-object {$_.Name -match "MsDtsServer*"}) { #check the status of the service if($SSISService.Status -eq "Running") { #if its stopped, start it write-verbose "Stopping SSIS Service $($SSISService.Name)" $SSISService.Stop() } else { #write comfort message write-verbose "SSIS $($SSISService.Name) already stopped" } } } else { write-verbose "Skipping checking SSIS services" } if ($StopBrowser -eq $true) { #Check the browser, start it if there are named instances on the box write-verbose "Checking SQL Browser service" #get the browser service $BrowserService = $services | where-object {$_.Name -eq "SQLBrowser"} if($BrowserService.Status -eq "Running") { #if its stopped start it write-verbose "Stopping Browser Server $($BrowserService.Name)" $BrowserService.Stop() } else { #write comfort message write-verbose "Browser service $($BrowserService.Name) is already stopped" } } else { write-verbose "Skipping checking Browser service" } if($StopTextDaemon -eq $True) { # Start the full text daemons write-verbose "Checking SQL Full Text Daemons" ForEach($TextService in $Services | where-object {$_.Name -match "MSSQLFDLauncher"}) { #check the service status if ($TextService.Status -eq "Running") { #start the service write-verbose "Stopping Full Text Service $($TextService.Name)" $TextService.Stop() } else { write-verbose "Text service $($TextService.Name) is already stopped." } } } else { write-verbose "Skipping checking Text Daemon services" } if($StopSSAS -eq $True) { # start the SSAS service write-verbose "Checking SSAS services" ForEach($SSASService in $Services | where-object {$_.Name -match "MSSQLServerOLAP"}) { #check the service status if ($SSASService.Status -eq "Running") { #start the service Write-verbose "Stopping SSAS Service $($SSASService.Name)" $SSASService.Stop() } else { write-verbose "SSAS Service $($SSASService.Name) is already stopped." } } } else { write-verbose "Skipping checking SSAS services" } if($StopSQL -eq $true) { #check the SQL Server Engine services write-verbose "Checking SQL Server Engine Services" #get all named instances and the default instance foreach ($SQLService in $Services | where-object {$_.Name -match "MSSQLSERVER" -or $_.Name -like "MSSQL$*"}) { #Check the service running status if($SQLService.status -eq "Running") { #if stopped start the SQL Server service write-verbose "Stoppin SQL Server Service $($SQLService.Name)" $SQLService.Stop() } else { #Write comfort message that the service is already running write-verbose "SQL Server Service $($SQLService.Name) is already stopped" } } } else { write-verbose "Skipping checking SQL Engine services" } } export-modulemember -function Stop-AllSQLServerServices #function to start all the sql server services on a given server function Start-AllSQLServerServices { [cmdletbinding()] Param([string]$Server , [bool]$StartSQL=$true , [bool]$StartAgent=$true , [bool]$StartSSRS=$true , [bool]$StartBrowser=$true , [bool]$StartSSIS=$true , [bool]$StartTextDaemon=$true , [bool]$StartSSAS=$true) #Get all the services on the server $Services = get-service -ComputerName $Server if($StartSQL -eq $true) { #check the SQL Server Engine services write-verbose "Checking SQL Server Engine Services" #get all named instances and the default instance foreach ($SQLService in $Services | where-object {$_.Name -match "MSSQLSERVER" -or $_.Name -like "MSSQL$*"}) { #Check the service running status if($SQLService.status -eq "Stopped") { #if stopped start the SQL Server service write-verbose "Starting SQL Server Service $($SQLService.Name)" $SQLService.Start() } else { #Write comfort message that the service is already running write-verbose "SQL Server Service $($SQLService.Name) already running" } } } else { write-verbose "Skipping checking SQL Engine services" } if($StartAgent -eq $true) { #check the SQL Server Agent services write-verbose "Checking Agent Services" #get all named agent instances and the default instance ForEach ($SQLAgentService in $Services | where-object {$_.Name -match "SQLSERVERAGENT" -or $_.Name -like "SQLAgent$*"}) { #check the servcie running status if($SQLAgentService.status -eq "Stopped") { #if stopped, start the agent write-verbose "Starting SQL Server Agent $($SQLAgentService.Name)" $SQLAgentService.Start() } else { #write comfort message that the service is already running write-verbose "SQL Agent Service $($SQLAgentService.Name) already running" } } } else { write-verbose "Skipping checking Agent services" } if($StartSSRS -eq $true) { #check the SSRS services write-verbose "Checking SSRS Services" #get all reporting service services ForEach ($SSRSService in $Services | where-object {$_.Name -match "ReportServer"}) { #check the status of the service if($SSRSService.status -eq "Stopped") { #if stopped, start the agent write-verbose "Starting SSRS Service $($SSRSService.Name)" $SSRSService.Start() } else { #write comfort message that the service is already running write-verbose "SQL Agent Service $($SSRSService.Name) already running" } } } else { write-verbose "Skipping checking SSRS services" } if($StartSSIS -eq $True) { #get the SSIS service (should only be one) write-verbose "Checking SSIS Service" #get all services, even though there should only be one ForEach ($SSISService in $Services | where-object {$_.Name -match "MsDtsServer*"}) { #check the status of the service if($SSISService.Status -eq "Stopped") { #if its stopped, start it write-verbose "Starting SSIS Service $($SSISService.Name)" $SSISService.Start() } else { #write comfort message write-verbose "SSIS $($SSISService.Name) already running" } } } else { write-verbose "Skipping checking SSIS services" } if ($StartBrowser -eq $true) { #Check the browser, start it if there are named instances on the box write-verbose "Checking SQL Browser service" #check if there are named services if(($services.name -like "MSSQL$*") -ne $null) { #get the browser service $BrowserService = $services | where-object {$_.Name -eq "SQLBrowser"} if($BrowserService.Status -eq "Stopped") { #if its stopped start it write-verbose "Starting Browser Server $($BrowserService.Name)" $BrowserService.Start() } else { #write comfort message write-verbose "Browser service $($BrowserService.Name) already running" } } else { #if no named instances, we don't care about the browser write-verbose "No named instances so ignoring Browser" } } else { write-verbose "Skipping checking Browser service" } if($StartTextDaemon -eq $True) { # Start the full text daemons write-verbose "Checking SQL Full Text Daemons" ForEach($TextService in $Services | where-object {$_.Name -match "MSSQLFDLauncher"}) { #check the service status if ($TextService.Status -eq "Stopped") { #start the service write-verbose "Starting Full Text Service $($TextService.Name)" $TextService.Start() } else { write-verbose "Text service $($TextService.Name) already running." } } } else { write-verbose "Skipping checking Text Daemon services" } if($StartSSAS -eq $True) { # start the SSAS service write-verbose "Checking SSAS services" ForEach($SSASService in $Services | where-object {$_.Name -match "MSSQLServerOLAP"}) { #check the service status if ($SSASService.Status -eq "Stopped") { #start the service Write-verbose "Starting SSAS Service $($SSASService.Name)" $SSASService.Start() } else { write-verbose "SSAS Service $($SSASService.Name) already running." } } } else { write-verbose "Skipping checking SSAS services" } } export-modulemember -function Start-AllSQLServerServices