Programming design architecture for my application

Question

I have the need from an asp.net web site to send out many SMS messages at once, and also poll a POP3 account for an incoming mail, and then SMS that out to many recipients, one at a time.

The way I am thinking of doing this is a windows service that would connect to my sql back-end to see if there are SMS messages to be sent out, like every 10-20 seconds or so. If so, get all the messages in a list, delete them from the table, and then proceed to send them out.

Same way with the pop account.

Any ideas on how to best provide this service without causing blocking in the asp.net web page when it is kicked off (e.g. messages added to sql server)?

Platform is windows server 2003 R2, sql 2008 standard, asp.net 3.5 SP1.

Thanks for your advice.

Answer 1

We have implemented similar scenarios using SQL Server service broker's Queueing mechanism. The idea is that every inserted SMS record is caught by a trigger which inserts a message containing the SmsID into the service broker Queue.

You then need a stored procedure which receives messages from the Queue. If there are no messages, your procedure will run until the next entry is inserted. That's OK, since it does not take up resources to listen to the Queue.

Next you'll need a Windows service who continuously (recursively) calls the STP, assembles the SMS and sends it.

The Advantage of the Service Broker Queue over a flag in a table is thread safety. This way you could have as many instances of your Service as you want w/o having to worry too much about concurrency issues.

You can find a nice Service Broker tutoial here: http://www.developer.com/db/article.php/3640771

Answer 2

Instead of using an Sql Server for the queuing you could use MSMQ (Microsoft Message Queuing) for this.

MSMQ is quite easy to set up and once it is up and running it is more scalable than Sql Server.

So what you could do was to setup a new queue in MSMQ that would receive the messages you wanted to send. The message would normally be some sort of Message object that describe the message, the sender and the recipient.

Then you would either setup a service that would poll the queue at a regular interval or you could setup MSMQ to start a class of your choice each time a new Message was sent to the queue.

If you need a log of the messages you could have the service / sender object write to a log in sql server when the message was sent.