There’s a lot of scattered information out there on how to configure ASP.NET applications to leverage Microsoft SQL Server’s Query Notification and Service Broker services for caching in ASP.NET applications. The two best step by step tutorials I’ve found online are:
http://www.simple-talk.com/sql/t-sql-programming/using-and-monitoring-sql-2005-query-notification/
http://dimarzionist.wordpress.com/2009/04/01/how-to-make-sql-server-notifications-work/
Both of those articles should get you started for sure. I ran into issues keeping our application from crashing after a period of time though while leveraging Query Notifications for caching in a few of my sites. The biggest issue I found was that I would see the following exception in our logs:
When using SqlDependency without providing an options value, SqlDependency.Start()
must be called prior to execution of a command added to the SqlDependency instance.
Never did quite get a handle on what was going on here. I did figure out though that I could always find this in my Application log around the time that exception was thrown:
The query notification dialog on conversation handle '{A1FB449B-DEB3-E011-B6D2-002590198D55}.' closed due to the following error: '-8470Remote service has been dropped.
So, does this mean that I called SqlDependency.Stop() and now queued notifications aren’t going to be delivered. Are these critical errors that keep the application from coming back? I’ve read that a lot of the Query Notification messages you see in the log aren’t critical errors and can be ignored. I can’t ignore the timing of this error and the exception being thrown above though.
Anyway, I finally decided to pull this stuff out of our application until I get a better handle on what’s going on. The last straw was that I was trying to sync some database changes during a maintenance period and I couldn’t get them to sync because of a bunch of these SQL Query Notification issues. As I write this, I can’t even get my database back online as I’m waiting for ALTER DATABASE SET SINGLE_USER to complete (approaching 3 hours!!!). As I keep waiting, my Application log keeps filling up with the following Query Notification messages:
Query notification delivery could not send message on dialog ‘{FE161F6A-D6B3-E011-B6D2-002590198D55}.’. Delivery failed for notification ‘
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
I had a response to a post I made on the ASP.NET Forum and it was suggested that with all the cached items in the system, that SQL Server really could not catch up. This is a problem because not only does it slow the entire system down, but when you have to cycle the SQL Server service itself, it takes forever for the system to come back up because all of the notifications get requeued or something.