How to Configure Database Mail in SQL Server

  1. Monitoring — In my days as a Database Administrator, in the absence of a full-blown monitoring tool, I adopted a script developed by an acquaintance who blogged on SQL Server Central. That script was HTML-based, but it incorporated SQL that extracted data from key system catalog views and sent the output by email to administrators.
  • Job Results — I also used Database Mail to deliver the output of jobs to Database Administrators or even regular users who wanted to get such information. An SQL Agent lets you leverage Database Mail to simply send jobs’ status — success or failure. You can go further by developing scripts using the stored procedure sp_send_dbmail
  • Reporting — In the case of simple reports, the sp_send_dbmail stored procedure can also be used to aggregate a result set and send it as a file or in an email with proper formatting.

REQUIREMENTS

  1. Port 25 (SMTP) must be open between the SQL Server host and the Exchange Server. It is relevant in environments where the network is segmented by Firewalls.
  2. SQL Server host must have mail relay permissions on the domain. A Microsoft Exchange administrator should be able to ensure it for you.
  3. Create an exchange server account to send emails. It is not mandatory, but I personally prefer to have control over which account performs which actions in my environment.

PROCEDURE

  1. The welcome screen gives you an overview of the actions you are about to take. In this process, you will set up a mail profile, a mail account, security, and then configure the system parameters. (See Figure 1)
Figure 1: Welcome Screen
Figure 2: Select Configuration Task
Figure 3: Enable Database Mail Feature
Figure 4: Mail Profile
Figure 5: Mail Account
Figure 6: Mapping Profile to Account
Figure 7: Profile Security
Figure 8: System Parameters
Figure 9: Configuration Summary
Figure 10: Completion

CONCLUSION

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
{coding}Sight

{coding}Sight

Awesome blog focused on databases and Microsoft, .NET and cloud technologies. http://codingsight.com/