Step by Step setting up Database Mail and Creating alerts in SQL Server 2008 R2

Database Mail

Database Mail is designed for reliability, scalability, security, and supportability.

·         Database Mail uses the standard Simple Mail Transfer Protocol (SMTP) to send mail. You can use Database Mail without installing an Extended MAPI client on the computer that runs SQL Server.

·         Cluster support. Database Mail is cluster-aware and is fully supported on a cluster.

·         Multiple accounts: Each profile can contain multiple failover accounts. You can configure different profiles with different accounts to distribute e-mail across multiple e-mail servers.

·         SQL server database engine define rules for attachment, type and size limitation.

·         Database Mail keeps copies of messages and attachments sent in the msdb database. You can easily audit Database Mail usage and review the retained messages.

Database mail architecture

Database Mail is designed on a queued architecture that uses service broker technologies. When users execute sp_send_dbmail, the stored procedure inserts an item into the mail queue and creates a record that contains the e-mail message. Besides storing the sent, or unsent, e-mail message, Database Mail also records any e-mail attachments in the system tables. You can view mail log using following commands on msdb database

SELECT *

FROM sysmail_mailitems

SELECT *

FROM sysmail_log

Setting up Database Mail

Database Mail can be setup using SQL Server management studio and scripts. I am doing this using management studio

In database instance , go to the management and Configure database Mail.

If database mail is disabled, you will get message to enable it before configuration. You need to have Admin rights on msdb database.

.

During data mail wizard, You will setup your Profile name and SMTP setup. You can use Windows Auth and password to verify email account.

As a last step you can configure File size, retry attempts etc as shown in figure, After finishing all these steps, You will receive successful installation message.

To verify your configuration. You can send test email by right clicking on your profile name and press Send test email. You can Audit you mail history by using sysmail_log and review your profile setting using  sysmail_mailitems table in msdb.

Once you finish setting up Database Mail profile, To send SQL Job Notification or Alert you have to create new Operator.

Creating Operator

Create new operator using SQL Server management studio. 

You can setup schedule for your Operator by using Pager on Duty Scheduler 

Setup SQL Agent 

You have to enable and setup your Profile account along with SQL Agent to send email.

Press Include body of email in notification message will send message detail as email in case of error or alert. You can setup Fail-safe operator in case there is any problem in primary profile.

Once all setting has been completed. restart SQL Agent service to implement changes.

Creating Alert

Alert will send an email when TEMPDB database gets larger than 0 KB. 

Right Click Alerts and select New Alert

On the General tab properties are set to 

In Response Tab, You can run Job to accommodate Alert changing i.e Database shrinking job or you can generate email notification.

You can add error message in Alert body or Custom message can also be added

You can see last email time to verify either Alert is working fine. You can also view history using command given above in msdb.

Stop the Test

To stop the Alert from being sent every two minutes, you have two options disable the alert or provide a more appropriate size and response times.

To disable alert

• Right click on Alert name and select Disable

Email SQL Job Status

You can attach operator to send email notification on the failure, successful or completion of any SQL Agent job. In following image you can see email notification as well as Windows event log is being written on different status of SQL Job.

I tried to cover all possible scenario's about Database Mail setup and attaching it to send email notification in different ways. Normally we find all this information in number of posts. Hopefully it will help.