Database Mail Architecture in SQL SERVER
SMTP Server Details
Hotmail SMTP server name: smtp.live.com Port number: 587
Gmail SMTP server name: smtp.gmail.com Port number: 587
Yahoo SMTP server name: smtp.mail.yahoo.com Port number : 25
AOL SMTP server name: smtp.aol.com Port number : 587
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.
EXECmsdb.dbo.sp_send_dbmail
@profile_name=N'Database Mail Profile',
@recipients = @To,
@subject = @Subject,
@body = @Body
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @@SERVERNAME,
@recipients = 'xxx@email.com',
@query = 'SELECT name FROM master..sysdatabases',
@exclude_query_output = 1
10.10.10.1
Database Mail consists of the following main components:
- Configuration and security components
Database Mail stores configuration and security information in the msdb database. Configuration and security objects create profiles and accounts used by Database Mail.
- Messaging components
The msdb database acts as the mail-host database that 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 executable
The Database Mail executable is an external program that reads from a queue in the msdb database and sends messages to e-mail servers.
- Logging and auditing components
Database Mail records logging information in the msdb database and the Microsoft Windows application event log.
Difference between Database Mail and SQL Mail
1) Database mail is newly introduced concept in SQL Server 2005 and it is replacement of SQLMail.
2) Database Mail is based on SMTP (Simple Mail Transfer Protocol) and also very fast and reliable where as SQLMail is based on MAPI (Messaging Application Programming Interface).
3)SQL Mail needs an email client (like Outlook/Express) to send and receive emails, where as Database mail works without any Email client.
4) SQL Mail works with only 32-bit version of SQL Server, where as Database Mail works with both 32-bit and 64-bit.