Setup Database Mail in SQLServer 2008 R2

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

dbmail1

3) Check on Setup Database mail by performing the following  tasks and click next

dbmail2

4)Enter the profile name and click on add button in the SMTP accounts:

dbmail3

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.

dbmail4

5)Once the SMTP account is setup click on the Next button.

dbmail5

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.

dbmail6

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.

dbmail7

8)Now click on finish button and then close button as soon as the database mail is configured as shown in the below screenshots.

dbmail8

dbmail9

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.

dbmail10

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.

dbmail11

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.

dbmail12

Hope this helps.

Advertisements
This entry was posted in SQL Server DBA Stuff and tagged , , , , , , , , , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s