Don’t Use SQL Server to Send Emails.

I would like to unsubscribe from this idea.

I’m not talking about Agent alerts when jobs fail. That’s fine when you can’t get the business to spring for a monitoring system.

I’m talking about using SQL Server to send emails to confirm a customer’s order, alert that a product is low on stock, or other business processes.

SQL Server’s email delivery isn’t very robust. Whoever started the documentation by writing “Database mail is an enterprise solution” had clearly never seen an enterprise solution in real life. Sure, it seemed robust when it came out a couple decades ago, but these days, anything involving the word “enterprise” needs to survive a data center failover. To protect your user databases, you’re likely using log shipping or Always On Availability Groups, and in the future, it’s only getting more complex with SQL Server 2022’s ability to fail over to Azure SQL DB Managed Instances. Database Mail lives in the MSDB database and Service Broker. Your messages ain’t coming along for the ride.

When email delivery stops, you won’t notice. People think, “Hey, things must be going great these days. It’s been forever since I’ve gotten a failure email.” In reality, there are hundreds or thousands of emails piled up in some SQL Server somewhere with a broken email delivery subsystem, like an old SMTP server address. You won’t figure it out until it’s too late.

SQL Server’s email troubleshooting is almost nonexistent. When things do go wrong, the logging isn’t detailed, and it’s not the system that your sysadmins and email admins are used to working with. They’re going to be shrugging and pointing the finger at you, going, “Well, the rest of our systems are doing email just fine.”

These things aren’t as much of a big deal for DBA-focused emails like a job failed. However, if your business users are relying on the email getting there, hand the task over to your developers, and have them use a truly modern enterprise solution like SendGrid or Simple Email Service.

Previous Post
[Video] Office Hours: SQL Server 2008 R2 Edition, I Guess
Next Post
[Video] Office Hours: First Post From Cabo

19 Comments. Leave new

  • Regarding logging, I found the lack of logging of SSRS’s data driven subscriptions maddening.
    So we’ve attempted to generate 1000 report instances and email them out. 10 have generated reports but unable to deliver to the email subsystem. Which 10? Who were the intended recipients? What parameters were to have been used? Am I expected to reconcile the Sent folder with what the subscription query produced?

    How are data driven subscriptions an enterprise feature?

    Reply
  • How about when the company wants SSRS subscriptions but will not spring for Enterprise, so you gotta schedule an Agent job that runs a sproc that queries for data and if it exists, emails a link to an SSRS URL so the user can generate their own report? Fun times.

    Reply
    • Can take it another step. Create a subscription and run add event to run the subscription and email out the report to the user.

      Reply
  • Andrew Peterson
    December 14, 2021 5:23 pm

    Seems all companies like to call all their components “enterprise class”. Guess there was sale at the local store on “Enterprise Class” stickers. SQL Server email, like some other not fully baked components ( master data are you listening??) can and should only be used if you know the down side. Besides, db mail works most of the time!

    Reply
  • Michael Karpenko
    December 14, 2021 5:33 pm

    Well
    I dont use much fo business, but i have an alert in redgate monitor checking failed email view/table

    Reply
    • Michael Karpenko
      December 14, 2021 5:43 pm

      to be more specific, here it is

      — failed e-mails
      select Count(1) Error
      from msdb.dbo.sysmail_allitems sa
      join msdb.dbo.sysmail_event_log sel on sa.mailitem_id=sel.mailitem_id
      where 1=1
      and log_date > DATEADD(hh, -1, GETDATE())

      Reply
  • I use SQL Server’s built in email process for a few things. One of those is a status check, which provides various reports to users in individual emails IF there is anything to report. But, if none of those reports are generated, it automatically sends a single email that says “Everything is okay”.

    The user knows to expect at least one email so, if mail fails, they know to double check.

    One of the reports that this performs is a check to ensure that the Daily Backup is enabled because SQL does NOT send a “Backup Failed” email if the backup never ran at all. (The scheduled backup was once disabled because of an update, which required a manual backup and we didn’t want the automatic backup to overwrite the manual one half way through the upgrade. Someone forgot to re-enable the scheduled backup. For three days.)

    Reply
  • Interesting information about fails over. Also, my stored procedures monitor whether the call to DBMail succeeded – i.e., DBMail took the message to await delivery. Ok, so let’s unravel this a bit . Once successfully sent from SQL SERVER or SSRS, the mail sits in a queue. So far, so good. The following action by DBMail is to ‘preprocess’ each email. By ‘preprocessing,’ I mean reaching out to the associated ‘to’ email server and asking if the structure and content sizes are acceptable to the receiving in mail server.

    If this check fails, think ’78, 998 Rule’ or embedded CHR(10), CHR(13) characters, then SQL Server DBMail will write these errors in MSDB.dbo.Sysmail_failure with a link to MSDB.dbo.sysmail_event_log on mailtiem_id. I monitor this view daily for errors. I find that DBMail runs fine, BUT it can not solve email issues associated with rejection at the receiving end.

    Reply
  • I did a tons of event messaging, sometimes for business proccesses, sometimes for application/infrastructure monitoring.
    As a developer, you can never rely on the messaging infrastrucure itself to be up and running. Therefore you have to design monitoring that will indicate a problem when no messages are received. In other words, positive alerting rather than negative alerting. It’s the only way to know when the messaging infrastructure fails you.

    Reply
  • Ooooo, maaaaannnn…. don’t get me started, Brent. We have “Jira” and we have a “Jira task” database that some 3rd party set of experts installed and I can’t touch. I don’t know if they’re actually sending emails from the server (they wouldn’t do anything about it anyway so almost don’t care) but they are saving the damned “mime message chunks” in a single table that has grown to over 100GB in just several months. I just wanna scream but I’m too busy puking about this.

    Reply
  • E-Mailing is trivial from any Windows Service (e.g., C# Service). I built a trivial set of SQL procedures that post e-mail information to tables, and a C# Service that monitors those tables and does all the actual e-mailing (with CC, attachments, etc.).
    Trying to make SQL Servers “native” mailing services work is ridiculously complex and restrictive. Just say NO to SQL Server e-mailing!

    Reply
  • In early versions of SQL Server (sadly can’t tell you which) emails got sent by the database server process itself, via an MS Outlook session. This would regularly get jammed up and required a restart of the DB server itself.
    Yuk! – was very frustrating to have to do a full DR failover of the DB just to get emails flowing again

    As a workaround/improvement used a 3rd-party freely available DLL that could called from within an SP.
    A parameter compatible (with the built in email sending SP) SP would log emails into a “queue” table (which could be replicated to failover/DR servers) and other SPs triggered by a repeat-scheduled SQLAgent job could read from the queue, send the message by calling the DLL, dequeue when appropriate, log any errors etc.

    This was later replaced with a Windows Service that functionally did the same as the Agent job
    – someone else above has done something similar.

    Uless there’s a really good reason not to use one then an off-the-shelf service like the ones Brent mentioned are likely to be more reliable and may provide a bunch of reporting analytics debugging options etc. that would take quite some time to build in-house.

    Reply
  • I agree and found out the hard way when users complained and I had to research it. Now I use SSRS and schedule out the report. I shudder at doing this and am trying to teach my users about Power BI but that is slow going at the moment; the ones that are more tech savvy love Power BI style reports.

    Reply
  • Next Brent will say that SQL Server should not be used to generate HTML. Being 7K per core and such.

    I used to agree but then I found a blog post on how to send HTML mails from SQL Server. And that’s when reason left the building.

    Reply
  • Alexander Speshilov
    December 16, 2021 12:55 pm

    “Do not send emails”, “Do not store blobs”, “Do not manipulate (concatenate, transform) long strings”, “Do not xp_cmdshell either do not manipulate files”, “Do not use SS[^M]S”, “Do not use change tracking”, “Do not hold open transactions”, “Do not shrink”, “Don’t set small autogrow (Avoid autogrow at all)”, “Do not make performance decision based only on duration”, “Don’t use threshold 5 for parallel plans”, “Do not use full backup model if you do not use tran log backups” — hm… someone can write huge cool useful book “SQL Server DON’Ts vol.1” (I think it will be 4 or 5 volumes).

    Reply
  • The arguments for not sending email with SQL Server are true with a lot of applications, including monitoring applications. In my opinion, these are not good arguments. But, that is not saying that you should or should not. There are good use cases for sending email from SQL Server and there are bad use cases. It comes down to the right tool for the job.

    As others have pointed out, the effects on msdb is good justification to, at a minimum, use prudently.

    Remember the days of SQL Mail? The reliability, or I should say lack of, was a good justification for not using it. When it crashed, SQL would go down with it.

    Reply
  • Darko Martinovic
    December 17, 2021 6:46 pm

    SQL Server E-mail is not suitable for an enterprise system. That is a good point and I agree with that. The best practice is to implement e-mailing system outside of the SQL Server engine. But, there is a custom solution you can try on the following link.

    https://www.red-gate.com/simple-talk/databases/sql-server/t-sql-programming-sql-server/sqlclr-practice-creating-better-way-sending-email-sql-server/

    The solution helped me and other people in some cases.

    Reply
  • Database should never perform business functions. I just hate it when a sql server machine is loaded with other apps. A good system architect will never do that.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu