Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Database Mail set up in SQL Server 2008

by Abi Chapagai – Saturday, May 08, 2010

I have recently set up database mail in SQL Server 2008 in production SQL server Instance. I would like to share what I have learnt and how did I set up this powerful feature of sending notifications in SQL server 2008.

Introduction

This article discusses about setting up and configuring SQL Server 2008 Database Mail feature within SQL Server Instance. Database mail was introduced in SQL server 2005 and it was a new feature that Microsoft has added in SQL Server 2005. Database mail is also available in SQL Server 2008. I did not find any difference in Database mail in 2005 and in 2008. Database mail in SQL Server 2008 is an enterprise solution that is used to send emails from Database Engine component of SQL Server. Basic function of SQL Server 2008 Database Mail is to send alert messages to database administrators with issues related to performance, disk space, SQL Jobs, Backup plans, and other changes in the database schema. This is a very neat feature that helps database administrators to keep track of all activities in SQL Server Instances across all servers in the network. Database mail has better performance, easy to set up and more reliable than SQL Mail in SQL Server 2000.  SQL Mail uses MAPI where as Database Mail uses SMTP. Another main point to remember on database mail is, it uses service broker service and this service need to be enabled in order to use Database Mail in SQL Server 2008. By default, database mail will not be enabled; there are different ways to configure it. We can enable it using system stored procedure, configuration manager or from the Database Mail Wizard during the set up. I am using database mail set up wizard to enable it which will be shown below (Figure 3: Enable Database Mail Feature). In setting up database mail, profile creation, account set up, configuring the account and sending test email are the main steps. Each of these steps are explained below.

Note: Database mail feature in SQL Server 2008 is not available in Express Edition.

Create Mail Profile and Account:

First step in Database Mail set up is creating a profile. Mail profile is the main element of Database Mail.  A profile can have multiple email accounts added in that profile. Profile can be of two types, and they are:

Public Profile: A public profile is a mail profile set up in the database mail which can be accessed by any users and these users will have the ability to send emails.

Private Profile:  A private profile is that profile in which users who are granted access to this profile can use to send emails. 

Steps:

  1. Connect to the SQL Server Management Studio.
  2. In the Object Explorer, Go to Management Node,  expand Management Node, and Point to Database Mail option as shown in the figure below:  

Figure 1: Database Mail Folder Screenshot

  1. Right Click on Database Mail, and then Click on Configure Database Mail as shown below.

 

Figure 2: Configure Database Mail Screenshot

  1. Once Configure Database Mail option is clicked, Database Mail Configuration Wizard  appears.  This wizard helps to set up the database mail in SQL Server Instance.  
  2. Click Next on Database Mail Configuration Wizard as shown above. 
  3. Select Configuration Task window comes when Next button is clicked as shown above. Since am setting up Database Mail for the first time therefore am selecting the Set up option as shown in the figure below.  In this set step, new profile can be created, SMTP account will be added, security in the mail profile can be set up and system parameters can be configured.  Click Next after select the set up option.

Note:  In case, if Database Mail is already setup, we can choose to manage database mail and changing system parameters.  

 

Figure 4: Select Configuration Task

  1. When you click Next, Following window comes since Database Mail is not enable on my instance so am going to enable it from here instead of using system stored procedure or configuration manager as I mentioned earlier.

 

 Figure 5: Enable Database Mail Feature

  1. Click Yes as marked in Red to enable the Database Mail and the next comes a window where we can create New Profile for the Database mail and then add new SMTP accounts for this profile.  Profile name is used by different users to send out email notifications.  As shown in the figure below, Profile name and Description of the Profile are created. The name of the Profile that I used is:  SQLServer2008 Database Mail Profile. Description is an optional but it is good to add description about the profile. I have added a short description as shown in the figure.

 

9. After profile name and description of the profile is added, we can add the SMTP Accounts by Clicking on Add…. button as shown in New Database Mail Account Window figure below.

Account name is Database Mail, Description is an optional.  I have used my Gmail account as an Outgoing SMTP since I do not have mail server set up on my machine. 

Under Outgoing Mail Server SMTP:

E-Mail Address:   abi.chapagai@gmail.com

Display Name: SQL Server

Reply Email:  It can be blank or we can use same email as above.

Server Name:  smtp.gmail. com, this is SMTP server. 

Port Number:  Gmail smtp server port number to be used is 587 but default port number is 25.

Secure Connection: We have to select SSL connection as shown in the picture for gmail.

Basic Authentication:  Provide gmail account and password for this account.

 

 Figure 6: New Database Mail Account Screenshot

10.  Click OK  to go back to New Profile window as shown below.

 

Figure 7: Profile Window with SMTP Accounts Screenshot

11.  Now, SMTP account details are added in the profile.  Click Next to  Go to Manage Security Window as shown below.

 

Figure 8: Manage Profile Security Screenshot

In this profile security management window, there are two options for selecting profiles which users are going to have access. They are public and private as shown in the figure 6. Public profile can be accessed by all users, whereas private profile is accessed by only specific users.  

I have used Public profile where I have chosen Public profile. Also have to make this profile default, so have to select Yes option from the drop down menu from Default profile as shown in Figure 6.  

12.  Click Next button to go to Configure System Parameters window as shown below.

 

Figure 9: Configure System Parameters Screenshot

            In this window, we can see the information about how many times we can retry to get the mail, how long to wait when we retry the mail, maximum email size, what are the prohibited attachment extensions, Minimum life time for Database Mail and the logging level.  Default prohibited attachment extensions are: exec, dll, vbs, and js.  I have added two more extensions that I do not want to get the attachment from and they are; com and bat, which are shown in the picture above. 

To add more prohibited attachment file extensions, click on … on the right side of Configure System Parameters Window on Prohibited Attachment file Extensions under Systems Parameters column which is shown in Figure 7.  Once you add more extension values, Click OK to go back to Configure System Parameters Window as shown below.  There are three levels of logging namely normal, verbose and extended.

I am using extended logging level as is the default one.

 

Figure 10: Configure System Parameters Screenshot with Logging Level Configuration

13.  Click Next to go to the Confirmation Window for Database Mail Set up and is shown below.  Click Finish to complete the Database Mail set up steps.

 

Figure 11: Completing Database Mail Set up Screenshot

Once finished button is clicked following Configuring window comes where we can see the Action, status and messages. If there are errors, there will be error messages.  Click Close button to close the Database Mail Configuration Wizard.

 

Figure 12: Configuring Database Mail screenshot

Send Test Email

To send the test email, go to Database Mail as shown in (Figure 1: Database Mail Folder Screenshot)

Right click on Database Mail folder and the Click on Test Email … as shown in figure below.

 

Figure 13: Send Test Email Screenshot

After Send Test E-Mail is clicked, following screen comes where Database Mail Profile, to which email address this message will be sent, subject of the message and body of the test email. I am using my email address, abi.chapagai@gmail.com where the email will be sent. Click on Send Test E-Mail and this email will go to my Gmail inbox.

 

Figure 14: Sent Test Email Screenshot

Here is the email that I got after clicking the Test E-Mail in my Gmail inbox.

Figure 15: Screenshot of Gmail Inbox with Message from DB Mail

Conclusion

Setting up database mail in SQL Server 2008 is very straightforward and easy to set up.

Comments

Posted by dhlotter on 12 May 2010

Nice post! Thanks.

Posted by Justin Hostettler-Davies on 13 May 2010

Cheers, as Abi mentions SQL Server 2008 DB Mail shouldn't be too much of a nightmare to configure and use. Let us know how you get on.

databaseexpertise.com/.../sql-server

Posted by Abi Chapagai on 13 May 2010

Justin/dhlotter,

Thank you for the comments. Yes, SQL Server 2008 DB mail is easy to set up and use. I am enjoying it, it is a neat feature.

Thanks for reading.

Posted by Alma Jones on 17 May 2010

On the outgoing mail, how do I set Reply e-mail to "do not reply"?

Posted by Anonymous on 17 May 2010

Pingback from  Good enough resume for sporting goods store? | Martial Arts Training Gear

Posted by Abi Chapagai on 1 June 2010

Hi Alma:

I am not sure how can we set this up. I will see if i can set it up that way and let you know. May be someone has set this up. Thanks for the question.

Posted by Chunsong on 8 June 2010

Thanks, it helped me a lot

Posted by mail.harish.kala on 22 April 2011

Thanks, It is working Perfectly !!

Posted by Naresh Parmar on 23 October 2012

hi,

i want to edit existing profile.

what should i do ??

Thanks in advance.

Posted by Gary Nease on 18 November 2012

Naresh: RightClick "Database Mail", select "Configure Database Mail".  Skip the Welcome page. On "Select Configuration Task", select the radio-button for "Manage Database Mail accounts and profiles"; "Next >". On "Manage Profiles and Accounts", select radio button for "View, change, or delete and existing account"; "Next >".  It should be apparent from here.

Posted by art-bits on 18 January 2013

I'm working on 70-432. The author gives just half an example. Your article shows me how to do it without a DA in my VM. Great Job.

thx

Posted by art-bits on 18 January 2013

I'm working on 70-432. The author gives just half an example. Your article shows me how to do it without a DA in my VM. Great Job.

thx

Posted by awaissarmast on 24 September 2013

it work nice one

but I want to send mail from database

selecting from table , and if date is mention in the table send mail on that day

can any one know about this

Leave a Comment

Please register or log in to leave a comment.