SQL Server 2016 Database Mail Not Working

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

Screen Shot 2016-07-27 at 2.41.59 PM

  • 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

This should be fixed in CU2, but there are reports below of it failing in SP1. The XML to put in the .config files is:

<?xml version=1.0 encoding=utf-8 ?>

<configuration>

  <startup useLegacyV2RuntimeActivationPolicy=true>

    <supportedRuntime version=v4.0/>

    <supportedRuntime version=v2.0.50727/>

  startup>

configuration>

SQL Server 2016 Automated Installs and a SQL 2016 Webinar

I hate installing things through a GUI–I’ve talked about this before at PASS Summit, and when I worked at big cable, we build a private cloud and fully automated the SQL Server installation process. When SQL Server 2014 launched, my process had virtually no changes, however a number of things broke it in SQL Server 2016 (mostly for the better)

factory_automation_robotics_palettizing_bread

  • TempDB installs with the right number of files–they are still too small though
  • The behavior of trace flags 1117 and 1118 are built in to TempDB so you don’t need to turn them on
  • SSMS is a now a separate installation and download
  • I used batch and I wanted to rewrite into PowerShell

So since this is 2016, I decided to share it on Github. You can find it here: https://github.com/DC-AC/SQL2016_Scripted_Install. Feel free to fork and make edits in the code, or tell me how crappy my PowerShell logic is. Read the readme–there’s a few assumptions about your configuration. I’d like to parameterize those over time.

One other thing I wanted to mention is that Denny Cherry and I are doing a webinar this Friday July 29th at 11 PDT/2 EDT talking about managing SQL Server 2016 in production. We’ve have a customer live for a year now, and will share what we’ve learned and answer your questions. You can sign up here.

 

 

%d bloggers like this: