Solved

Best Way to Design a system to Event/Alert Emails

Posted on 2011-03-23
4
430 Views
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?
0
Comment
Question by:davidcahan
  • 2
  • 2
4 Comments
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 500 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:

SELECT *
  FROM YourEmailQueue WITH (ROWLOCK, READPAST)
 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).
 
0
 

Author Comment

by:davidcahan
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....
0
 

Author Comment

by:davidcahan
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.

0
 
LVL 28

Assisted Solution

by:Ryan McCauley
Ryan McCauley earned 500 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:

SELECT
    mailitem_id,
    recipients,
    subject,
    send_request_date,
    sent_status, --0 new, not sent, 1 sent, 2 failure or 3 retry.
    sent_date
FROM 
    msdb.dbo.sysmail_mailitems
WHERE
    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.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
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…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

758 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now