Subscription Notification Service using SQL or .net?

Question

I need some advice. I have to create a subscription notification service for my system. I need something that will be flexible where I can set some parameters to send out notifications on certain days (before a subscription is expired) and to set accounts to expired. I need something to run in the background that will send out these notifications on time and expire accounts on time. Timing for the expiration will be crucial. So if a subscription is created on 02/12/2010 at 10:00 am it will expire on 03/12/2010 at 10:00am. I guess I could have a combination of SQL and .Net, but I am not sure the best way to approach this. Thanks

I am using SQL Server 2005, btw.

Answer 1

I think what I am going to do for now is setup a scheduled task to run an app to check the DB and send out the notifications like that. Thanks to all who looked ;)

Answer 2

You may consider using the built-in SQL Server timers: BEGIN CONVERSATION TIMER. This allow you to schedule yourself a timer in the database, and the timer enqueues a message into a queue. Using the queues activation mechanism a procedure is launched into execution and this procedure can do the logic of expiring the subscription and/or delivering a notification (eg. call sp_send_dbmail). The biggest advantage is that everything is self contained in the database. Even if your server crashes and the database is restored on a completely different machine, the notifications fire and the activated code runs, expiring the subscription. Other advantages are consistency in presence of Database Mirroring or Clusters failover events.