Alert for SQL Service down

This alert is similar to low disk space alert. It’s basically SQL Agent job running on a monitoring SQL Server, reading server names from a text file, running PowerShell script on remote servers to check if one of the SQL Servcies is down and send an email to DBA team’s email distribution list if a SQL Service is down.

I have another PowerShell script for people who need more in-depth monitoring with servers that may not necessarily be in same domain. You may request it in the comment section below. I am not including that since the script below should be sufficient for most of the DBAs in most situations.

You can see in ‘script logic’ section, script is reading status of all services that have ‘SQL’ in their name and excluding ‘SQL Browser’ service. The script also ignores SQL Services that are disabled.

As always, I am not including sample alert email but rest assured it’s beautiful and meaningful.

 

POWERSHELL SCRIPT:

 
######################### DEFINING VARIABLES ################################

$TO = "DBA Team<dbateamDL@abc.com"

$From = "SQL Server DB Alert<xyzMonitoring@abc.com"

$SMTPServer = "smtp@abc.com"

######################### DEFINING FUNCTIONS ################################

# (FuncMail) Function to send email

function FuncMail {
param($To, $From, $Subject, $Body, $smtpServer)
$msg = new-object Net.Mail.MailMessage #creating a mail object
$smtp = new-object Net.Mail.SmtpClient($smtpServer) #creating SMTP server object
#Email Structure
$msg.From = $From
#$msg.ReplyTo = "xyz@abc.com"
$msg.To.Add($To)
$msg.Subject = $Subject
$msg.IsBodyHtml = 1
$msg.Body = $Body
#$mailmessage.Priority = [system.net.mail.mailpriority]::high
$smtp.Send($msg)
}
######################### Script Logic ################################
$servers=get-content "\\abcSQL01_ServerName\D$\DBA_DoNotDelete\PowerShellScripts\Config\Servers.txt"

foreach($server in $servers)
{
# go to each server and return the name and state of services
# that are like "SQLAgent" and where their state is stopped

get-wmiobject win32_service -computername $server | where-object {$_.name -match "SQL*" -and $_.startmode -ne "disabled" -and $_.name -ne "SQLBrowser"} | `
#select name,state,startmode |
Foreach { if ($_.state -ne "running") {`
$subject = ":( !!! " + $_.name +"service down on " + $server
$body = "The state of SQL service " + $_.name + " has changed to" + $_.state + ". Please look into the issue"
FuncMail -To $TO -From $From -Subject $subject -smtpServer $smtpserver -body $body
}
}
}
######################### Script Logic ################################