As a DBA, automating your various jobs on SQL Server – taking backups, truncating logs, updating statistics, etc – is a good idea because it makes sure you don’t have to depend on your feeble memory for these important tasks. And it leaves you free for more vital tasks such as sleeping and bingeing on whatever new Netflix series is out. But to make sure your automated tasks are still running, you need a good notification method, and it should also be automated. And foolproof. So how do you do this?

Types of SQL Server Notifications  

A quick background: there are 5 types of SQL Server Agent Notifications you can set up:

• Email
• Pager
• Net Send
• Write to the Windows application event log
• Automatically delete the job.

In order to use the Email and Pager option, you need to set up Database Mail.  But both the Pager and Net Send options have been deprecated and Microsoft recommends you don’t use these options; they are only maintained for backward compatibility with jobs and notifications set up in older versions of SQL Server. To receive either email or pager notifications you should also enable Database Mail. Setup of Database Mail is outside the scope of this article, but the steps to do so are covered in this MSDN page.

Email Notifications

But we are specifically interested in email notifications, so we’ll focus on those for now. There are three different types of email notifications for a SQL Server Agent job (this also applies to other notification types, not only email): when the job succeeds, fails, or completes.  If you are only interested in a job’s failures, then set it up to only send notifications when the job fails.

To find out which jobs are enabled but do not have any email notifications set up, run the following script:

USE msdb;
GO
SELECT name, enabled, description
FROM dbo.sysjobs WHERE notify_level_email = 0;
GO

To set up an email notification for an existing SQL Server job, open SQL Server Management Studio, expand Jobs, then double-click to open the specific job you want. Select the Notifications menu item and check the E-mail checkbox. Then enter the email address to use and the action to be performed; that is when you want to be alerted. The options here are: When the job fails, When the job completes, and When the job succeeds.

A Less Complicated Method

Now, setting up email notifications sounds quite straightforward, and in most cases it is. The tricky part is the perquisite – Database Mail. You require to have already set up an email account (and if you are hosting your own email, also have set up a mail server) and know the requisite port numbers and whether or not your email setup uses SSL. For non-techies, this is doable but can be rather bewildering at first.

An easier way to handle email notifications is to use the SQLBackupAndFTP application. With SQLBackupAndFTP you simply check the option of when sending you an email: when your job completes, when it fails, or both.

SQLBackupAndFTP is easily usable by both the complete novice and IT pro alike. For the novice who doesn’t want to muck around with SMTP settings and mail servers and whether or not to use port 25, he can simply enter an email address to send the notifications to, as described above. For those who want to customize their email notifications, they can click the “Gear” button on the “Send email confirmations” section. And on the next screen select the Custom option to specify their email server settings. Note that the Automatic option on this page is the same as what was described earlier – simply entering an email address in the “Send email confirmations” section. There is also a hybrid option to use a Gmail address; you simply enter the Gmail address and password to use, and all the other Gmail settings are auto-configured for you.

Summary

For the SQL Server DBA, notifications are a very good idea. Through them, you know that everything is still working (or not) in your database, without having to rely on your memory to physically monitor your jobs. Notifications are especially important for important tasks like taking backups and moving or copying the backup files to offline locations for security. And SQLBackupAndFTP is an excellent tool for doing just this. It enables you, whether an email guru or not, to quickly set up your own jobs and email notifications.