Setting up SQL Server Database Mail

Today I am going to write about how to setup SQL Server Database Mail.  This is very handy as the server will be able to email you query results, whether a maintenance job succeeded or failed, status results, etc.Before starting, you will need to know your email SMTP server name or IP address, and you will need access to send emails through it.  Your Mail Admin will be able to help you.

 

First open SSMS and scroll down to Management, and right click on Database Mail, and select Configure Database Mail.

 

m1

 

This will bring up the Mail Configuration Wizard, click next.

m2

 

Since we are setting up a new server, we select the first option to setup database mail.

m3

 

Click Yes if you get the prompt below.

 

m4

 

Here we need to create a Mail Profile.  I am calling mine SQL Admin, then click Add.

 

m5

 

Now we need to fill in the important information like the email address we will be sending from, and the email server name.  I am lucky and am able to send emails using anonymous authentication.  Talk to your Mail Administrator to see what your options are.

m6

 

After clicking OK, we are back to this screen, click next.

m7

 

Now we need to decide if the profile should be Public or Private, I am choosing Public, and I am making it the default profile.  Click Next.

m8

 

On the next screen we are able to make changes like limiting the attachment file types, file size, and retry attempts.  Click Next.

m9

 

This takes us to the summary screen, click Finish to complete the setup.

m10

 

This will run through the setup and you should get all check marks.

m11

 

Lets send a test email to make sure we got everything setup correctly.  Right click Database Mail in SSMS, and select send Test E-Mail.

 

m12

 

Put in your email address to send the test email to.

m13

 

After pressing the Send Test Email button, it will give the box below.  If you receive the email, select OK if you did not get an email, click Troubleshoot.   I have found most issues are usually caused by your server not having access to send emails through your system.  You need to talk to your email Admin.

 

m14

 

Checking my email, I see the test message was sent successfully.

 

m15

Once the email setup is done successfully, now comes the important part that a lot of people forget to do.  They setup their database jobs to email if there are problems, but then do not get emails.  We now need to configure the SQL Server Agent to use our email profile.

Right click on your SQL Server Agent in SSMS and select Properties.

m16

 

Select the Alert System on the left, and Enable mail profile on the right.  Also check off the include body of email option at the bottom of the screen.  If you are not getting emails and you get test emails, this may be the reason you are not getting alerts.  This Alert system tab needs to be setup.

m19

 

Now we need to setup an Operator for the alert system to send emails to.   Right click Operators and click New Operator.

m17

 

Provide an email address and select Enabled.  If you have a team of DBA’s, it would be a good idea to create an email group and send emails to the group instead of a single person.

m18

 

Now, we can go into one of our Database jobs and go to the Notifications tab, and we can select Email and can set if we want to get an email when the job fails, succeeds,  or completes.

m20

 

By going through these steps, you can now receive email from SQL Server.

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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s