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