Perhaps one of the more compelling hands-off administration abilities of SQL Server is the SQL Mail capability. SQL Mail lets you send, receive and process mail from within SQL Server. The most common use of this is setting up the SQL Agent process to alert you to server conditions. Since many wireless devices (phones, pagers, etc.) now support an email address, it’s possible to easily have the server notify you of system problems automatically.

This really supplements your downtime management of the server. Since you can set up alerts and use operators to watch over the system, you can quickly significantly decrease the time required for notification.

SQL Mail is typically used in one of two different ways. First, it’s used as a component of the SQL Agent services, providing the means for operators to be notified by the system.

Secondly, SQL Mail can be used by your applications through the use of extended stored procedures. You can start and stop the services, send and receive mail, etc.

The first thing you’ll need to know is what type of mail server you’ll be using to handle the mail – from there you can set up the different services and options that you need to support your server.

Steps to using SQL Mail:

1. Select the mail server you’ll be using – you’ll need to know the type of server (Exchange, SMTP/POP3, etc.), the name of the server and the profile of the user you want to use for the configuration.
2. Configure SQL Mail to use that server and the user information you’ve set up for access, and
3. Set up SQL Agent operators
4. Set up stored procedures that will use the extended stored procedure approach.

Items 3 and 4 are not both required – you will have to determine how you’ll be working with SQL Mail and choose one or both of these options.

SQL Mail Configuration

SQL Mail consists of a small set of items that must be configured to know the accounts and servers that will be used to send and receive mail. It simply consists of a mail service setup on the SQL Server along with SQL Mail configured to use that service.

SQL Mail can be configured to use either Exchange or a simple POP3/SMTP server. Exchange is the most prevalent at configuration for it is much easier, so it is recommended to use Exchange if there is an option

Configuring SQL Mail for Exchange

SQL Mail for Exchange utilizes a mailbox setup on the Exchange server, a profile pointing to that mailbox, an Exchange client, SQL Server services running under named user accounts, and the SQL Mail service within SQL Server.

Most installations will have the services for SQL Server setup under the local system account. This offers the greatest amount of network protection, but it prevents some of the ancillary services such as SQL Mail from working.

The Exchange mailbox is setup for a specific network login and so the local system account can not be used. Instead you must use a named NT account. You still want to protect your network, so it is recommended that you create an account specifically for the use of SQL Mail. This account must have administrator privileges on the SQL Server machine, so it should be added to the local administrator group. However, you should also explicitly revoke any access to other machines or resources on the network for this account. This prevents an unauthorized user from accessing your network using this account.

Configuring and Managing SQL Mail 1 2 3 4 5 6 7