tcp\ip server tracking database changes

Question

I have a tcp\server ( .NET 3.5 ) that notifies connected clients about aah interesting events. These events occur because an aspx website is writing stuff to a database( SQL 2005 ) What would be a good way for this server to monitor the database so to speak can the db push info to the server in any way?

any suggestions thoughts welcome thanks

Answer 1

Take a look at SQL Broker Service - http://msdn.microsoft.com/en-us/library/ms345108(SQL.90).aspx.

To take this further you can use SqlDependency. There is a nice small article on CodeProject - http://www.codeproject.com/KB/database/chatter.aspx.

There are some caveats using it but many of them are already described on the web. Use the following resources:

1. http://www.wintellect.com/CS/blogs/jprosise/archive/2005/12/06/sqlcachedependency-hell.aspx
2. http://davidhayden.com/blog/dave/archive/2006/04/29/2929.aspx

SQL Broker services is quite vast to get in details with, but I hope I gave you good resources to start up with.

Answer 2

I have a similar problem. Need instant notification of data insertion. I used SQL Broker Service and CLR stored procedure (written in C#). The procedure notify about the inserted data to another application using socket.

You can write similar CLR stored proc or CLR Trigger (Offcource asynchronous when using service broker)