PowerShell script to remotely start / stop MS SQL server on multiple machines

 This PowerShell script picks up server machines from given text file which includes all SQL server host names.

In order to run the script successfully, it needs following requirements,

1. User who runs the script has to be a domain user account who has start/stop SQL server privileges on all SQL servers listed by the text file. 

2. All SQL servers are member of same domain, and each server is listed as one line in a text file.

3. The script file accepts two parameters. First one is operation directive, it should be start, stop or status. Second parameter is full path of SQL server list file, the script will read all servers from the list file.

Server list text file sample,

# All lines starting with # will be ignored.
host01
host02
#host03

Script source code:

#-------------------------------------------------
#  Usage:
#      remoteControlSQL.ps1 <status|start|stop> <server_list_file>
#-------------------------------------------------
# First parameter: $operation 
#    stataus - Show status of SQL Server Instance and Agent status
#    start   - start SQL Server Instance and Agent
#    stop    - stop SQL Server Instance and Agent
# Second parameter: $serverList 
#    File name with full path, the file list all SQL server host names
#
param([string]$operation,[string]$serverList)
foreach($server in Get-Content $serverList | Select-String -NotMatch '^#') {
  Write-Host ""
  Write-Host "=== $server ==="
  switch ($operation){
    "start"
       {# Start SQL Server Instance and Agent
          $session = New-PSSession -ComputerName $server
          Invoke-Command -Session $session -ScriptBlock { 
            get-wmiobject -Class win32_service | where {$_.DisplayName -like 'SQL Server (*)' } |Select-Object name |start-service
            get-wmiobject -Class win32_service | where {$_.DisplayName -like 'SQL Server Agent (*)' } |Select-Object name |start-service
          }
          Remove-PSSession $session
       }
    "stop"
       {# Stop SQL Server Instance and Agent 
          $session = New-PSSession -ComputerName $server
          Invoke-Command -Session $session -ScriptBlock { 
            get-wmiobject -Class win32_service | where {$_.DisplayName -like 'SQL Server (*)' } |Select-Object name |stop-service -force
            get-wmiobject -Class win32_service | where {$_.DisplayName -like 'SQL Server Agent (*)' } |Select-Object name |stop-service -force
          }
          Remove-PSSession $session
       }
    default
       {# Check SQL Server Instance and Agent status
          $session = New-PSSession -ComputerName $server
          Invoke-Command -Session $session -ScriptBlock { 
            get-wmiobject -Class win32_service | where {$_.DisplayName -like 'SQL Server (*)' } |Select-Object name |get-service
            get-wmiobject -Class win32_service | where {$_.DisplayName -like 'SQL Server Agent (*)' } |Select-Object name |get-service 
          }
          Remove-PSSession $session
       }
  }
}

Sample output of checking SQL server status,

PS C:\> .\remoteControlSQL.ps1 status .\computers.lst

=== host01 ===

Status   Name               DisplayName                            PSComputerName
------   ----               -----------                            --------------
Running  MSSQL$SQLTEST      SQL Server (SQLTEST)                   host01
Running  MSSQLSERVER        SQL Server (MSSQLSERVER)               host01
Running  SQLAgent$SQLTEST   SQL Server Agent (SQLTEST)             host01
Running  SQLSERVERAGENT     SQL Server Agent (MSSQLSERVER)         host01

=== host02 ===
Running  MSSQLSERVER        SQL Server (MSSQLSERVER)               host02
Running  SQLSERVERAGENT     SQL Server Agent (MSSQLSERVER)         host02

Sample output of stopping SQL server,

PS C:\> .\remoteControlSQL.ps1 stop .\computers.lst

=== host01 ===

=== host02 ===

PS C:\> .\remoteControlSQL.ps1 status .\computers.lst

=== host01 ===

Status   Name               DisplayName                            PSComputerName
------   ----               -----------                            --------------
Stopped  MSSQL$SQLTEST      SQL Server (SQLTEST)                   host01
Stopped  MSSQLSERVER        SQL Server (MSSQLSERVER)               host01
Stopped  SQLAgent$SQLTEST   SQL Server Agent (SQLTEST)             host01
Stopped  SQLSERVERAGENT     SQL Server Agent (MSSQLSERVER)         host01

=== host02 ===
Stopped  MSSQLSERVER        SQL Server (MSSQLSERVER)               host02
Stopped  SQLSERVERAGENT     SQL Server Agent (MSSQLSERVER)         host02

Sample output of starting SQL server,

PS C:\> .\remoteControlSQL.ps1 start .\computers.lst

=== host01 ===

=== host02 ===

PS C:\> .\remoteControlSQL.ps1 status .\computers.lst

=== host01 ===

Status   Name               DisplayName                            PSComputerName
------   ----               -----------                            --------------
Running  MSSQL$SQLTEST      SQL Server (SQLTEST)                   host01
Running  MSSQLSERVER        SQL Server (MSSQLSERVER)               host01
Running  SQLAgent$SQLTEST   SQL Server Agent (SQLTEST)             host01
Running  SQLSERVERAGENT     SQL Server Agent (MSSQLSERVER)         host01

=== host02 ===
Running  MSSQLSERVER        SQL Server (MSSQLSERVER)               host02
Running  SQLSERVERAGENT     SQL Server Agent (MSSQLSERVER)         host02