Set Up Operators and Alerts in PowerShell

One important task that is often neglected by DBAs when setting up SQL Server, is defining operators and alerts. SQL Server will tell you when critical errors occur if you've configured the important alerts. SQL Server will also tell you about critical errors via the operators you set up under SQL Server agent.

The script I use has command-line arguments for instance name ($inst), operator name ($oper), and operator email address ($email). After verifying that the SQLPS module is loaded we connect to the instance.

$svr = New-Object ('Microsoft.SQLServer.Management.SMO.Server') $inst

Then, we need to test to see if the operator already exists. The Alerts and Operators are collections under the server's JobServer object, which also contains the SQL Server Agent Jobs and Schedules. We'll first grab the Operator object where the name matches the name we passed into our script. For the purposes of my script, I want to make sure the operator has the properties I've suggested, so I'll drop it if it exists, then recreate it with my desired properties. We determine it exists if the count property is greater than 0 when we grab it.

$op = $svr.JobServer.Operators[$oper]
if ($op.Count -gt 0) {
 $op.Drop()                        # The operator already exists, so drop it
}
$op = New-Object ('Microsoft.SqlServer.Management.Smo.Agent.Operator') ($svr.JobServer,$oper)
$op.EmailAddress = $email
$op.Create()

Once we've created the operator, we can use it in our alert notifications.

The alerts we want to set up on all servers are for all severity levels 19 through 25, and for error messages for errors 823, 824, 825 and 829. These errors should give us a heads-up on any critical issues with our servers and our databases.

First we'll set up collections for the severity and message numbers. (We'll use a built-in feature of PowerShell for the severity levels.)

$sev = 19..25
$msg = 823,824,825,829

Now, the properties for the severity level alerts are slightly different from the error message alerts, so we'll use different but very similar loops for each. We'll create the alert name as "Error 19 Alert," or whatever error number we're working on, then test to see if that exists and drop it if it does. Then, we'll create the new alert, set either the Severity or the MessageID property, depending on the type of alert and indicate that the event description is to be included in the notification. (This ensures that the error message is sent in the body of the message.) We can then create the alert, but we need to go back and add a NotifyEmail notification to the alert and alter it, so our operator is actually emailed when the alert is fired.

foreach ($sv in $sev) {
$nm = "Error $sv Alert"
$a = $svr.JobServer.Alerts[$nm]
if ($a.Count -gt 0) {
$a.Drop()
}
$a = New-Object ('Microsoft.SqlServer.Management.Smo.Agent.Alert') ($svr.JobServer, $nm)
$a.Severity = $sv
$a.IncludeEventDescription = 'NotifyEmail'
$a.Create()
$a.AddNotification($oper, [Microsoft.SqlServer.Management.Smo.Agent.NotifyMethods]::NotifyEmail)
$a.Alter()
}
 
foreach ($ms in $msg) {
$nm = "Error $ms Alert"
$a = $svr.JobServer.Alerts[$nm]
if ($a.Count -gt 0) {
$a.Drop()
}
$a = New-Object ('Microsoft.SqlServer.Management.Smo.Agent.Alert') ($svr.JobServer, $nm)
$a.MessageID = $ms
$a.IncludeEventDescription = 'NotifyEmail'
$a.Create()
$a.AddNotification($oper, [Microsoft.SqlServer.Management.Smo.Agent.NotifyMethods]::NotifyEmail)
$a.Alter()
}

Now, whenever a critical issue arises, our operator is notified and can address the problem right away.

Related: Steps to Setting Up SQL Agent

Comments

Plain text