Monitor SQL Server and Service

Create a .txt file with all the server name and keep it in safe location (C:\ServerList.txt). 

Save the powershell script to a preferred location as .ps1 file.

Create a new task on Windows Task Scheduler to run the powershell script every 5 or 10 minutes.

 

 

$servers = gc 'C:\ServerList.txt'
foreach($server in $servers)
{$body=get-wmiobject win32_service -computername $server |
select name,state | where {($_.name -eq "MSSQLSERVER")-and $_.state -match "Stopped"} |
  Out-String
    
if ($body.Length -gt 0)
{
  $smtp = new-object Net.Mail.SmtpClient("xxxxx.xxxxx.com")
  $subject="SQL service is down on " + $server
  $smtp.Send("xxxxx@xxxxx.com", "mashrurs@shuvo.com", $subject, $body)
 
}
}

foreach($server in $servers)
     {
       $result = (Test-Connection $server -Quiet)
        if ($result -match 'False')
            {
                $smtp = new-object Net.Mail.SmtpClient("XXXX.XXXX.com")
                $subject="SQL Server $Server is down"
                $smtp.Send("XXXX@xxxx.com", "mashrurs@shuvo.com", $subject, $result)
            }
      }