SQL 2016 configuring database mail on Windows 2016
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!
