There are many ways to implement queuing in the Microsoft environment, from message queues to file systems, but we will discuss the option of using MS SQL Server. This article should equally apply to any relational database and on any platform.
Advantages of using a database:
- Transactional integrity
- Concurrency control
- Failure recovery
- Scalable
- Easy to code
- Statistics/Reporting
- Batching
There are many ways to approach the solution, and input/criticisms are encouraged. For the purpose of this article, the solution will take on the simplest form.
Some issues will need to be addressed in a good, simple, scalable design. Let’s start with the code that needs to send an email. Besides the usual stuff (to, from, body), there may need to be priorities, retries and status. From these requirements, we can design a simple table in the database:
CREATE TABLE [dbo].[MailQueue] (
[ID] [int] IDENTITY (10000, 1),
[dtStamp] [datetime],
[DateToProcess] [datetime],
[DateProcessed] [datetime],
[FromName] [varchar] (100),
[FromAddress] [varchar] (400),
[ToAddress] [varchar] (400),
[CC] [varchar] (400),
<BCC> [varchar] (400),
[Status] [varchar] (800),
[ThreadLock] UNIQUEIDENTIFIER,
[AttemptsRemaining] [int],
[Priority] [int],
<Body> [text],
)
This simple table schema will provide the queue. Code will insert rows into the table, one per email to send. The real work will be done in a windows service that reads from the queue. Let’s go into what the ambiguous columns are for.
Status
- The status will start as ‘UnSent’ when the row is inserted.
- The status will change to ‘Sent’ if the email has been sent successfully.
- The status will represent some error if the mail wasn’t sent.
AttemptsRemaining
This will decrement each time the email service tries to send the email and fails. When picking up mail from the queue, the email service will only read rows where this value is greater that zero.
Priority
By making this number larger, the email service will use this to choose email rows by there priority first.
ThreadLock
This field will be set while the email service is trying to send the email. After the service has finished with the row, the field will be set back to null.
The SQL to get rows out:
BEGIN TRANSACTION
UPDATE MailQueue
SET ThreadLock = NULL
WHERE ThreadLock IS NOT NULL
AND DateProcessed < DATEADD( minute, 15, GETDATE() )
AND Status != 'Sent'
SELECT TOP 10 *
INTO #tmpRows
FROM MailQueue
WHERE ThreadLock IS NULL
AND DateToProcess > GETDATE()
AND AttemptsRemaining > 0
ORDER BY Priority DESC
UPDATE MailQueue
SET ThreadLock = '<MY GUID>',
DateProcessed = GETDATE()
FROM #tmpRows r
WHERE r.ID = MailQueue.ID
AND ThreadLock IS NULL
AND DateToProcess > GETDATE()
AND AttemptsRemaining > 0
COMMIT TRANSACTION
SELECT *
FROM MailQueue
WHERE ThreadLock = '<MY GUID>'
Three variables will need to be passed in; the amount of rows per batch (in this case 10), a GUID generated by the thread that will process the batch (in this case ‘<MY GUID>’), and the timeout on threads for retries of emails (in this case 15 minutes).
The Email Service
The service will run the above SQL and iterate the rows, sending out emails. The service may be multi-threaded, in which case each thread will run the SQL and process its own batch. The service may be run on many machines – still no two threads should ever process the same row at the same time.
It is the job of the email service to update the database. The following are example SQL for successes and failures:
UPDATE MailQueue
SET ThreadLock = NULL,
DateProcessed = GETDATE(),
Status = 'Sent'
WHERE ID = 10001<BR>
UPDATE MailQueue
SET ThreadLock = NULL,
DateProcessed = GETDATE(),
Status = 'Cannot access CDO.Message object'
AttemptsRemaining = AttemptsRemaining - 1
WHERE ID = 10002
If the email service or thread dies for some reason, the row will be put back in the queue by the next run select statement after the timeout period.
Coming up next, a C# example.
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
