How to I tell if a client process is dead?

Background

We have worker processes that read “jobs” from a table in SqlServer then write the results back to other tables. One or more machines run one or more worker processes. A single SqlSever database (sometimes clustered) is at the centre of our world, so is likely to be where the solution should live.

We need a system that detaches if the worker process or the machine it is running on fails. (Not being able to talk to the database is a bad as the worker process failing in any other way.)

The worker process can, if needed, keep a connection and transaction open to the SQL Server while processing a job.

Options I have come up with.

Use database locks:

• Start a transaction in the worker process and lock a row (or application lock)
• We know the worker process is OK, provided the row is still locked
• If the worker process aborts for any re开发者_开发百科ason the SQL Server will abort the transaction and remove the lock.

Use a watchdog timer:

• Have a row for each worker process in a table
• The worker process updates a “last time OK” column in the row often
• If the column has not been updated for a long time, the worker process is dead.
• The SqlServer UST time is used, so as to avoid problem with different time on each machine.

Is there another option I have not thought about?

What problems do you see with the above options?

If you care our worker process are written in .NET and run on Windows servers. We don’t need to be able to port the solution to a different database vendor; we only need to surport Sql Server 2005 and 2008

We do exactly this, works like a charm. Kind of pro that you thought to use server time beforehand, we learned that only after a wintertime conversion ;)

Kerberos requires all computers in an active directory domain to have the same idea of UTC. As long as you use getutcdate() in SQL, I wouldn't expect any problem whatsoever.