Configuring Database Mail

To complete this step correctly, a properly configured mail server is needed. In most cases it is okay to use localhost, but that requires the installation of Microsoft IIS/SMTP. If your administrator will not allow the install of IIS on a server running SQL Server, get the name of a mail server that can be used.

Step. 1

Open SQL Server Management Studio (SSMS), connect to the Database Engine, expand Management, right-click on Database Mail and then click Configure Database Mail.

You will then see the splash screen below. Click Next.

Step. 3

Leave the default option of “Set up Database Mail by performing the following tasks” selected. Click Next.

Step. 4

You will get a pop-up below if mail hasn’t been configured yet. Click Yes and then click Next.

Step. 5

Give the New Profile a name and then click Add.

Step.6

Fill in the correct information as show below.

· Email address– This should be the sender email address.

· Display name– This should be the name displayed when the email arrives.

· Reply email– This should be set properly too.

· Server name– The mail server address. In this case, I am using localhost.

· SMTP Authentication– I leave this section set to the default, but you can specify it to your needs if it is required.

Note that the OK button will remain grayed out until all required fields are completed. Once all the fields are completed, click OK.

Step. 7

Click Next.

Step. 8

Check the option to make the new profile Public. If the profile isn’t set to Public, permissions will need to be assigned properly. I am not going to discuss this here, but it will be important to note the difference.

Step. 9

Review the System Parameters. I always use the default settings, but they can be adjusted as needed. You can review the settings in depth here. Click Next.

Step. 10

Click Finish.

Step. 11

You should see that each Action was completed and has a Status of Success. Click Close.

Now let’s test.

Right-click Database Mail and select Send Test E-Mail… type in an email address to use for testing and click Send Test E-Mail.

You will then see the screen below. Once you verify the email was sent and received you can move on and Click OK on the box. 

Right Click on SQL Server Agent at the bottom and click Start.

Adjust the properties within the SQL Server Agent

Right-click SQL Server Agent and select Properties

Click on Alert System under Select a page.

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

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

Click OK.

***MAKE SURE YOU CAN RESTART THE SQL SERVER AGENT WITHOUT INTERRUPTING CLIENT OPERATIONS!!***

Restart the SQL Server Agent service.

Create an Operator

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

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

Create or Modify an SQL Server Job

Now we need to set up a SQL Job and then set SQL so it sends emails if the job fails. The easiest way to test this is to set up a Backup job and set it to notify on “Completion”. We want the system to email reports on success or failure. 

Right Click on Maintenance Plans and Click "Maintenance Plan Wizard". 

Click Next

Select your maintenance tasks and click Next. 

Click Next. 

Select your database and set any other parameters needed and click Next. 

Select Email Report and select the profile you created. Click Next. 

Click Finish. 

Find the newly created maintenance plan under SQL Server Agent - Then Expand Jobs and highlight the job as shown below. Right click and select Properties. 

Click on Notifications then select Email. Click the drop down and select the profile you created. Finally select "When the Job Completes". This is very important to make sure you get notified on success and failure emails. 

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: