Another way to allow e-mail messaging and alerts is through SQL Server Agent. SQL Server Agent allows you to set up alerts to notify users or operators via e-mail, pager, or a Net Send message when certain events are triggered, or it can send messages to report job or job step status. The mail profile used for SQL Agent will typically be the same mail profile used for SQL Mail, but it could be a different mail profile if a different e-mail configuration was needed.
You can set up different mail profiles for SQL Server and SQL Server Agent in two ways. You can either set up separate domain user accounts for each service and configure a mail profile for each user account, or you can use the same domain account for each service and create two different mail profiles.
To specify the mail profile to be used by SQLAgentMail, open up the Management folder in SQL Enterprise Manager, right-click on SQL Server Agent, and choose the Properties option. In the Mail Session section of the General tab, specify the mail profile you want SQL Server Agent to use (see Figure 23.4). After the profile is chosen , click on the Test button to ensure that SQL Server Agent can initial a mail session using the specified mail profile. In addition, you might want to check the option to have SQLAgentMail record all messages sent in the Sent folder of the e-mail client so you'll have a record of e-mail messages sent by SQL Server Agent.
Figure 23.4. Specifying the mail profile to be used by SQLAgentMail in the SQL Server Agent Properties dialog box.
Setting Up Mail Notification for Operators in SQL Server Agent
You can configure SQL Server Agent to send e-mail notifications for either of the following events:
Before you can send an e-mail for either of these events, you need to set up the e-mail account(s) for the operators you will be notifying . Click on the Operators item in the SQL Server Agent folder in SQL Enterprise Manager to bring up a list of the configured operators. (For more information on defining SQL Server Agent operators, see Chapter 18, "SQL Server Scheduling and Notification.") In the Operators dialog box, you can enter the e-mail information for the operator, as shown in Figure 23.5.
Figure 23.5. Specifying the e-mail addresses for an operator in the Operator Properties dialog box.
You can choose to notify the operator via standard e-mail, pager notification, or Network Send message. You can enter the e-mail address directly or, if you've set up e-mail addresses in the e-mail client address book, you can click on the Ellipsis button ( ) to bring up the e-mail client address book and choose the recipient e-mail address, as shown in Figure 23.6.
Figure 23.6. Choosing the e-mail addresses for an operator from the Outlook 2000 Address Book.
If your e-mail server software supports sending messages to a pager, you can enter the e-mail address for pager notification in the Pager e-mail name box. Typically, you will need to install a pager transport provider for your e-mail server so that it has the ability to dial a pager directly when an e-mail is sent to a pager e-mail address.
If you are using a POP3 server to send e-mails, you might still be able to send pager notifications. Many pager vendors today support sending pages by sending an e-mail to an Internet address. The pager vendor's e-mail system will receive the e-mail and forward it to the associated pager. The e-mail address usually includes the pager number and the domain name of the pager vendor. For example, for many pager vendors , the e-mail address is of the format email@example.com . Check with your pager vendor for the specific e-mail address format, the message type ( alphanumeric or numeric only), and size it supports.
Setting Up Mail Notification for SQL Server Agent Alerts
You can configure SQL Server Agent to send e-mail messages to specified SQL Server operators when specific alert events are generated. When configuring alerts, you have the opportunity to select a type of response for this alert under the Response tab of the Alert Properties dialog box (see Figure 23.7). When this alert is triggered, a message is sent to the designated operator(s), with any additional instructions or messages specified in the Additional Notification Message to Send text box.
Figure 23.7. Setting up operator notification for an alert in the Alert Properties dialog box.
If you have to set up a number of alert notifications for a new operator, it can be tedious to go through each alert individually to add the notification for the operator. Fortunately, an easier method exists. You can bring up the Properties dialog box for the operator and click on the Notifications tab. Doing so brings up a dialog box that lists all defined alerts and lets you check off which ones you want to have send an e-mail, pager, or Net Send notification to the operator (see Figure 23.8).
Figure 23.8. Specifying which alerts send notification to an operator in the Operator Properties dialog box.
In this dialog box, you can also turn off notification of the operator temporarily by unchecking the Operator Is Available to Receive Notifications check box. This option lets you suspend sending of notifications without having to clear out all the notification selections. You can also click on the Send E-mail button to send an e-mail message to the operator to inform him of what types of events he will be notified and what the pager notification schedule is. A sample of the text of this message is shown in Listing 23.2.
Listing 23.2 Sample SQL Server Alert and Job Notifications Message
The following alerts have been assigned to operator 'Ray Rankins': [Via E-mail] Demo: Full msdb log [Via E-mail] Demo: Full tempdb [Via E-mail] Demo: Sev. 19 Errors [Via Pager] Demo: Full msdb log [Via Pager] Demo: Full tempdb [Via Pager] Demo: Sev. 19 Errors [Via Net Send] Demo: Full msdb log [Via Net Send] Demo: Full tempdb [Via Net Send] Demo: Sev. 19 Errors The pager schedule for operator 'Ray Rankins' is as follows: Monday - 08:00 to 18:00 Tuesday - 08:00 to 18:00 Wednesday - 08:00 to 18:00 Thursday - 08:00 to 18:00 Friday - 08:00 to 18:00 Saturday - 08:00 to 18:00 Sunday - 08:00 to 18:00 Jobs that send notifications to this operator: DB Backup Job for DB Maintenance Plan 'DB Maintenance Plan1' - By e-mail (Upon failure) Optimizations Job for DB Maintenance Plan 'DB Maintenance Plan1' - By e-mail (Upon failure)
Setting Up Mail Notification for SQL Server Agent Jobs
SQL Server Agent jobs can specify e-mail notifications as well on job completion. You can specify whether to send the notification on job success, failure, or simply on job completion regardless of success or failure.
To set up notification for a job, right-click a predefined job, select Properties, and then select the Notifications tab (see Figure 23.9).
Figure 23.9. Specifying job notification settings in the Job Properties dialog box.
In this example, the job will send an e-mail to the SQL Operators operator on job completion, and send a page to the operator Ray Rankins on job failure. Both of these are previously defined operators with valid e-mail addresses.
In the Operator Properties dialog box, you can also see which jobs an operator is configured to receive notifications for by clicking on the Notifications tab and then clicking on the Jobs option (see Figure 23.10). However, you cannot configure the job notification settings through this dialog box ”that can only be done in the Notifications tab of the Job Properties dialog box. If you need to change the operator to be notified for a number of jobs, you have to manually edit each job individually to change the notification information.
Figure 23.10. Viewing job notification settings for an operator in the Operator Properties dialog box.