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.
25 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?
Haha went through this pain.
Later used combination of Ssrs and Ssis to get around.
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.
Can take it another step. Create a subscription and run add event to run the subscription and email out the report to the user.
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!
Well
I dont use much fo business, but i have an alert in redgate monitor checking failed email view/table
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())
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.)
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.
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.
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.
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!
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.
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.
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.
“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).
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.
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.
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.
Most are fair points, but I’m not sure if I’ve missed something? Not ideal but in an “enterprise” i.e. with some kind of datacentre failover, surely each server has identical mail profiles that you’ve set up? Like I say not ideal, and it only gets around a failover, it does nothing to address the other points (poor troubleshooting etc)
Why would you say surely?
Check out https://twitter.com/LBacaj/status/1478241312570707970 point 2 (from Steve Jones originally) – I’m trying not to be a jerk.
Also I am actively looking for an alternative to dbmail, we’ve had some issues recently so maybe I failed on that point. My bad!
Sorry, I’m still not understanding.
Why would you believe this:
> in an “enterprise” i.e. with some kind of datacentre failover, surely each server has identical mail profiles that you’ve set up?
Have you actually worked in enterprises where that was the case for all SQL Servers? And why would you assume that all enterprises would be that way? And why would you assume that only enterprises run SQL Server?
“I’m not sure if I’ve missed something?” – it seems I have.
In answer to your questions;
Have you actually worked in enterprises where that was the case for all SQL Servers? – No. I have (and do) work in an organisation that has SQL servers. These are generally for a specific purpose and some are in Always On groups so if server A a site X went down server B at site Y would take over. I’m referring to this specific use case where the dbmail on server A and server B would have the same mail profiles so any reference to the send mail stored procedure would still work on either server.
And why would you assume that all enterprises would be that way? I don’t, please see previous answer.
And why would you assume that only enterprises run SQL Server? Again I don’t, in fact I know it *not* to be true.
PS thanks for the suggestions of other mail solutions, looking for a replacement to dbmail is how I came across the post.
Good, as long as you understand that SQL Servers will not always be set up with automatic redundant failover, then you’ve just answered your own question. Cheers!