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.