MS SQL Server mail queue

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),
 [varchar] (400),
[Status] [varchar] (800),
[ThreadLock] UNIQUEIDENTIFIER,
[AttemptsRemaining] [int],
[Priority] [int],
 [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

 -- retry old mails that failed

UPDATE MailQueue
SET ThreadLock = NULL
WHERE ThreadLock IS NOT NULL
AND DateProcessed < DATEADD( minute, 15, GETDATE() )
AND Status != 'Sent'

 -- select mails to send

SELECT TOP 10 *
INTO #tmpRows
FROM MailQueue
WHERE ThreadLock IS NULL
AND DateToProcess > GETDATE()
AND AttemptsRemaining > 0
ORDER BY Priority DESC

 -- update to lock them

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 rows

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:

 -- success

UPDATE MailQueue
SET ThreadLock = NULL,
DateProcessed = GETDATE(),
Status = 'Sent'
WHERE ID = 10001

  -- failure

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.