Email Notification Service

Question

What are some ideas (using .NET and SQL 2005) for implementing a service that sends emails? The emails are to be data-driven. The date and time an email is to be sent is a field in a table.

Answer 1

Usually, I just spin up a process such as http://caspian.dotconf.net/menu/Software/SendEmail/

Answer 2

I was going to suggest SQL Server Notification Services, which will handle the job nicely. But I see that's been dropped from SQL Server 2008, so you probably don't want to go there.

Answer 3

You can use triggers to send emails on UPDATE/DELETE/INSERT. The triggers can be implemented with .Net, just send mails from there using the classes in System.Net.Mail namespace.

Here is a good article how to implement CLR (.Net) triggers in .Net.

For a light-weight SMPT server, and to minimize the delays, you can use the one, recommended in Kenny's answer.

Answer 4

I have built several high volume email notification services used to send data driven emails in the past. A few recommendations:

• Look into using a high quality email service provider that specializes in managing bounces, unsubscribes, isp and black list management, etc. If sending email is critical to your business, but not your main business it will be worth it. Most will have an api for sending templated messages, click tracking, open rates and will have provide triggers etc.
• Look into the SQL Server Service Broker to queue the actual messages, otherwise you can consider Microsoft Message Queuing Services. There is no need to build your own queuing service. We spent too much time dealing with queing infrastructure code when this was already solved.
• Develop a flexible set of events on your business tier to allow for the triggering of such messages and put them in your queue asynchronously, this will save you alot of grief in the long run as opposed to polling on the DB or hacking it in with Database triggers.

Answer 5

Data Driven SSRS Subscriptions? Just a thought.

Answer 6

Thanks everyone for the feedback. For simplicity's sake I've started out with a SP that looks up the reminders to be sent and uses sp_ send_ dbmail (SQL Database Mail) to send the emails. This runs on a job every minute. I update the record to indicate the reminder was sent with the MailItemId sent back from sp_ send_ dbmail. The volume of reminders expected is worst case in the 10^2 range per day.

I'd love to hear feedback about any shortcomings people think this solution may have.

By the way, I can't believe Vista doesn't come with a local SMTP server! Luckily Google is more generous, I used Gmail's server for testing.