Suggestions for a architecture

[g_hickley]

I have a database table with columns of URLs and datatimes. At the
precise datetime I would like to a perform HTTP Get request to the URL.
There may be multiple requests to different URLs that need to be
performed at the same time.

It doesn't seem efficient to have a service polling the database every
second looking for requests to process.

I have been doing a bit of research into MSMQ and SQL Service Broker in
SQL Server 2005. But the messages seem to be processed in the order
they arrive not on a specific datetime value.

Any pointers for what would be the best design approach?

 

[Remus Rusanu]

Hello,

With Service Broker you could use dialog timers. Open a dialog for each
request you need, and then create a timer on that dialog set to fire at the
moment you desire. You can add Service Broker activation into the mix so
that the timer actually lauches an CLR stored procedure that goes and does
the HTTP request. This way you don't actually need any external code, is all
contained within the database. In addition, you get durable, persistent
timers. Dialog timers are stored in the database, so you get all the
benefits that come with this, like persistent state between server restarts,
backup/restore. If high availability is desired you also get clustering
failover and database mirroring for free.

--
This posting is provided "AS IS" with no warranties, and confers no rights.

HTH,
~ Remus Rusanu
SQL Service Broker

 

[g_hickley]

Remus,

Thank you for your reply. You have given me some good suggestions.


Gordon