Database checking thread

Database checking thread.


One of the issues that E911 server faces is the database connection. Since the database is sitting on another box, so it’s possible that the server reboots, or network goes down.

I posted this question in the atl newsgroup since I know there are a couple of gurus there.

Here is the question I posted.
[
Hi, dear gurus in this group, I have a question about a design to check a valid database connection in my service application. My service is caching a database connection on another server. If for some reason, the database server is rebooted, then my connection become invalid, and any operations will cause an error. 1> Is there any way to get some event that the other database is available after rebooting? if that is the case, I can just simply catch that event and reopen my connection. 2> Another way is that I have to keep polling to check the database connection. Which I cannot run it in main thread since it may take long time, and I don't want to block other work. So I would think creating another thread just dedicated to check the database availability. Do you guys have any good suggestions. Thanks, Jianwei
]

Then , I got a couple of answers from Brian Muth and one answer from Alexander Nickolov, who are both gurus on the group.

[Brain]
Often the simplest solution is the best. If any of your database operations
returns an error indicating the the server is unavailable, then handle the
problem right there (presumably by closing and reopening the connection).

Why wouldn't that work?

[Me]
Hi, Brain, The issue is that the database is sitting on another box,and if I do that way, it may take long time to get some result back (like the connection error), and it will block other work. [Brian]
Ok, first tell me what you anticipate would be the cause of the database to
be offline? Maybe the remote machine has been rebooted, or the disk drive
has overflowed. Perhaps the janitor has tripped over the network cable. Is
pinging the target computer every second going to help your program to
recover any sooner? Of course not.

I'm having trouble seeing how you can make the program more "responsive"
when dealing with a physical connection problem.

Brian

[Alexander]

For example a caching proxy will be affected, e.g. a server that
caches some information and only goes to the database for
cache misses.

The real problem, however, is that the server is single-threaded.

[Brian]

I think it is worth pointing out to the original poster, than connection
pooling comes for free if you package any DB layer as a COM+ object
(assuming OLEDB is being used). One can open the connection object only when
one needs it, and then close it immediately after, and Component Services is
clever enough to look after the connection pooling for you. This "open-late
close-early" strategy is counter-intuitive, but actually scales better in
the long run. I don't know enough of the inner workings to comment on what
CS does when there is a blip in the database server, but I suspect it might
be wiser in the long run to leave the details to CS rather than
"over-engineer" the solution. It probably depends a lot on the requirements,
of which we have heard very little.