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 $ServerI 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-AllSQLServerServicesShare this:
Like this:
Like Loading...