SQL Server provides a great notification system for alerts and jobs. There are many options to be set and multiple dialog boxes to step through, but once finished, you’ll always be the first to know when a problem is immanent. Below is a detailed reference to get this running on a new server.

1. First thing’s first: Set up Database Mail. This a wonderfully illustrated tutorial that is consistent for SQL Server 2005 and 2008.
2. Create a new operator.
   Right-click the Operators folder in the Object Explorer window. It will be nested inside SQL Server Agent.
   

   Create New Operator

3. Configure the new operator. There are only a couple of items that need to be set:
   1. Name: A label for either the person or group name. It need not match the actual email account name.
   2. E-mail name: Enter the email address of the person or group who you wish to receive this notification. Separate multiple entries with a semicolon (;).
   

   Operator Settings

   While there are additional notification options such as pager and net send, they have been deprecated and will be removed in future versions.

4. Configure SQL Server Agent
   Right-click the SQL Server Agent tree item and select properties from the resulting dropdown list.
   

   SQL Server Agent Properties

5. Switch to the Alert System pane and make several changes there.
   1. Enable mail profile: Ensure this is checked.
   2. Mail system: Database Mail. SQLMail has been deprecated and we have not configured it in this guide.
   3. Mail profile: Select the profile you created when you set up Database Mail.
   4. Enable fail-safe operator: Check this box.
   5. Operator: Select the operator you created above. The name should be listed in the dropdown box.
   6. Notify using: E-mail. Again, pager and net send have been deprecated.
   

   SQL Server Agent Alert settings

6. Restart SQL Server Agent. This is required.
7. Configure the SQL Server Agent Job
   1. Locate a job which you want to configure notification for.
   2. Open the properties dialog box and switch to  the Notification Pane.
   3. Check the E-Mail box and select the appropriate operator and “When the job fails”
   

   Job Notification Settings

   When the job does not succeed for any reason, you will receive an email telling you so. You can also set options for “When the job succeeds” and “When the job completes”. You may want to fiddle with these settings to test that the job is configured correctly.