SQL: Setting up Database Mail for SQL Job failure

Problem
Many things have changed with SQL Server 2005 and one of these changes is the replacement of SQL Mail with Database Mail.  This is a good thing, because SQL Mail relied on having a MAPI mail client installed such as Outlook in order for it to work.  With SQL Server 2005, this has changed and now the mail services use an SMTP server to send out emails which makes it a whole lot easier to setup and maintain.  So how do you setup Database Mail?

Solution
There are two ways that you can setup Database Mail, either by using the stored procedures that are included with SQL Server 2005 or by using SQL Server Management Studio.  For this exercise we will walk through how to setup Database Mail by using the GUI.

To setup Database Mail, connect to your server and expand the Management node of the tree and then right click on “Database Mail”.

clip_image002_thumb1

Then select “Configure Database Mail’ and you will get the following welcome screen and the click “Next”.

The following screen will appear and select “Set up Database Mail by performing…” and click “Next”.

If Database Mail has not been enabled, you will get this following screen. Just click “Yes” to enable it.  If it has already been enabled this screen will not appear.

Enter in a name for the Profile and also a description and click “Add…”

The following screen will appear.  Fill out the details for your mail account that will be used to send out email from SQL Server.  When you are done click “OK”.

After you click “OK” you will be brought back to this screen and the SMTP details will now show for the account you just setup.  Click “Next” to continue.

On the next screen you will see the name of the profile that you just setup.  Click on the checkbox to allow this to be a Public profile and also select “Yes” for the default profile and then click “Next”.

The following screen has some additional parameters that can be set to control how mail is sent.  You can make changes or leave the defaults.  When you are done click “Next”.

A summary screen will appear that shows you all of the options that were selected.  If everything is correct click “Finish” or click “Back” to go back and make changes.

When you click “‘Finish” the next screen will appear that shows you the status of installing Database Mail. When this has finished just click “Close” to close this screen.

To test Database Mail, right click on Database Mail and select “Send Test E-Mail”.

Fill in a “To:” email address and change the body of the email if you want and then click “Send Test E-Mail”.

After you have sent the email you will get this message box to confirm if the email was received or not.  If it was you can click “OK” to close the screen or click “Troubleshoot” which will launch the help information to see what the issue may be and how it can be resolved.

That’s all there is to it.  As I mentioned before this can also be setup by using stored procedures. To look at this approach take a look at this article Database Mail in SQL Server 2005.

Create SQL Server Job

At this point we know the SQL Server can send email. Now we need to setup a SQL Job and then set SQL so it sends emails if the job fails. The easiest way to test this is to setup a Backup job and set it to notify on “Success”. Once it is working properly, change the job to notify on “Failure”. To setup a simple Backup job, see instructions here. Note: The Maintenance Plan Wizard can also be used to setup a backup job very quickly.

Adjust the properties within the SQL Server Agent

Right-click SQL Server Agent and select Properties.

SQL Server Agent Properties

Click on Alert System under Select a page.

· Under Mail session, select the option to Enable mail profile. Ensure the correct Mail profileis selected.

· Under Token replacement, enable Replace tokens for all job responses to alerts.

Click OK. Restart the SQL Server Agent service.

clip_image028_thumb1 clip_image002_thumb1

Create an Operator

Under the SQL Server Agent, right-click Operators and select New Operator…

 

SQL Server Agent Operator

Type in the recipient email address in the E-mail name and click OK.

Test Email Name

Adjust the SQL Server job to send the email on Success (for testing purposes only).

Expand SQL Server Agent and click on Jobs. Find the job you created above, right click it and select Properties.

Under Select a page, select Notifications. Enable the first option, E-mail. Then select the Operatorwe just created and change the job to email When the job succeeds (for testing purposes only). Click OK.

Operator Email Success

That’s it!! Now we test. Run the job we create that will backup databases. You should receive an email similar to the one below once it completes:

Test Email Example

If an email isn’t received, restart the SQL Server Agent once more and then test again.

Curious about SherWeb’s DBaaS solution? Watch our webinar now!

Next Steps

  • Setting up Database Mail is not that complicated and it is much easier then SQL Mail. Take the time to see how this new approach to mail can work in your environment
  • After you setup Database Mail don’t forget to setup your operators, alerts and SQL Agent alert settings
Advertisements

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