Home » SQL Server » How Database Mail works in SQL Server ?

How Database Mail works in SQL Server ?

Database Mail is email feature in SQL Server from SQL Server 2005 onwards. This feature used for mailing alerts, notification, reports etc.

Requirements for Setting up Database Mail :-

1) Details of SMTP server

2) Connectivity with SMTP server

3) Access to SMTP server to send e mails

Components of Database Mail :-

1) Configuration & Security Details : Database Mail configuration details like user details, SMTP Server IP, Port No., profile name, default profile etc. will be stored in MSDB database.

2) Messaging components : MSDB database stored several tables & store procedures for Database Mail to send e-mail. The sp_send_dbmail stored procedure is used to send emails.

3) Database Mail executable : The Database Mail feature is something that is not inside in MSDB & SQL Server. MSDB connects to external executable DATABASEMAIL90.exe to send mails using configured SMTP server.

4) Logging and auditing components : MSDB database stored several tables to store Database Mail records logging information. Information like message status (Send / Failed / Pending), attachment details etc. saved in logging tables.

Completed Process of Database Mail :-

1) Database Mail feature use service broker for its functionality. When you execute sp_send_dbmail stored procedure to send email, it inserts a record into mail queue that contain e-mail message.

2) Entering a new record in the mail queue activates the external Database Mail executable (DatabaseMail90.exe).

3) Database Mail executable (DatabaseMail90.exe) reads e-mail details & sends the e-mail message to the appropriate e-mail server or servers.

4) Database Mail executable (DatabaseMail90.exe) inserts a record in the Status queue for the outcome of the each email send operation.

5) Inserting the new record in the status queue trigger an internal stored procedure that updates the status(send / Pending / Failed) of the e-mail message