In this post I would like discuss how to setup database mail in SQL Server 2008 R2. I have also posted information required to enable the database mail in the SQL Server Agent as well as how to setup the operator along with screenshots. When the database mail is enabled in the SQL Server Agent any SQL Server agent jobs should be able to notify dbas or developers upon successful completion or failure. Below are the steps involved and screenshots:
1)Ensure that the SQL Server Agent services are up and running
2)Connect to SSMS–>Management–>Right click on Database Mail–>Click on Configure database mail. Now Click next on welcome screen shown below
3) Check on Setup Database mail by performing the following tasks and click next
4)Enter the profile name and click on add button in the SMTP accounts:
Enter the Account Name, Email Address, Display Name and the exchange server name along with the port number as shown in the screenshot below and click on OK button.
5)Once the SMTP account is setup click on the Next button.
6)In the profile security page make the profile public by checking on the public column next to profile name and select “Yes” for the default profile as shown in the below screenshot and click on Next button.
7)If you want to change any parameters like increase the file size in bytes or restrict any additional attachment file extensions or any other available options then it can be done in the next screenshot which is shown below and click on Next button once the changes are made.
8)Now click on finish button and then close button as soon as the database mail is configured as shown in the below screenshots.
Now restart the sql server services. The database mail is now setup.
9)To enable the database mail in the SQL Server Agent. Right click on SQL Server Agent and select properties as shown in the below screenshot.
10)In the SQL Server Agent properties select the Alert system and Check on Enable Mail profile in the mail sessions and select the mail system and mail profile in the drop downs as shown below and click on OK.
11)Now right click on Operators in the SQL Server Agent section and select New operator. Enter the Name of the operator and check on enabled and enter the email name and any other details you want to enter as shown below and click on OK button.
Hope this helps.