Best Way to Design a system to Event/Alert Emails

I need to design a system to send out emails that are user driven (ex. user requests password, system admin wants to send emails to all users), conditionally driven (ex. a PC has failed to communicate with the server for X time) and conditionally event driven (a PC switches from red status to green status).  

I'd like to encapsulate all Business/Sending logic in one layer as much as possible (so I don't want to initiate send emails from the app layer and then have sql jobs that send other types).  I also want to track what emails have been sent, when sent, etc.

I'm thinking about using an Outgoing email table to hold all the notifications (with stored procedures hooked both to the app layer and sql jobs that put the emails into those tables).  I was going to put an AFTER INSERT trigger on the table so that critical alerts could be sent immediately.  At night, I would poll the table to send out all other, non time critical emails (like new account notifications, expired account notifications, etc).  Reason not to initiate sending via trigger for all emails is that some notifications could be inserting 1000's of rows at a time and I don't want to "overwhelm the trigger".

Does any of this sound reasonable?  Alternative solutions?
Who is Participating?

Improve company productivity with a Business Account.Sign Up

Ryan McCauleyConnect With a Mentor Data and Analytics ManagerCommented:
It would probably be better if you didn't use a trigger to send emails at all - even with an "AFTER INSERT" trigger, and error during the email send would result in the transaction being rolled back and the user (or application) getting an error that doesn't really apply (since they may not know that their action is what caused the email). Also, you'll hold up the application until the email is sent, since triggers are synchronous - the application will have to wait for the trigger to complete, which may take a few seconds. This may not be desirable.

I like your general idea, though - it centralizes the email handling and you don't have dozens of places to manage that an email can be sent from. Also, the table can keep track of other data - where the email request came from, which app/user put it there, was it application/website/etc generated, etc - all information you'd like to have available, but things you wouldn't want in the email itself.

I'd have the application and everything else write into that table when an email needs to be sent, and then have two SQL jobs that run on your server:

The first job runs every minute or two (or however often is prudent) checks the table for recent "high priority" emails and sends them. Once an email is sent, it's marked off in the table so it's not sent again
A second job runs a few times/day or every night and sends all other emails from the table, marking each one as sent after it does it.

This way, you can handle the emails with any frequency that you like, and it will be taken care of without holding up the application or any other processes, all in the background on the database server. Critical emails will be handled within minutes, while less critical emails will go out less frequently.

One note about your high-priority job, though - ensure that you treat this table like a high-volume queue and read/lock the rows accordingly. In this case, when you read it, you'll want to use a command like:

 WHERE HighPriority = 1

Open in new window

This way, the job will lock the rows it's working with, preventing any other jobs (like the overnight, low priority one) from grabbing and sending the same emails. The ROWLOCK hint tells SQL Server to block any other threads that try to read this row (until you're done with it), and the READPAST tells your process to skip by any rows locked by other processes as if they're not there (since somebody else is already handling them).
davidcahanAuthor Commented:
My one concern is what happens over time to the peformance of the "every 1-2 minute job" when that table starts to get a billion or more rows in it....
davidcahanAuthor Commented:
also, does SQL provide a mechanism in it's send mail that will let me know that the message has actually made it through the SMTP server?  that way, I can at least accurately record whether our server sent the message or not.  If it fails for some reason, I'd like to keep that row as not sent.

Ryan McCauleyConnect With a Mentor Data and Analytics ManagerCommented:
When you "Send" mail in SQL Server (using DBMail), you don't actually get a confirmation - it just hands off the mail and calls that good. However, there's an output parameter on sp_send_dbmail called @mailitem_id, and you can view the transmission status of a piece of mail using the following system view:

    sent_status, --0 new, not sent, 1 sent, 2 failure or 3 retry.
    sent_status != 1

Open in new window

Just record the @mailitem_id of each piece of mail as you send it in a column of your original mail queue and you can always check the status.

The speed of your query won't be a problem for a while as long as you keep the table indexed properly, but as it grows, you have a couple of options:

Add an index on the "Priority" column and ensure that this column also has statistics on it. SQL Server will know there are only a few rows that are high priority and will choose to use in index seek, which is a pretty quick operation.
Use a table partition on priority to physically group the high-priority rows together
Remove data from this table when you're done with it, perhaps moving it from your "EmailQueue" to an "EmailLog" table. That way, your constantly running email job only deals with unsent mail, and you can report off the sent mail from another table, keeping all your history.
You mention that you're using SQL 2005, but if you have the option to use 2008, there's a feature called "Filtered Indexes" that would keep things quick ( Just create a filtered index on unsent high-priority mail.

In any case, I wouldn't worry about this until you get a few million rows in your table - until then, a standard index that starts with Priority" will be fine to get the job done in under a second.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.