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.
You can configure SQL Server Agent to send e-mail notifications for either of the following events:
When an alert is triggered
When you want to report completion status of a job
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.
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.
If you are running Enterprise Manager from a client workstation and not the server where SQL Server is running, the address book displayed will be the local address book on the client workstation. SQL Server will not be able to send e-mail to entries from the local client's address book. Do not choose recipient addresses from an address book unless you are configuring SQL Mail on the server machine.
The e-mail address you choose can be an individual's e-mail address or a distribution list. Using a distribution list makes it easier to send e-mails to multiple operators or to multiple e-mail addresses for a single operator. You can create a single operator in SQL Server Agent and link it to a distribution list. To change the operators to be notified, you only need to change the members of the distribution list; you do not have to add or remove and configure individual e-mail addresses for operators in SQL Server Agent.
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.
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.
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).
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.
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)
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).
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.
Actually, the e-mail address for the SQL Operators operator in the previous example is a distribution group rather than an individual e-mail message. The current SQL Server job architecture does not provide the ability to send e-mail notification to more than one operator on job completion. The only way to notify multiple operators is to set up a generic operator whose e-mail address is a distribution group that contains all the persons who need to be notified of job success or failure.
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.