Should I use .NET (System.Net.Mail) to batch send my web application's emails or SQL (msdb.sp_send_dbmail)?

Question

Scenario:

1) My application is a .NET 3.5 C# web app and the database is SQL 2008.

2) Emails will be in the region of 100 to 1000 a day and are triggered by various web user interactions with the app.

3) Most emails will contain attachment(s) between 50KB and 5MB. Some emails will be HTML and some will be plain text.

4) All attachments will be sourced from a directory on the web server.

5) The SQL server is a separate machine to the Web Server. All SQL connections from the application are via SQL logins not Windows Authentication.

6) For a scalable solution, Emails to be sent will be queued in a database table ready for a batch process to pickup. Failed emails should be retried up to 4 times.

Dilemma:

I'm not sure whether to write a web server solution to send emails (e.g. a windows service which polls the emails ready to send) or maybe to use SQL database mail which is easy to setup and use and doesn't require much development.

The fact the attachments sit on the web server suggests to me to use a web server solution but I would be interested to see if I've missed something.

Answer 1

I think you've answered your own question. As you've chosen to queue the emails in the database IMO there would be little advantage to creating a managed solution on the webserver in order to send the emails.

The one thing I'd consider is the fact you're storing the attachments on the webserver, this means there would be more network traffic between webserver and database than if the webserver were sending the emails (assuming they run on different physical servers). Using your figures that could be 5GB traffic per day in attachments. However, I'd go with the DB solution and test it rigorously to see if traffic becomes a problem. If so, you haven't lost much dev time on the DB solution.

Answer 2

I'd go with .NET instead of sending emails from the DB. Sending emails in .NET is super-simple also, and won't require any kind of extensive setup or development time.

On a related note, I have no idea why SQL Server can send emails, as this functionality does not seem to fit within the data storage model. Does SQL Server also do 3D rendering?

Answer 3

First, be wary about sending 1000s of emails a day in bursts. That's a good way to get blacklisted from other mail servers or even your Internet provider. Second, I'd recommend going the .NET routine. This problem smacks of a service. Even if you do not build it as a service today, you may want to do so at some point in the future. With .NET, you could refactor your solution to send emails in parallel or access the queue in a multi-threaded way. All of this would be difficult in SQL Server. In short, .NET gives you the ability to make a more extensible solution than SQL Server.