Application Email Architecture

Question

I've inherited an ASP.NET website written in c# using an mssql 2k8 database that is sending emails based on an insert into a message table via a trigger using database mail :| One of any failures and too many things rollback, aren't logged and no email is sent...

What a mess. I've written a few email subsystems in the past and I thought I'd ask for input before starting a rewrite here. What are the best practices for queuing/sending email in a Microsoft environment? Should I be pushing emails to a queue, from there pulling, sending, logging? DB Email seems like a fail. Is the queue managed in SQL server? Does SQL Server call a C# app? If an email send fails, what's a good approach for recovery?

Thanks for any insight!

Answer 1

You can send mail via sql-server. For more refer this

The architecture of it is here

Another implementation of sending mail via c# is this as they have developed an email factory for implementation... hope this helps

Answer 2

sp_send_dbmail places the mail request into a queue in msdb. After the transaction that sent the mail commits, the queue activates an external process that handles the SMTP delivery, including retries, logging and all that. Overall system is quite resilient, scalable and performant.

Perhaps you're using the old, deprecated xp_sendmail based system?

Answer 3

I think you are correct to completely separate the use of system functions: use SQL for data, and push email on to a completely different 'service provider'; I assume you have some sort of business logic tier that will orchestrate this?

I can't talk in terms of 'best practice' from experience (for email specifically), but abstracting out the email service (just like you'd abstract out data-access) is definitely on the right path, and that's probably the critical decision you need to make right now. You could then have different implementations of the email (including SQL, if you really wanted to).

Depending on volumes - do you look at an asynchronous call or synchronous call? WCF seems like a good candidate to handle comms - this would allow you to fire data (for emails) into an end-point that had a queue built into it, or you could call (via WCF) a web service that acted synchronously.