Database Mail

• It is an enterprise solution for sending mails from SS db engine to SMTP servers.
• It uses SMTP server to send mail.
• SS 2000 supports SQL Mail which supports MAPI profiles to send email instead of SMTP server.
• Main components of db mail
•  sp_send_dbmail  
•  msdb
• service broker
•  DatabaseMail.exe (Present in Binn folder of respective instance)
• Database Mail Architecture

 FAQ:- What is difference between SQL Mail and DatabaseMail?

                        SQL Mail                                                                                  DatabaseMail
1. Introduced in SQL Server 2000                                            1. Introduced in SQL Server 2005
2. Uses Exchange Server to send mail.                                       2. Can interact directly with SMTP server.
3. Database Engine sends the mails.                                           3. Uses separate process 
                                                                                                     (DatabaseMail.exe) to send mails
4. Less performance.                                                                 4. High performance.
5. Not cluster aware feature.                                                      5. Cluster aware feature.



FAQ:- How to enable Service Broker in MSDB?

USE [master]
GO
ALTER DATABASE [MSDB] SET  ENABLE_BROKER WITH NO_WAIT
GO

MSDB Tables related to Database Mail

1. sysmail_profile                      : Consists of all the profiles information
2. sysmail_account                    : Consists of SMTP server accounts information.
3. sysmail_server                      : Consists of SMTP server details.
4. sysmail_allitems                    :  Mail sent status. If the sent_status is 1 then success otherwise failed.
5. sysmail_log                          :  To check the errors raised by Database Mail feature.
6. sysmail_configuration           :  Consists of system parameters details.


Steps to configure
1. Enable db mail feature at server level

    sp_configure 'Database Mail XPs',1
    reconfigure

2. Enable service broker in msdb database.

USE [master]
GO
ALTER DATABASE [MSDB] SET  ENABLE_BROKER WITH NO_WAIT
GO

3. Configure mail profile (Profile is a collection of Accounts)
4. Add SMTP account(s)
5. Make the profile as private or public

    * Private profile can be used by
        * sysadmin members and
        * databasemailuserrole members of msdb

6. Set the parameters
7. Send the mail

Example

• Go to Management --> Right Click on Database Mail --> Configure Database Mail

• Next
• Select the following option

• Next
• Enter profile name = SQLProfile
• Click on Add
• Select New Account if it was propted. Otherwise enter the following details

• Click OK
• Next
• Make the profile as public and default as follows
• 
  
• 
•  Next
• Next 
• Finish
•  

Testing Database Mail

• Right click on Database Mail --> Send Test Email and Enter the following
• 
•             Click Send Test E-Mail. 
  • To verify the mail status
  
•       Now verify from Email Box
  
   
  ·         Sending mail manually by calling sp_send_dbmail
  use MSDB
  go
  sp_send_dbmail @profile_name='SQLProfile',
                @recipients='info@optimizesql.com',
                @subject='From SQL Server',
                @body='Database Mail Testing...'