How to monitor tables in SQL Server for changes

Question

This question was asked quite some time ago, and while it covers possible solutions for SQL 2005 and 2008, it lacks a good solution for SQL 2000, which is still far too common.

I need a way to monitor certain fields of a database table for changes, and notify my application when these changes occur so that I can blast them out on the local network as broadcast messages where anyone with a client can listen for them and display them as alerts (think something similar to stock market data reaching specific thresholds).

I do NOT want to poll the database for several reasons. 1) I don't wish to add additional load to the servers. 2) I would rather get notifications in near real-time rather than wait for the polling frequency to expire.

Now, I could put logic in the applications that update the database, but the data can be updated from several sources, including the web and I don't want to deal with web servers sending notifications across DMZ boundaries, etc.. And I don't want to have to maintain this in 20 different applications (the more overpowering issue).

I've seen this done on SQL 2000 using extended stored procs and triggers, but the xp's seem to be difficult to make cross platform, and they break when installed on SQL 2005 and 2008. Maybe that's just bad code in the examples i've seen, i'm not sure, but I am looking for something that works in SQL 2000 and later versions.

Any ideas?

EDIT:

I've thought about dropping support for 2000, but that really doesn't solve my problem. I would like a solution that is going to continue to work for years to come. One problem with many microsoft technologies is that they drop support for them. For instance, Notification services does what I need it to do, but they decided to deprecate that in 2008 and it won't be available in the next version. So i'm looking for a solution that has a good chance of sticking around.

Answer 1

Very simple solution

You could have a trigger that calls a webpage, notifying of an update.

This may be quite bad, because if the server can't get to the web, for some reason, it may make the insert operation quite slow. Also, depending on the frequency of inserts, it could be equally bad.

Alternative plan

In a trigger, write to a queue. (I happen to be in love with MSMQ). Then, have something waiting against that queue, and you will get the messages in 'real time'. Again, it's prone to the frequency of updates, as above.

Better plan

Have a trigger that posts the data to a 'tblUpdatedThings' table, which you then poll. But I know you don't want to poll. Regardless, I consider this better, due to the reasons I describe.

Answer 2

You want your solution to be in the database, but you want it to be database-independent. You can't have it both ways. Pick one. If you want to be independent of the database, don't allow the sources to write to the database directly, but to call a central service that you control, and where you can trap any events of interest to you. If you want to use database functionality without polling, you have to deploy code that the database invokes, and you will have a dependency on future versions supporting your code.