Database mail is newly introduced concept in SQL Server 2005 and it is replacement of SQLMail of SQL Server earlier version.
The main purpose of Database Mail is sending e-mail messages from the SQL Server Database Engine to users. We can send a text message, query result, failure alerts, file as attachment.
The database mail can be used to notify users or administrators regarding events raised in SQL Server.
To send Database Mail, you must be a member of the Sys admin <or >DatabaseMailUserRole database role in the msdb database.
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.
Database mail depends on Service Broker so this service must be enabled for Database Mail.
Database Mail run outside of SQL Server so it is not pressure on your SQL Server Engine. In other hand 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.
It also is support clustered environment and can use SMTP Servers. On the other hand it sends mails asynchronously with Service broker so there will be no waste time and it has some security maintenance which let you filter messages.
Database Mail does not support receiving incoming e-mail messages. Receiving e-mail can present a security risk to the database server through the use of Denial-Of-Service (DOS) attacks or messages that contain harmful Transact-SQL like DROP DATABASE.
It allows to create multiple email profiles in single instance.
Main components of database mail:
MSDB Database : The msdb database acts as the mail-host database that consists of all stored procedures, system tables, and database roles related to database mail. and it holds the messaging objects that Database Mail uses to send e-mail. These objects include the sp_send_dbmail stored procedure and the data structures that hold information about messages.
Database Mail records logging information in the msdb database and the Microsoft Windows application event log.
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.
Database Mail executable <or>DatabaseMail.exe: The Database Mail executable is an external program that reads from a queue in the msdb database and sends messages to e-mail servers.
This file[DatabaseMail.exe] is present in the Binn folder of the respective instance. It is the database mail engine.
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.
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.
Inserting the new entry in the mail queue starts the external Database Mail process (DatabaseMail.exe).
The external process reads the e-mail information and sends the e-mail message to the appropriate e-mail server or servers.
The external process inserts an item in the Status queue for the outcome of the send operation.
Inserting the new entry in the status queue starts an internal stored procedure that updates the status of 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.
Database Mail views provide the status of messages for troubleshooting, and stored procedures allow for administration of the Database Mail queue.
Another way to explain is 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
The main purpose of Database Mail is sending e-mail messages from the SQL Server Database Engine to users. We can send a text message, query result, failure alerts, file as attachment.
The database mail can be used to notify users or administrators regarding events raised in SQL Server.
To send Database Mail, you must be a member of the Sys admin <or >DatabaseMailUserRole database role in the msdb database.
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.
Database mail depends on Service Broker so this service must be enabled for Database Mail.
Database Mail run outside of SQL Server so it is not pressure on your SQL Server Engine. In other hand 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.
It also is support clustered environment and can use SMTP Servers. On the other hand it sends mails asynchronously with Service broker so there will be no waste time and it has some security maintenance which let you filter messages.
Database Mail does not support receiving incoming e-mail messages. Receiving e-mail can present a security risk to the database server through the use of Denial-Of-Service (DOS) attacks or messages that contain harmful Transact-SQL like DROP DATABASE.
It allows to create multiple email profiles in single instance.
Main components of database mail:
MSDB Database : The msdb database acts as the mail-host database that consists of all stored procedures, system tables, and database roles related to database mail. and it holds the messaging objects that Database Mail uses to send e-mail. These objects include the sp_send_dbmail stored procedure and the data structures that hold information about messages.
Database Mail records logging information in the msdb database and the Microsoft Windows application event log.
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.
Database Mail executable <or>DatabaseMail.exe: The Database Mail executable is an external program that reads from a queue in the msdb database and sends messages to e-mail servers.
This file[DatabaseMail.exe] is present in the Binn folder of the respective instance. It is the database mail engine.
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.
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.
Inserting the new entry in the mail queue starts the external Database Mail process (DatabaseMail.exe).
The external process reads the e-mail information and sends the e-mail message to the appropriate e-mail server or servers.
The external process inserts an item in the Status queue for the outcome of the send operation.
Inserting the new entry in the status queue starts an internal stored procedure that updates the status of 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.
Database Mail views provide the status of messages for troubleshooting, and stored procedures allow for administration of the Database Mail queue.
Another way to explain is 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
No comments:
Post a Comment