Click here to Skip to main content
Click here to Skip to main content

Configuring Database Mail in SQL Server

, 30 Dec 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
This is an enterprise solution for sending mails from the SQL Server database engine to SMTP servers.

Introduction

This is an enterprise solution for sending mails from the SQL Server database engine to SMTP servers.  SQL Server database applications can communicate with users through an email system. It provides features like scalability, security, and reliability. 

It uses an SMTP server to send mail. SQL Server 2000 supports SQL Mail, which supports MAPI profiles to send email instead of an SMTP server. SQL Mail requires a MAPI-compliant mail server (Microsoft Exchange Server) and a MAPI client (Microsoft Outlook).

We can send a text message, query result, file as attachment. The database mail can be used to notify users or administrators regarding events raised in SQL Server. For example, if an automation process like replication, database mirroring fails or there are latency related problems then SQL Server can use this feature to notify the administrators or operators.

Points to Remember

  • Like SQL Mail, database mail doesn’t require a MAPI – a compliant mail server like Outlook Express or extended programming interface.
  • 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.
  • Works fine in a cluster based environment.
  • 64-bit support.
  • Database mail configuration information is maintained in an MSDB database.
  • Only members of SysAdmin and DatabaseMailUserRole database role of MSDB can send mails by default.
  • Allows sending messages in different formats like text and HTML.
  • Supports logging and auditing features through different system tables of MSDB.

The main components of database mail are: 

  • 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.

  • MSDB Database 
  • Consists of all stored procedures, system tables, and database roles related to database mail.

  • 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.

  • DatabaseMail.exe
  • This file is present in the Binn folder of the respective instance. It is the database mail engine.

http://3.bp.blogspot.com/-cxlUgtqozvQ/Td3ppkZcm9I/AAAAAAAAAA8/Zn46WnRA1Gs/s1600/dbmail.gif

Figure – 1 (Source: BOL) Database Mail Architecture

How it works? 

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

FAQ: How to enable a 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 parameter details.

Steps to configure

  1. Enable the db mail feature at server level
  2. .
    sp_configure 'Database Mail XPs',1
    reconfigure
  3. Enable service broker in the MSDB database.
  4. USE [master]
    GO
    ALTER DATABASE [MSDB] SET  ENABLE_BROKER WITH NO_WAIT
    GO
  5. Configure mail profile (profile is a collection of accounts).
  6. Add SMTP account(s).
  7. Make the profile private or public.
  8. Private profile can be used by:

    • sysadmin members and
    • databasemailuserrole members of MSDB
  9. Set parameters.
  10. Send the mail.

Example: Configuring Database Mail

  1. Go to Object Explorer
  2. Management
  3. Right click on Database Mail and select “Configure Database Mail” as follows
  4. Next
  5. Select “Setup Database Mail by performing the following tasks” as follows
  6. Next
  7. Enter profile name = “SQL Profile” and description as follows
  8. Click on “Add” button and enter the following details. Always use your own email ID. Generally we have to use here the company email id. We have to raise a ticket to the mail server admin team to get the following details.
    • Separate email id for SQL Server (This is From Email ID)
    • SMTP server name
    • Port number
    • SSL feature should be enable or disable.

    Here I am using my personal email id. In Basic Authentication option enter the same email ID along with the valid password of the email ID.

  9. OK
  10. Next
  11. Under Manage Profile Security option make the profile as public by selecting checkbox and default as follows
  12. Next
  13. Accept the default settings for System Parameters as follows
  14. Next
  15. Finish
  16. Close.
  17. Observations

    Use msdb
    Go
    --Step1: Varifying the new profile
         select * from sysmail_profile
    --Step2: Verifying accounts
         select * from sysmail_account
    --Step3: To check the accounts of a profile
         select * from sysmail_profileaccount
         where profile_id=3
    --Step4: To display mail server details
    select * from sysmail_server
  18. We have configured database mail feature successfully. Let's test the mail feature as follows.
  19. Go to Object Explorer -> Management -> right click on Database Mail -> Send Test Email
  20.  Enter the following details. You can use required values.
    • Select Profile name:  SQLProfile
    • To:  ask.sqlserver@gmail.com  (Any Email ID- generally it should be administrators group email ID) 
    • Subject: Hi, Backup Alert 
    • Body: Backup of master database was generated successfully.
  21. Send Test Email -> OK 
  22. Verifying whether the mail was send successfully or not. Take new query and run the following command and check sent_status column value for your mail as follows
  23. Check your mail box you can find new Email from SQL Server.

Sending Mail using SP_SEND_DBMAIL 

We can send the mail programmatically from any stored procedure, job or batch file using sp_send_dbmail system procedure. Before sending the mail we can check the required parameters by viewing the definition of stored procedure.

We have to pass the following parameters to the above stored procedure.

  • Profile_name (We can mention the above profile name which we have created)
  • Recipients (We can mention multiple recipients by separating with Wink | <img src= " src="http://www.codeproject.com/script/Forums/Images/smiley_wink.gif" />
  • Subject 
  • Body 

Example 1:  Sending mail using the sp_send_dbmail stored procedure

Step 1:

Step2: Verifying using sysmail_allitems. Here check the sent_status column of last Email.

use msdb
go
select * from sysmail_allitems

Example 2:  Sending mail using sp_send_dbmail stored procedure which has query result as  message.

Step 1: Sending mail (I am using emp table present in Test database)

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLProfile',
@recipients = 'skm.mcp@gmail.com',
@query = 'SELECT COUNT(*) as No_Empls FROM 
            Test.dbo.emp',
@subject = 'No of Employees Working';

Step 2: Verifying using sysmail_allitems. Here check the sent_status column of last Email.

use msdb
go
select * from sysmail_allitems   

Configuring Database Mail Using T-SQL Script

--Enabling Database Mail
sp_configure 'show advanced options',1
reconfigure										  go											  sp_configure 'Database Mail XPs',1							  reconfigure 
--Creating a Profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'SQLProfile',
@description = 'Mail Service for SQL Server' ;
-- Create a Mail account for gmail. We have to use our company mail account.
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'SQL_Email_Account',
@email_address = 'youremail@gmail.com',
@mailserver_name = 'smtp.gmail.com',
@port=587,
@enable_ssl=1,
@username='youremail',
@password='Emailid password'
-- Adding the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'SQLProfile',
@account_name = 'SQL_Email_Account',
@sequence_number =1 ;
-- Granting access to the profile to the DatabaseMailUserRole of MSDB
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'SQLProfile',
@principal_id = 0,
@is_default = 1 ;
--Sending Test Mail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLProfile',
@recipients = 'youremailid@xxxx.com',
@body = 'Database Mail Testing...',
@subject = 'Databas Mail from SQL Server';
--Verifying, check status column
select * from sysmail_allitems 

Summary   

Database Mail feature was introduced in SQL Server 2005 version, which can be used to notify the administrators/operators. It provides better performance as well as cluster aware feature.     

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Kareem.S
Instructor / Trainer OptimizeSQL Technologies
India India
Mr. Kareem Syed is a senior SQL Server Trainer and working as Technical Consultant.
 
You can visit my blog at
www.optimizesql.blogspot.in
Follow on   Twitter

Comments and Discussions

 
QuestionNotify when cannot connect to SMTP server Pinmemberahmadmamed9-Apr-14 18:50 
Questionproblem in setup DataBase Mail Sql Server Pinmembermoeinmohebbi28-Dec-13 2:39 
AnswerRe: problem in setup DataBase Mail Sql Server PinmemberKareem.S29-Dec-13 16:45 
GeneralMy vote of 5 Pinmemberarcher00526-Aug-13 1:01 
QuestionDatabaseMail in SQl Server 2008 Job schedule -Send e-mail PinmemberMember 29609084-Aug-13 18:48 
GeneralMy vote of 5 PinmemberMember 840764725-Apr-13 14:18 
QuestionManagement - Database Mail [modified] Pinmemberkiquenet.com31-Dec-12 1:49 
QuestionIs ENABLE_BROKER really needed? PinmemberMark Freeman20-Dec-12 4:29 
I have been setting up database mail on various SQL Server 2005 and 2008R2 instances over the last year or so and have never used the command
ALTER DATABASE [MSDB] SET ENABLE_BROKER WITH NO_WAIT
These were standard edition instances that only had the Database Engine installed. Is this command really needed, or is it executed behind the scenes by
EXEC sp_configure 'Database Mail XPs', 1;
or
EXEC sp_configure 'Agent XPs',1;
or some other part of setting up this feature?
Thanks,
Mark

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141223.1 | Last Updated 30 Dec 2013
Article Copyright 2012 by Kareem.S
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid