Database Mail Configuration in SQL Server 2008

Part 1 of this article explains step by step, how to configure Database Mail in SQL Server 2008.

Remember SQL Mail?

SQL Mail was introduced in SQL Server 2000. Those who worked on SQL Mail know how much they hate it. They have to do Outlook configurations, third-party Simple Mail Transfer Protocol mapping, Messaging Application Programming Interface profiles (this mapping must also be installed on a production server), etc... etc..., lots of stuff must be ready for sending and receiving mails. I also got a chance to work with SQL Mail, it looks simple, but a lot of this kind of configuration must be there to get it working. One of the main disadvantages was when SQL Mail goes down; SQL Server also goes down because SQL Mail runs in-process to the SQL Server service. Also, SQL Mail is less secure. Even Microsoft has fully explained this in the article on Common SQL Mail problems.

Getting Started with Database Mail

Database Mail was introduced in SQL Server 2005 and we can say it is a complete replacement of SQL Mail of SQL Server earlier version. Database Mail is designed for reliability, scalability, security, and supportability.

The following is a list of some of the advantages of using Database Mail.

• It's easy to configure, fast, and reliable.
• Highly Secure.
• Auditing and Maintaining Logs of every mail.
• Can be configured to multiple SMTP servers.
• Can send HTML messages, and attachments (can filter out attachments depending on extension).
• No worry of SQL Server going down when Database Mail goes down because Database Mail runs outside SQL Server in a separate process.
• Database Mail does not need any configuration like SQL Mail must have.
• Fully supported on 64-bit installations of SQL Server.

By default, Database Mail is not active. By using the Database Mail Configuration Wizard or the sp_configure stored procedure or by using the Surface Area Configuration facet of Policy-Based Management we can activate or configure it. Database Mail Configuration, security information, creating profiles and accounts, messaging components, executables, Logging, and auditing information will be saved in the MSDN database.

How to configure Database Mail?

1. Open and login into your server with SQL Server Management Studio. Now press F8 or click on Object Explorer. Under the Management node list, you will see the Database Mail node.
   
2. Now right-click on Configure Database Mail.
   
3. A welcome wizard will pop up for configuration. You can read the information provided and click on Next. On the second window choose the first option from the radio button list for the configuration task.
   
4. Next comes the New Profiles creation window, here we can configure multiple SMTP configurations. If any of the accounts fails while sending emails, the profile uses the next account in the priority list. Add the profile name, and description (optional) and click Add.
   
5. A New Database Mail Account window will be opened. Am using here as the server name as smtp.gmail.com and port number 587. You can configure other SMTP accounts too. Select Basic authentication and provide your Gmail username and password. Click OK and the profile account gets created. We can even remove the profiles from the list available. Click on Next.
   
6. Manage Profile Security window where we can make a profile Public or Private. A Public profile can be accessed by all users of any mail-host database. A Private profile can only be accessed by a specific of any mail-host database from the User name dropdown list provided. We can even make default profiles. This part is optional and we are moving to the next window.
7. Configure System parameters, this is also optional.
8. Click Next and Finish Completing the Configuration Wizard.
   
9. And the configuration is successful and now ready to test it.
   
10. Right-click on Database Mail then Send Test E-Mail from Object Explorer.
    
11. The Send Test E-Mail window will be opened. Enter the email in the "To" box, subject, and hit Send.
    
    
12. We can even check the Database Mail Logs from Object Explorer.
    
13. Now open your mail inbox and check the mail received.
    

That's it; the Database Mail has been successfully configured.

Conclusion

This Part 1 article explained step by step, how to configure Database Mail in SQL Server 2008. The next part will cover how to programmatically send emails, attach files, and send HTML format messages. Hope you all enjoyed the article. Post your comments and rate the article. For any queries our forum is available.

Thank you, have a nice day!