This blog discuss about one of the most useful and important features of MS SQL server which is Database Mail. The feature helps users to send query results and attachments from SQL Server via E-mail. There are multiple components work together to get this work done. We will see each component and end-to-end architecture of this process.
Architecture:
Fig 1
Database Mail Architecture
Database Mail Components:
MSDB
Profile and accounts
Stores Procedure (sp_send_dbmail)
Mail Queue
Status Queue
External Program
MSDB: MSDB is the core component of db mail functionality. All the required elements that are used by Database mail such as Stored procedure, Configuration Details, Views are present in this DB.
Profile and account:
Since DB Mail uses SMTP server to send mail, we first need to create a profile that hold information about SMTP server that can be used to send mail from. Each profile can have more than one account to ensure mail delivery without any failure. Multiple profiles can be created and we can choose a profile from which we want the mail to be sent.
Stores Procedure:
Sp_send_dbmail acts as an ignition to start mail delivery process. It is a stored procedure that can be found in MSDB. When this SP is executed with all the required parameters such as recipient list, mail subject, mail body, attachment if any, the mail will be added into mail queue and processed by an external process. Executing below script will send mail with simple message in mail body.
Exec msdb.dbo.sp_send_dbmail
@profile_name=<Mail profile which can be used for sending the mail among the available profiles>
@recipients=<Recipients IDs separated by semicolon>
@body=<Mail Body>
@subject=<Mail Subject>Mail Queue:
On successful execution of sp_send_dbmail, the mail details will be added into mail queue for further processing by an external program.
External Program:
Once a mail is added into queue, database mail will start an external program which then communicates with SQL server and process the E-mail messages. This is an executable file that can be found in below file path.
C:\Program Files\Microsoft SQL Server\SqlInstanceName\MSSQL\Binn
Status Queue:
Status queue will be updated by an external program, once it is done with processing the mail objects, to indicate the outcome. This new entry in Status queue will initiate internal stored procedure to update mail object with corresponding outcome (Sent or Unsent).