SQL Mail lets your server "reach out and touch someone" when it needs to
SQL Server's email functionality—which you can use to notify DBAs of potential problems and of task completion—is a powerful means of keeping your server running. In SQL Server 6.5, the SQL Mail function isn't the easiest to configure; SQL Server 7.0 improved the configuration process. (For information about how to configure SQL Mail on SQL Server 7.0 or 6.5, see "INF: How to Configure SQL Mail" at http://support.microsoft.com/support/kb/articles/q263/5/56.asp.) Now with SQL Server 2000, which can use either POP3 or Microsoft Exchange Server as email servers, the mail function is even easier to set up. Let's look at a couple of ways to configure SQL Mail for SQL Server 2000.
Configure SQL Mail
To configure SQL Server's mail profile in Windows 2000 Advanced Server by using Exchange as a mail server, you first establish an email account on your mail server. This email account is the account from which SQL Server can send and receive mail. I typically get a domain account that I use only for the MSSQLServer service and the SQLAgent service. You then affiliate the mail account with this domain account. Next, go to Control Panel, Services. Then, select the account that SQL Server uses for its service account. This account must exist in the domain account. Log in to the SQL Server system with the account affiliated with the email account you just created. After you log in, go back to Control Panel and select Mail. Make sure you stop and restart any affected services when you finish.
To create a mail profile, you can either add a new mail configuration or copy and modify an existing configuration. You complete the information as you would for any other email account, except that you make the host Messaging API (MAPI)-compliant. SQL Server requires MAPI-compliant host messaging so that it can connect with the mail host. I recommend that you use different accounts with appropriate names for each SQL Server to make mail management easier. For example, using different accounts lets you sort email from a development SQL Server separate from a production SQL Server so that you can prioritize any errors.
You then set up SQL Server's email function through Enterprise Manager by expanding the Server Group icon and expanding the server you want to send mail from. Select the Support Services folder under the server name, right-click SQL Mail, and select Properties. The Profile name option appears in the SQL Mail Configuration Properties dialog box, as Figure 1 shows. Enter a preconfigured profile from Exchange or select from the drop-down menu the profile that you just created. I recommend that you select Autostart SQL Mail when SQL Server starts in the SQL Mail Configuration dialog box to ensure that the DBA doesn't miss any messages. To find this option from the Server Group option, right-click the server name you want to send mail from and select Properties. As a safety precaution, you should also test an email account before you use it on a production system.
Add an Operator
Although you don't need a SQL Server operator to use some SQL Mail functions, I recommend that you set up an operator to make best use of the mail capabilities. An operator is an alias for an email account that some predefined functions look for. For example, if you want SQL Server to email someone if a backup fails, SQL Server sends the email to the predefined operator's email account. You add an operator to the Operators option under Management—SQL Server Agent by right-clicking Operator and selecting New Operator. You then complete the Name and Email input fields.
You might also want to set up a dba operator when you add your email operators. The name dba helps identify which operator to notify. Similarly, setting up an operator such as developer helps direct email to the operator most likely to respond to development-related emails. Whenever a DBA or primary developer leaves a job and a new person takes over, you just need to change or redirect the dba or developer operator's email address to ensure that the email goes to the correct operator. To change the operator's destination email address, select SQL Server Agent, Operators. Find the operator's name, edit that operator's properties, and change the email to the new destination.
Set Up Automated Notifications
After you configure the SQL Mail options and add the operators, you can configure the events that trigger error alerts, completion messages, or job success or failure messages. To configure alerts and warnings to use the email function, select Operators under Management—SQL Server Agent, right-click whichever operator you want to receive the email message, then select Properties. The Notifications tab, which Figure 2 shows, contains a list of potential operator alerts, including Full msdb log, Full tempdb, and errors with a severity level of 19 or higher.
I recommend that you select both Full msdb log and Full tempdb. A full msdb log halts the execution of all automated tasks because SQL Server can't make any log entries until you clear the log or allocate more space. A full tempdb stops the server from executing any transactions that require space in tempdb. I also recommend that you notify the operator for errors with a severity of 19 or higher, which indicate problems in system resources or batch termination. Errors with severity levels of 20 or higher are fatal errors that can crash the server. Each severity-level alert indicates that a server might be down or in trouble.
Prev. page
[1]
2
3
next page 
