One of the nice things about SQL Server 2016 is that .NET 3.5 is no longer required for installation. The .NET requirement wouldn’t have been problematic, but in order to add the .NET feature access to the Windows media was required (this was particularly painful in Azure VMs). Fortunately the product team eliminated the requirement as part of the release. Unfortunately, a few things broke–namely database mail, native log shipping, and distributed replay. You can identify this problem, by a couple of symptoms:
- Your database mail log has no entries
- You have messages with a status of “unsent’ in sysmail_allitems in MSDB
- If you try to execute the file C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\DatabaseMail.exe you get a .NET 3.5 error
Cumulative Update 1 for SQL 2016 has a fix for this, but only for log shipping. The fix is simply a config file (or you can install .NET 3.5, but don’t do that). If you have a default installation, just run the below PowerShell code to copy the LogShipping file to a new file in the database mail directory.
copy-item C:\Program Files\Microsoft SQL Server\130\Tools\Binn\SQLLogship.exe.config C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\DatabaseMail.exe.config
That’s it–that file is just a config file that tells the executable to run on .NET 4.0. I’m sure this will be fixed in CU2, but this will get your going.