Setting Up Database Mail And SQL Agent Alerts With PowerShell

As a DBA, you need to know when there’s a problem on your SQL Servers. And while I highly recommend you use a full-fledged monitoring system, there are also some things you can set up on your SQL Servers so that they will tell you when certain things go wrong. This doesn’t replace a full monitoring system, but setting up the below alerts will give you notification when SQL Server encounters things like corruption or resource issues.

Before we get into the code, I want to be very clear in saying that I did not originally write these scripts. I scoured the Internet to find a few tutorials that had what I needed, and pieced them together to produce the following. The original scripts can be found here:

Based off of the above tutorials and documentation, I have created the below PowerShell script that does the following:

Configure a SQL Server to enable Database Mail
Set up initial mail account
Set up initial mail profile
Configure the SQL Agent to use the mail profile
Create an Agent Operator
Set up alerts for Error Severities 17-25 (read about those here)
Set up alerts for Corruption Errors 823, 824, 825, and 829 (read about those here)


####
#below code "inspired" by: https://www.sqlservercentral.com/articles/configuring-database-mail-with-powershell-and-smo
####
cls

$SQLInstance = "SQLServer.YourDomain.com" #Use FQDN
$MailAccount = "AccountName"
$MailProfile = "ProfileName"
$EmailAddress = "your@email.com" #email address SQL Server will send from
$MailServer = "mail.YourDomain.com"
$OperatorName = "AgentOperatorName"
$AlertEmail = "your@email.com" # the email address the alerts will go to


# Load the SMO assembly and create the server object, connecting to the server.
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null
$server = New-Object 'Microsoft.SqlServer.Management.SMO.Server' ($SQLInstance)

# Configure the SQL Server to enable Database Mail.
$server.Configuration.DatabaseMailEnabled.ConfigValue = 1
$server.Configuration.Alter()

#Set up initial mail account
New-DbaDbMailAccount -SqlInstance $SQLInstance -Name $MailAccount -EmailAddress $EmailAddress -MailServer $MailServer -Force
#set up initial mail profile
New-DbaDbMailProfile -SqlInstance $SQLInstance -Name $MailProfile -MailAccountName $MailAccount

#Configure the SQL Agent to use the mail profile.
$server.JobServer.AgentMailType = 'DatabaseMail'
$server.JobServer.DatabaseMailProfile = $MailProfile
$server.JobServer.Alter()

####
#below code "inspired" by: https://www.itprotoday.com/sql-server/set-operators-and-alerts-powershell
####

$op = $server.JobServer.Operators[$OperatorName]
#if the specified operator does not already exist, create it
if ($op.Count -eq 0) {
$op = New-Object ('Microsoft.SqlServer.Management.Smo.Agent.Operator') ($server.JobServer,$OperatorName)
$op.EmailAddress = $AlertEmail
$op.Create()
}

#set severity levels we want alerts for
$sev = 17..25
$msg = 823,824,825,829

foreach ($sv in $sev) {
$nm = "Error $sv Alert"
$a = $server.JobServer.Alerts[$nm]
#if the specified operator does not already exist, create it
if ($a.Count -eq 0) {
$a = New-Object ('Microsoft.SqlServer.Management.Smo.Agent.Alert') ($server.JobServer, $nm)
$a.Severity = $sv
$a.IncludeEventDescription = 'NotifyEmail'
$a.Create()
$a.AddNotification($OperatorName, [Microsoft.SqlServer.Management.Smo.Agent.NotifyMethods]::NotifyEmail)
$a.Alter()
write-host "$nm Created"
}
}

foreach ($ms in $msg) {
$nm = "Error $ms Alert"
$a = $server.JobServer.Alerts[$nm]
#if the specified alert does not already exist, create it
if ($a.Count -eq 0) {
$a = New-Object ('Microsoft.SqlServer.Management.Smo.Agent.Alert') ($server.JobServer, $nm)
$a.MessageID = $ms
$a.IncludeEventDescription = 'NotifyEmail'
$a.Create()
$a.AddNotification($OperatorName, [Microsoft.SqlServer.Management.Smo.Agent.NotifyMethods]::NotifyEmail)
$a.Alter()
write-host "$nm Created"
}
}

####

#below code "inspired" by: https://www.sqlservercentral.com/articles/configuring-database-mail-with-powershell-and-smo

####

cls

$SQLInstance = "SQLServer.YourDomain.com" #Use FQDN

$MailAccount = "AccountName"

$MailProfile = "ProfileName"

$EmailAddress = "your@email.com" #email address SQL Server will send from

$MailServer = "mail.YourDomain.com"

$OperatorName = "AgentOperatorName"

$AlertEmail = "your@email.com" # the email address the alerts will go to

# Load the SMO assembly and create the server object, connecting to the server.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null

$server = New-Object 'Microsoft.SqlServer.Management.SMO.Server' ($SQLInstance)

# Configure the SQL Server to enable Database Mail.

$server.Configuration.DatabaseMailEnabled.ConfigValue = 1

$server.Configuration.Alter()

#Set up initial mail account

New-DbaDbMailAccount -SqlInstance $SQLInstance -Name $MailAccount -EmailAddress $EmailAddress -MailServer $MailServer -Force

#set up initial mail profile

New-DbaDbMailProfile -SqlInstance $SQLInstance -Name $MailProfile -MailAccountName $MailAccount

#Configure the SQL Agent to use the mail profile.

$server.JobServer.AgentMailType = 'DatabaseMail'

$server.JobServer.DatabaseMailProfile = $MailProfile

$server.JobServer.Alter()

####

#below code "inspired" by: https://www.itprotoday.com/sql-server/set-operators-and-alerts-powershell

####

$op = $server.JobServer.Operators[$OperatorName]

#if the specified operator does not already exist, create it

if ($op.Count -eq 0) {

$op = New-Object ('Microsoft.SqlServer.Management.Smo.Agent.Operator') ($server.JobServer,$OperatorName)

$op.EmailAddress = $AlertEmail

$op.Create()

}

#set severity levels we want alerts for

$sev = 17..25

$msg = 823,824,825,829

foreach ($sv in $sev) {

$nm = "Error $sv Alert"

$a = $server.JobServer.Alerts[$nm]

#if the specified operator does not already exist, create it

if ($a.Count -eq 0) {

$a = New-Object ('Microsoft.SqlServer.Management.Smo.Agent.Alert') ($server.JobServer, $nm)

$a.Severity = $sv

$a.IncludeEventDescription = 'NotifyEmail'

$a.Create()

$a.AddNotification($OperatorName, [Microsoft.SqlServer.Management.Smo.Agent.NotifyMethods]::NotifyEmail)

$a.Alter()

write-host "$nm Created"

}

foreach ($ms in $msg) {

$nm = "Error $ms Alert"

$a = $server.JobServer.Alerts[$nm]

#if the specified alert does not already exist, create it

if ($a.Count -eq 0) {

$a = New-Object ('Microsoft.SqlServer.Management.Smo.Agent.Alert') ($server.JobServer, $nm)

$a.MessageID = $ms

$a.IncludeEventDescription = 'NotifyEmail'

$a.Create()

$a.AddNotification($OperatorName, [Microsoft.SqlServer.Management.Smo.Agent.NotifyMethods]::NotifyEmail)

$a.Alter()

write-host "$nm Created"

}

This script has quickly become part of our standard server build. After setting up the parameters initially, all we now have to do is change the server name and run it, and all of our alerts are set up for us in a matter of a few seconds.

(Visited 3,154 times, 1 visits today)

Setting Up Database Mail And SQL Agent Alerts With PowerShell

Related

2 thoughts on “Setting Up Database Mail And SQL Agent Alerts With PowerShell”

Share this:

Related

2 thoughts on “Setting Up Database Mail And SQL Agent Alerts With PowerShell”