sp_send_dbmail usage best practice

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.