I would like to get some clarity on the usage best practices of sp_send_dbmail and I cannot find any answers online.

Our developers love to use sp_send_dbmail to send out application related emails from within the application. Say for instance a user forgets their password, they will send the password reset email that a user requests by executing sp_send_dbmail. This is just one of the many types of emails they send using this stored procedure. Is this a good practice? To me it feels like a dirty solution as there are many API's out there that can handle email sending in your application and as a DBA, I don't like enabling these options on the production servers. I just don't have a choice. Maybe with enough motivation and proof that it's bad practice and/or potentially dangerous for various reasons I can sway them to change it.

  • I second the answer that says use dbmail for admin purposes only. The biggest concern in my book is deliverability and reputation of your IP / Domain in regards to spam and blacklisting. Look for services specifically designed for this and very inexpensive ie SendGrid. – Joe Jan 24 '17 at 13:09
  • using db mail will bloat your msdb. I would suggest to use table as queue and have some windows service query the queue and send out emails. – Kin Jan 24 '17 at 17:32
up vote 12 down vote accepted

SQL Server core licenses are expensive: using a SQL Server machine's CPU to send thousands of emails has no justification, especially when there are countless better options in terms of features, manageability, setup, troubleshooting.

Another point is that tight coupling between the email and database features makes the application difficult to scale, isolate and manage. One server - one service.

Database mail, in my opinion, should be used for administrative purposes only (alerting) and not for sendind business email messages.

  • ,Another point is that tight coupling between the email and database features makes the application difficult to scale. One server - one service. for +1 – Md Haidar Ali Khan Jan 24 '17 at 10:30

I don't see the problem. SQL Server has always wanted to wrap business logic into the database whether it's procedures, pure CLR code, reports, now analysis, and even good old service broker.

Having mail in there isn't so bad either. It provides excellent queuing and delivery history directly in the database in a querable format.

Unless you're aware of a specific current vulnerability there's no reason to attempt taking it away. Surely there are better things to do with your time and without making enemies, you're practically begging for them to replace it with an awful solution and then to pin the blame on you. I wouldn't.

IMHO this isn't a technical problem. It's a career problem and learning when the hold back.

  • This is exactly the conclusion which I am trying to come to. Is it worth arguing over with the devs? There is nothing wrong with the current solution functionality wise and even in this thread I am finding different views regarding this aproach. I like answer from @spaghettidba as it has been in line with my thoughts in terms of unnecessary usage of resources for a very expensive system, although, I understand where you are coming from. Is it worth it to spend all the time to try and re-do something that is working even though it feels dirty? I guess not. – Joachim Prinsloo Jan 24 '17 at 13:34
  • In the end when designing a new system I would also advise keeping it separate but for now I guess were stuck with doing it the sp_send_dbmail route. Why fix which is not physically broken right? Thanks for the input. – Joachim Prinsloo Jan 24 '17 at 13:35

Trying to complement the existing answers with new aspects:

If sp_send_dbmail is used you can never take away internet access for that database server.

Also, it's a security problem because network protocols like SMTP are common sources of vulnerabilities. The application end users will now be able to make your SQL Server connect over SMTP to a host under their control (since they presumably input email addresses). What if that host replies with 1TB of garbage data? Can SQL Server handle that? Who knows. sp_send_dbmail is an old component. Maybe it's old, crappy C code trying to parse network data.

I also would argue that it's in the best interest of the developers to use another language to send mail. T-SQL is not a productive language for procedural tasks.

They might like the following alternative: Make them insert their mails into a queue table in the database. Then, make the application poll that table regularly and send mails from the application code. That way they can even send mails in a transactionally consistent way! That is extremely convenient. This also is easy to scale. You might use Service Broker to reduce the need for polling.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.