Database Mail

 Introduction

The database mail can be used to notify users or administrators regarding events raised in SQL Server. For example, if an automation process like replication, database mirroring fails or there are latency related problems then SQL Server can use this feature to notify the administrators or operators. We can send a text message and HTML, query result, file as attachment

Points to Remember

·          Like SQL Mail, database mail doesn’t require a MAPI – a compliant mail server like Outlook         Express or extended programming interface.

·         Better performance. Impact of sending mails to SMTP servers by SQL Server is reduced as this     task is implemented by an external process initiated by the DatabaseMail.exe file.

·         Works fine in a cluster based environment.

·         64-bit support.

·         Database mail configuration information is maintained in an MSDB database.

·         Only members of SysAdmin and DatabaseMailUserRole database role of MSDB can send          mails by default.

·         Allows sending messages in different formats like text and HTML.

  Supports logging and auditing features through different system tables of MSDB

The main components of database mail are: 

·         Sp_send_dbmail

This is a system defined stored procedure which is used by SQL Server to send email using the database mail feature. This stored procedure is present in the MSDB database.

·         MSDB Database 

Consists of all stored procedures, system tables, and database roles related to database mail.

·         Service Broker

To establish communication between the SQL Server engine and the database mail engine we need a service broker. It submits the messages to the mail engine.

·         DatabaseMail.exe

This file is present in the Binn folder of the respective instance. It is the

database mail engine.

                                 Figure – 1 (Source: BOL) Database Mail Architecture

How it works? 

When a run time error occurs due to any automated task like backups, replication etc database engine raise the error and same information is submitted to Database Mail engine, then database mail engine will submit the mail to SMTP Server using EmailID and Password mentioned in profile. At the last SMTP Server sends mail to recipients.

Error  --> DB Engine  --> DB Mail Engine --> SMTP Server --> Recipients

How to enable a Service Broker in MSDB?

USE [master]

GO

ALTER DATABASE [MSDB] SET ENABLE_BROKER WITH NO_WAIT

GO

Configuring Database Mail

1.       Go to Object Explorer

2.       Management

3.       Right click on Database Mail and select “Configure Database Mail” as follows

4.      Next

5.      Select “Setup Database Mail by performing the following tasks” as follows

6.       Next

7.       Enter profile name = “SQL Profile” and description as follows

8.      Click on “Add” button and enter the following details. Always use your own email ID. Generally we have to use here the company email id. We have to raise a ticket to the mail server admin team to get the following details.

o    Separate email id for SQL Server (This is From Email ID)

o    SMTP server name

o    Port number

o    SSL feature should be enable or disable.

Here I am using my personal email id. In Basic Authentication option enter the same email ID along with the valid password of the email ID.

9.       OK

10.       Next

11.       Under Manage Profile Security option make the profile as public by selecting checkbox and default as follows

12.       Next

13.       Accept the default settings for System Parameters as follows

14.Next

15.Finish

. 16.     Close.

17.       We have configured database mail feature successfully. Let's test the mail feature as follows.

18.       Go to Object Explorer -> Management -> right click on Database Mail -> Send Test Email

   19.    Enter the following details. You can use required values.

o           Select Profile name:  SQLProfile

o          To:  ask.sqlserver@gmail.com  (Any Email ID- generally it should be administrators group email     ID) 

o         Subject: Hi, Backup Alert 

o         Body: Backup of master database was generated successfully.

.20.     Send Test Email -> OK 

21.       Verifying whether the mail was send successfully or not. Take new query and run the following command and check sent_status column value for your mail as follows

.     22.  Check your mail box you can find new Email from SQL Server.