Database Mail

Recently I’ve started using SQL 2016 with basic availability groups for a new Citrix deployment. Whilst going through the configuration of my maintenance plans I thought it would be sensible to setup email notifications. I went through the following process of setting up database mail however I stumbled across an issue where the emails weren’t being sent.

To configure Database Mail open up the SQL management studio and right click Database Mail and select Configure Database Mail:

We will follow through the wizard to complete the basic configuration:

Select set up Database Mail by performing the following tasks:

If the Database Mail feature is not available you will be asked to enable the feature, select Yes:

Create a new profile by populating the fields with meaningful information, then add an SMTP account:

Enter the relevant email settings for your server, this could be a local SMTP which allows relay, an Exchange server or even a Gmail account:

Select next:

Select the public checkbox next to the profile you have created:

Select next:

Select finish:

If everything goes to plan you will have no errors on this next bit, you can then select close:

Right click Database Mail and select send test e-mail:

Enter the email address you would like to send the test email to and select send test e-mail:

Wait a short while to see if you receive the message, I waited 60 seconds before I thought something wasn’t right:

After clicking OK nothing happened, my first line of troubleshooting was connecting to the Exchange server with Telnet. I was able to confirm I could send an email by entering in the commands manually. There were no logs under Database Mail on the SQL server, so I proceeded to check the Database Mail queues.

Create a new query and enter the following for SQL 2016:

SELECT * FROM msdb..sysmail_event_log order by log_id DESC
SELECT * FROM msdb.dbo.sysmail_mailitems
SELECT * FROM msdb.dbo.sysmail_sentitems
USE msdb
SELECT sent_status, *
FROM sysmail_allitems
SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb';
EXECUTE msdb.dbo.sysmail_help_status_sp

You can see that the sent_status of the mail is unsent.

Open Windows Explorer and navigate to the following location:

When double clicking on DatabaseMail.exe I received the following message stating the following feature couldn’t be installed. Clear there is a requirement for .NET Framework 3.5 (includes .NET 2.0 and 3.0):

Open PowerShell, make sure you run it as administrator otherwise you will receive the following error:

With Windows 2016 you will need to specify an additional source such as a DVD or ISO, enter the following command:

DISM /Online /Enable-Feature /FeatureName:NetFx3 /All /LimitAccess /Source:D:\sources\sxs

You will need to make sure you mount the ISO or insert the Windows 2016 DVD and select the correct path:

Installation completed:

If you attempt to open DatabaseMail.exe now it will send the items in the queue.

I hope this helps and saves some head scratching!