“Before we hired you, I would check the job histories on the servers to make sure all the databases got backed up over the weekend,” my boss said, about week three into the job. Can you do that now?” I told him I could do him one better: I could get the servers to tell me themselves that they got backed up — or not! — every week. Heck, I said that I could even go so far as to have them tell me when they had a problem backing something up AS IT HAPPENED. Now, because I’ve been taking “active listening” skills classes, I could hear what he was really asking. For the purposes of strong SEO performance he was asking me how to set up email notifications for SQL Agent jobs, like scheduled backups from maintenance plans.
You’re going to want to do four things. I know this because I tried one of them, then I tried two of them. Once you do all three of them, it works. Also, I read MSDN. First, configure Database Mail on the server in question. Then enable Database Mail for the SQL Agent. Add an Operator for your SQL Agent, and then add a Notification for that Operator in the job. Simple, right?
Let’s break that down.
In SQL 2008 and on, you have the ability to send emails easily through the use of the Database Mail functionality. It requires two steps. Create one or more mail profiles, and then add one or more mail accounts to those profiles. You can find Database Mail in the Management tree of the SQL Server’s object explorer.
Right click on Database Mail and click Configure Database Mail. It will bring up a wizard that gives you several options. Choose the first one, “Set up Database Mail” and click Next. When you do, SQL Server will let you know that the server has not yet enabled Database Mail (it is disabled by default), and then ask if you want to enable it. Click Yes.
Give your new Database Mail Profile a name. This is how you will identify it across the Server. For instance, you might want one profile for handling SQL Agent Job notifications, but another one for emailing the development team if a stored procedure throws an error. Once you’ve picked a profile name, click Add… to associate an SMTP account to the Profile.
Fill out the New Database Mail Account dialog with whatever values are appropriate. I like to use the name of the server as the account and display name, so when I get an email saying “Backup Failed” I see the server name in the header, the from line, pretty much everywhere. Get the appropriate SMTP information from your email administrator if necessary. Click OK when finished, and then Next to continue.
From here you can see all the profiles you have set up. You can choose to have public or private profiles, and can set one of the public profiles to be the default profile as well. MSDN says this about choosing which of these to use: “A public profile allows any user with access to the msdb database to send e-mail using that profile. A private profile can be used by a user or by a role. Granting roles access to profiles creates a more easily maintained architecture. To send mail you must be a member of the DatabaseMailUserRole in the msdb database, and have access to at least one Database Mail profile.” For my purposes, this is a server with no public users beyond system admins, so I chose to just go with a public profile set as the default.
Click Next. The last step of the wizard will give you some configuration options: number of retry attempts, prohibited file extensions for attachments, and other things of that sort. If your email system has a maximum file attachment size, you should modify the Maximum File Size value here to match it. Click Next when finished, and then click Finish to set everything up.
Once it finishes you can right click on Database Mail in the SQL Server object explorer and select “Send Test Email” to validate that everything has been set up correctly and is operating as expected. Presuming that goes well, let’s jump over to the SQL Agent.
SQL Agent Alert System
Simply put, alll we need to do at this point is tell SQL Agent that we have a Database Mail Profile that it can use to send email notifications. Right-click on the SQL Server Agent in the object explorer and select Properties.
Select Alert System on the left, and then check the “Enable mail profile” box. In the “Mail profile” dropdown, select the Database Mail Profile that you just set up and click OK.
SQL Agent Operator
Ok, now if you expand the SQL Server Agent in the object explorer, you’ll see Jobs, Alerts, Operators, Proxies and Error Logs. Right-click on Operators and select New Operator. From here give your operator a name and place the operator’s email address there. This might be an Exchange email group (that’s what we use) or a semicolon-separated list of emails. That’s all you need here. Click OK to save and close. Later, after you start setting jobs to notify this Operator when a job fails, you can click on the Notifications tab and see a list of them.
SQL Agent Job Notification
The last thing to do is to set which jobs you want to be notified about by email if they fail. Double click on any SQL Agent job, and then click on Notifications.
Check the Email box, set the Operator you want to use, and click OK. Bam. You’re done.
You can see that there are options to send emails on Success, Fail, or Completion (either Success or Fail), but for most people’s purposes, it’s enough just to know when it doesn’t work right. YMMV.