Go Premium for a chance to win a PS4. Enter to Win


Best Way to Design a system to Event/Alert Emails

Posted on 2011-03-23
Medium Priority
Last Modified: 2012-05-11
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?
Question by:davidcahan
  • 2
  • 2
LVL 28

Accepted Solution

Ryan McCauley earned 2000 total points
ID: 35210529
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).

Author Comment

ID: 35210830
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....

Author Comment

ID: 35210855
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.

LVL 28

Assisted Solution

by:Ryan McCauley
Ryan McCauley earned 2000 total points
ID: 35210986
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 (http://blog.sqlauthority.com/2008/09/01/sql-server-2008-introduction-to-filtered-index-improve-performance-with-filtered-index/). 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.

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question