Why sending e-mail with sp_send_dbmail is a bad idea

JJ Prinsloo SQL Expert sending e-mail with sp_send_dbmail

Sending e-mail with sp_send_dbmail. Should you or should you not. Well as with everything, it will depend on your requirements on your circumstances. Recently, I have been wondering the exact same thing. In my case it was more in the line of application specific emails.

sp_send_dbmail usage scenario:

Our developers are sending e-mail with sp_send_dbmail for various in-application tasks and events. You know, password reset emails, event notification emails, that kind of thing. To me, as a DBA, this always felt like a dirty solution. I took it upon myself to investigate as to whether or not sending e-mail with sp_send_dbmail is a good idea. To my surprise, I could not find any discussions or debates regarding the best practices for sending e-mail with sp_send_dbmail. I spoke to a couple of fellow SQL experts and I finally figured it out. Everyone has there own opinion. This does not really help. Eventually I turned to the good folks of stack exchange. Here, I got some interesting responses as well a some key point which is simply not debatable, and not in the favor of sp_send_dbmail either.

Problems with sending e-mail with sp_send_dbmail

  • Sending SMTP email with sp_send_dbmail will cause your msdb to grow. This may or may not be a problem depending on how many e-mails you are planning to send out. Plan for your volumes in the future. If you only plan for your current volume requirement you will soon find yourself back at the drawing board.
  • You run the risk of damaging the deliver ability and reputation of your IP/domain in regards to spam or blacklisting.
  • SQL server core licenses are expensive. When half of your expensive server cores are used to send email, this can be a potential waste of expensive resources.
  • If you couple the application features too closely with the database, it makes scaling difficult and becomes a nightmare if anything needs to be moved. Different services should run on different servers.
  • The server will require internet access. ALWAYS.
  • SMTP is a common security vulnerability often targeted by hackers and will have to be enabled on the SQL server.
  • Garbage data could be sent to the SQL Server as it will be an SMTP server as well. SQL server may not be able to handle this load. Especially not 1 TB of garbage data.

What now?

I hear you shouting from behind your screen. “I use sp_send_dbmail to send administration and notification messages from my SQL server!”. This is fine. These types of emails do not take up a lot of space and they will not consume large amounts of resources to send out”. I, however, have never liked sending administration emails using sp_send_dbmail for the following reasons:

  • You will have to enable SMTP on the server which is a potential security risk.
  • You will have to connect the server to the internet and keep it that way. This is also a potential security risk.

What I do to prevent sending email with sp_send_dbmail

Let’s be honest, it’s not always avoidable and I have sp_send_dbmail enabled on some of my servers due to the problem mentioned above. However, when it comes to maintenance, I like to keep my system on a central server which sends instructions to my other servers like backup databases, rebuild indexes, etc. I will collect the data and store it on the central server and send out notifications based on the data that I collected i.e. disk space usage and so forth. Third party applications monitor the performance of the SQL Servers and will send out notifications based on the data it collects. In the end, there is no sending email with sp_send_dbmail and no need to enable SMTP, or sp_send_dbmail for that matter, on a production server.

Conclusion

In the end it will be for you to decide whether it is necessary to enable sp_send_dbmail on your SQL server as it is not always avoidable, even though there are risks involved. Whether there is a right or wrong answer is debatable and as mentioned, every SQL expert has his or her own opinions regarding the matter. When deciding though, keep the above points as to why it could be a bad idea in mind and make an informed decision based on the information at hand.

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

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