Apr
9
Written by:
Dylan Barber
4/9/2009 1:57 PM
The Back Story
When building Version 3, well that’s what we call it, of our online safety tracking and compliance software we created a small monster. In the initial stages of design I added in the ability to send users alerts about events that happen. For example a person in a Kansas grain elevator gets hurt and the incident is added to the recordable incident list and the manager in New York knows about it and can get an investigation going on what happened. Really helps the corporate guys stay on top of things right! After the first iteration of reporting I intended to move this to a purely online alert system with daily overview emails so users didn't get deluged with emails. I tried to change to a full web based system and users revolted! (Yes, users are revolting sometimes but this was users demanding to get more rather than less emails and worse yet it was the users who pay the bills!)
The Problem
At the time it was no big deal if an incident happened the alert email got sent to maybe 20 people max. Then came the problem. Wouldn’t it be cool if regional managers or even any users on the system could get these emails! So I was forced to add a way that different companies could allow these alert emails to be sent to anyone in their user list. Easy enough fix, a table that maps the user id to an allowed list, loop through the list, send the email to those people. Until the list became 1000’s of emails per an incident, we are running over 10,000 people on the portal so you can see it could be a problem. Saving an incident and having it send those emails at that time caused a delay of up to 2 minutes in some cases.
The First Solution
The first solution was built up by my trusty backup programmer, an email queue! Simple enough put all the info in the queue that is needed for the email have a DNN Scheduler job run and send out the emails. Worked for about a day until the job ‘stalled’ once and then tried to ‘catch up’. (If you have never played with DNN Scheduler the catch up allows the system to run all the missed times the scheduler would have run while it was down.) We found that even with the ‘catch up’ feature turned off it would run multiple times and send out 2 or more, sometimes even 10, copies of the email before the sent flag was set correctly.
Our Final Solution
Imagine 2 or 3 hundred users who have asked for many emails now calling you and saying yeah great but we didn’t want the same one 10 times. That's what we had! So our final solution was to move the whole email alert system to SQL Server and its mail queue. Doing so actually ended up being a much easier and maintainable solution for our site.
The Lesson
The lesson I would take from our experience with the DNN Scheduler is this. Its great for items that happen once a day/month/weekly/year or items that its not going to matter if it skips once or runs twice like Purge Users Online, but if you need more control and assurance that something is happening and only happening once then move to a SQL Agent job or some other outside DNN system to assure that!
For those wanting to know we were on DNN 4.8.2 when we did this and since our SQL email queue has worked like a champ we have not tried it on the scheduler in any newer DNN versions
I would love comments on a better way to do this as our SQL Server fix is not always a solution in a shared hosting environment.