Database Mail vs SQL Mail in SQL Server

SQL Mail was what one would use to send emails from SQL Server in the SQL Server 2000 version. But in SQL Server 2005, SQL Mail is considered legacy. Microsoft introduced a better email component called Database Mail for SQL Server 2005.

Database mail is newly introduced concept in SQL Server 2005 and it is replacement of SQLMail of SQL Server earlier version. Database Mail has many enhancement over SQLMail. 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). Database mail depends on Service Broker so this service must be enabled for Database Mail. Database Mail can be encrypted for additional security. SQLMail is lesser secure as it can encrypt the message as well anybody can use SMTP to send email. Additionally, for MAPI to be enabled for SQLMail it will require Outlook to be installed. All this leads to potential security threat to database server.

why did Microsoft make the decision to move away from SQL Mail and introduce Database Mail?

1. SQL Mail was developed and runs in-process to SQL Server service. This means if SQL Mail goes down, your SQL Server also goes down. You don’t want this to happen on your production database because you will not have many happy users if that happens.
2. To use SQL Mail you need to install the extended MAPI client component on your server. How I usually do this is by installing Microsoft Outlook on the server. This will install the MAPI client component. Without MAPI, SQL Mail will not run.

What are the advantages of using Database Mail?

1. Unlike SQL Mail, Database Mail runs outside SQL Server in a separate process. So if Database Mail goes down, it will not bring down SQL Server.
2. Database Mail does not need Extended MAPI client components installed for it to run.
3. Database Mail can be configued with multiple SMTP accounts and with multiple profiles. This increases the robustness of the system. In a realtime environment, if one mail server goes down, Database Mail will still be able to send emails through the other configured SMTP accounts.
4. In Database Mail, all mail events are logged and you can view the mail history.
5. You can configure Database Mail to limit file sizes, disallowing large attachments and filtering of file extensions such as .exe or .bat

Where do you enable Database Mail and SQL Mail in SQL Server 2005?

Step1:  You do it through SQL Server 2005 Surface Area Configuration shown below. Click  "SQL Server Surface Area Configuration"

 Step2:  Click on Surface Area Configuration for Features as shown bellow screen.

 

Step 3:  Tick Enable Database Mail Stored Procedures to enable Database Mail in SQL Server and then click on OK button.

 

Step 4: Tick Enable SQL Mail Stored Procedures to enable SQL Mail in SQL Server and then click on OK button.

 Access Database Mail and SQL Mail and setup both in SQL Server Management Console, shown below.

Once that is setup, you can invoke Database Mail and SQL Mail to send emails and notification.

Database Mail:

 SQL Mail:

Where do you enable Database Mail and SQL Mail in SQL Server2008 ?

• Connect to the SQL Server Management Studio.
• In the Object Explorer, Go to root not i.e SQL Server node and Right-Click on it as shown below and point to the "Facets".

Click on Facets then it will show View Facets window as shown below.  Form Facet list select Surface Area Configuration.

Then it will display Surface area configuration for features of the Database Engine as shown below screen and the select DatabaseMailEnabled option and select true as shown in the below screen to enable Database email sending option and then click on OK button.

To enable SQL Email option follow the steps as shown below screen.

In summary, I suggest if you are using SQLMail, it is right time to upgrade to Database Mail.

If you still want to use SQLMail you will have to enable it with specific commands.

EXEC sp_configure 'SQL Mail XPs', 1
GO
RECONFIGURE
GO

 

Manage Profile Security(Database Mail)

Profiles are either public or private. A private profile is accessible only to specific users or roles. A public profile allows any user or role with access to the mail host database (msdb) to send e-mail using that profile.

A profile may be a default profile. In this case, users or roles can send e-mail using the profile without explicitly specifying the profile. If the user or role sending the e-mail message has a default private profile, Database Mail uses that profile. If the user or role has no default private profile, sp_send_dbmail uses the default public profile for the msdb database. If there is no default private profile for the user or role and no default public profile for the database, sp_send_dbmail returns an error. Only one profile can be marked as the default profile. To configure follow the bellow steps.

• Connect to the SQL Server Management Studio.
• In the Object Explorer, Go to Management Node,  expand Management Node, and Point to Database Mail option  and Right-Click on it as shown in the figure below:

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 as shown below.

 click on Next button shown above then it will show set of Set up options as shown in the figure below.

click on next button from the above screen then it will show Mange Profile Security window.  From the Manage Profile Security screen, select the target profile, then click in the Default Profile tab to activate the drop-down. Select Yes fromt the drop-down then click on next button as shown below screen.

Click on next button then it will show complete wizard window as shown below screen. From the Complete the Wizard screen, click on

Click on finish button then it will show status window as shown below.

Manage Profiles and Accounts (Database Mail)

Manage an existing Database Mail profile. A Database Mail profile is a collection of Database Mail accounts. Profiles improve reliability in cases where an e-mail server becomes unreachable, by providing alternative Database Mail accounts. At least one Database Mail account is required.  We can create new account ,new profile and we can view, change and delete existing account and profile. To do this follow the below steps.

• Connect to the SQL Server Management Studio.
• In the Object Explorer, Go to Management Node,  expand Management Node, and Point to Database Mail option  and Right-Click on it as shown in the figure below:

• 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 as shown below.

 

when Next button is clicked as shown above then it will show Set up option as shown in the figure below.  In this we can find 4 options 

1. In this first option, new profile and account can be created, SMTP account will be added, security in the mail profile can be set up and system parameters can be configured.  To configure this follow the below link
2. In the second option Manage Database Mail Accounts and  Profiles. Here we can create,view and delete the accounts and profiles.
3. In third option Manage Profile Security.
4. View or Change System parameters.

For the first step you can find here Configure Database Mail – Send Email From SQL Database Now we will know about second option i.e Manage Database Mail Accounts and  Profiles configuration follow the below steps. check the radio button Mange Database Mail Accounts and Profiles option form the Database Mail Configuration Wizard window as shown below and then click on next button.

 

When click on next button then it will show Manage profiles and accounts window as shown below.  Here you can find 4 options, 

1. Create a new account (you can find how to configure a new account link )
2. View,Change or Delete an existing account
3. Create a new profile (you can find how to configure a new Profile link )
4. View,Change or Delete an existing profile.  

Here i will describe about 2 and 4th options.

View,Change or Delete an existing account
To View,Change or Delete an existing account check the radio button and click on next button as shown below. 

When click on next button it will show Mange existing account window. Here you can change/edit or you can delete the accounts.  If more than one account has been created then select the account form the account name drop down list and the select delete button to delete of change the email address and passwords and then click on next button as shown in the below screen.

When your deleting any account which is associated with any one of the profile then it throw an error as shown below screen shot. 

If more than one account then follow the steps as shown in the below screen shot and then click on next button.

 When click on delete button and then account will be deleted form the list and click on next button it will show deleted accounts list as shown in the below screen shot.

Click on finish button then it will show successful message and deleted list or updated list as shown in the below screen shot.

View,Change or Delete an existing Profile
To View,Change or Delete an existing profile check the radio button and click on next button as shown below.

 It will show database mail configuration window as shown below.  We can delete profile.  If more than one profile then select the deleted one and then click on delete button as shown bellow. here either we can add new SMTP mail account or remove the existing account. 

When you click on add button it will show a window to add a mail account to the profile as shown below window.

When you click on new account then it will show a window to create a new database mail account as shown below screen shot.  To configure follow the same steps as shown in the previous article.

after configuring or modifying profile click on next button then it will show complete wizard window as shown below.

Click on finish button then it will show status of the configuration as shown below.

SQL SERVER – 2008 – Configure Database Mail – Send Email From SQL Database

Introduction:  

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.

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

What is Database Mail?

Database Mail is a mail queuing system. The email messages are stored in a queue within the msdb database waiting to be processed. When an email message is placed in the queue, an external process is triggered to send the email messages in the queue to the appropriate mail server. Once the email has been sent an email message with the status of the delivery is then posted back to SQL Server.

In order to send mail using Database Mail in SQL Server, there are 3 basic steps that need to be carried out. 1) Create Profile and Account 2) Configure Email 3) Send Email.

Step 1:  Create 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.

• Connect to the SQL Server Management Studio.
• In the Object Explorer, Go to Management Node,  expand Management Node, and Point to Database Mail option  and Right-Click on it as shown in the figure below:  

 

•  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 as shown below.

 

• Select Configuration Task window comes when Next button is clicked as shown above. Since i 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.

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.

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.

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.

Here provide the details like Account name is Birthday Mail, Description is an optional.  I have used my Gmail account as an Outgoing SMTP.
Under Outgoing Mail Server SMTP:

E-Mail Address:   abc@gmail.com
Display Name: Enter Display Name in Email
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:   default port number is 25 but Gmail smtp server port number to be used is 587 .
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.
and then Click OK  to go back to New Profile window as shown below screen shot. Now, SMTP account details are added in the profile.  Click Next to  Go to Manage Security Window as shown below.

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 below screen shot. 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 the below screen shot.

We can configure more than so select which profile you wants to use it and then click on next button.

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

 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 screen shot.  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.

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.

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.

Step 2) Configure Email:

After the Account and the Profile are created successfully, we need to configure the Database Mail. To configure it, we need to enable the Database Mail XPs parameter through the sp_configure stored procedure, as shown here:

sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO

Step 3) Send Email:
To send the test email, go to Database Mail as shown in Database Mail Folder Screen shot Right click on Database Mail folder and the Click on Test Email … as shown in figure below.

 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,abc@gmail.com where the email will be sent. Click on Send Test E-Mail and this email will go to  Gmail inbox and check the mail.

 There is another way to test the sending mails after all configurations are done, we are now ready to send an email. To send mail, we need to execute a stored procedure sp_send_dbmail and provide the required parameters as shown below:

USE msdb
GO
EXEC sp_send_dbmail @profile_name='BirthdayEmailSending',
@recipients='abc@gmail.com',
@subject='Birthday message',
@body='This is the body of the test message.
Many many happy returns of the day...'

After all validations of the parameters entered are done, certain stored procedures are executed and the mail is queued by Service Broker 

Database Mail keeps copies of outgoing e-mail messages and displays them in the sysmail_allitems, sysmail_sentitems, sysmail_unsentitems, sysmail_faileditems . The status of the mail sent can be seen in sysmail_mailitems table, when the mail is sent successfully the sent_status field of the sysmail_mailitems table is set to 1 which can again be seen in sysmail_sentitems table. The mails that are failed will have the sent_status field  value to 2 and those are unsent will have value 3.
The log can be checked in sysmail_log table as shown below: 

SELECT *
FROM sysmail_mailitems
GO
SELECT *
FROM sysmail_log
GO

After sending mail you can check the mail received in your inbox.

Reference Links: 

• http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/
• http://sql-articles.com/articles/dba/database-mail/
• http://www.sqlservercentral.com/blogs/databaseexpertisecom/2010/05/12/database-mail-set-up-in-sql-server-2008/