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.

 

 

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

 

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

 

Click Yes if you get the prompt below.

 

 

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

 

 

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.

 

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

 

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.

 

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

 

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

 

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

 

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.

 

 

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

 

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.

 

 

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

 

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.

 

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.

 

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

 

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.

 

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.

 

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