I started looking at this recently with the very limited knowledge that the ASP.NET Cache could somehow make use of a SqlCacheDependency to invalidate the cache when a table (or a queries results) change. This lead me to the SQLDependency and SqlNotificationRequest classes. While the details of the SqlNotificationRequest class still elude me I did manage to learn a little about the SQLDependency class. The Start method required greater permissions then I will be able to give the user my process will connect to the database as, so I did some research and successfully created a Queue and Service Broker which my test app was able to use to get notifications.
My problem is that it doesn't work if I run multiple instance of my test app. I've seen little hints here and there that make it sound like I need one setup (Queue, Service Broker, ect..) per instance for this to work which sounds messy and unmaintainable.
Can any one confirm that this is the case, or maybe even suggest some alternative solutions?
Also, we are running SQL Server 2005.