Windows service

  • Thread starter Thread starter NWx
  • Start date Start date
N

NWx

Hi,

I have an ASP.NET application which allow registered user to set-up
notifications, saved into an SQL server database.
I want to make a win service which periodically check this SQL server table,
and send e-mail at the moment a notification is scheduled.

Question: what is the best way to implement this? Should I create a timer,
and in timer event to check the database and send notifications?
Wouldn't this approach overloads the server's processor?
Is there any way to let SQL server to notify my (using some callback
mechanism) - to start my server process to send e-mail?

Any suggestion / advice is appreciated

Regards
 
NWx,

I don't know about the best, but one of the easiest would be to do it
all within SQL server - Periodic SQL agent job, which checks the table,
and calls xp_sendmail.

If you are only sending mail, then finer resolution that a minute is probably
not required, which is the lowest resolution you can get for a SQL job without
looping steps and using waitfor.

For a service - a periodic poll of a simple table, just for the existence of rows
is not going to cause much load, unless you insist on doing it every msec.
Bit harder to write a robust service that sends mail though. A WaitForSingleObject
call is a very low processing cost timer.

I vaguely remember a shareware console program that you could CreateProcess / spawn
to do the actual mailing from e.g. a service, which offloads some of the messy stuff.

Regards
AJ
 
Both ways are possible, but the more traditional one would still be to
periodically check the database for the validity of your condition. In
general it's usually a good idea to have the database do what databases are
good at, and leave everything else to the application servers.
If you wanted to have it the other way around you could use a trigger that
performs the operation of calling your component. You can use the extended
stored procedures for that, and simply call a dll on the server, or start a
process.

A simple read from a SQL table is not a very intense operation for the DB,
so you should be fine as far as load. Using a timer is a good idea. Using a
sleep command can also be considered. While the process is sleeping there's
no significant load on the application server. As I mentioned the actual
read operation should not have a great performance impact if you do it
correctly. Set up your table so that it's indexed correctly in order to give
you the fastest results.


--itai
 
My thoughts exactly (I was trying to convey that in my response).
My mother always told me never to say "Yuck" about food, though... And
anything you look at may be somebody else's food...

--itai
 
Itai,

Sorry. "Yuk" is not very eloquent.
Scary thing is that MS themselves promote doing stupid stuff in triggers.
It was (I'm virtually certain) the "correct" answer to one of the questions
in the 70-229 exam I just sat - send mail inside a trigger.

Another minor point - sleep is not so good in services, as it slows
down the response to the service control manager callback functions.

Regards
AJ
 
Back
Top