Please could someone confirm my design approach:
I am designing a Windows service that processes rows in a SQL Server
2000 database table. The table has a datetime column. When the datetime
for a specific row is reached the service must perform an activity.
There may be multiple rows with the same datetime so the service will
have to assign individual threads to process each of these. I am
against the idea of the service constantly polling the database every
second as this seems a waste of resources.
This is my thinking:
1. The process that inserts the rows into the table also inserts a
message into MSMQ.
2. The Service listens to the queue and assigns a thread to each
incoming message.
3. The thread is set to sleep until the exact time.
4. As the thread awakens it checks the database to see the row still
exists before performing the required processing.
Is this the best approach? Otherwise any suggestions on a alternative
approach?
An alternative to 1. might to be use the new "SQL Service Broker" in
SQL Server 2005.
An alternative to the messaging approach might use a trigger to
activate a COM+ component on each insert/update to the table. The COM+
component could then communicate with the service to tell it to assign
another thread. Obviously in the event of a restart in the Service it
would have to collect all the table rows and assign threads to them.