SQL Server – Using powershell to start and stop all SQL Server services on a server

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 wasn’t particularly high, so I decided to knock up a quick powershell script to do it all for me.

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

What’s really interesting, however, is that the get-service cmdlet does 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-AllSQLServerS
Advertisements

One thought on “SQL Server – Using powershell to start and stop all SQL Server services on a server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s