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)

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 208 times, 1 visits today)

1 thought on “Setting Up Database Mail And SQL Agent Alerts With PowerShell

  1. Pingback: Using Powershell to Configure Database Mail and SQL Agent Alerts – Curated SQL

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.