SqlDependency Reliablity?

1) Yes, I consider it reliable as in it does correctly the purpose was designed to do (cache invalidation)

2) No. This is why you can only subscribe by issuing a query, this ensures that there is no race between the fetching of the data and new updates notifying

3) Database (or instance) restart signals all pending query notifications with an SqlNotificationInfo value of Restart. Read how SqlDependency and is based on Query Notification for a better understanding. As SqlDependency keeps an open connection to the database all the time, a database unavailability will be detected by SqlDependency even before any explicit query notification

4) No. More on this further down…

5) There is no ‘missed data’. Query Notification (and hence SqlDependency) never notify you about what data changed. It only notifies you that it changed. You are always supposed to go back and read all the data back to
see what had changed (and I refer you back to question/answer no. 2). A newly started application had not yet queried the data to begin with, so there is no change to be notified of. Only after it has first queried the data can it receive a notification.

From the description of your problem I’m not convinced you need query notifications. It seems to me that you want to act on any change, not matter when it happened, even if your application was not running. This is certainly not cache invalidation, it is change tracking. Therefore you need to deploy a change tracking technology, like Change Data Capture or Change Tracking, both of which are SQL Server 2008 and later only (not available in SQL Server 2005). With SQL Server 2005 is not uncommon to deploy a trigger and queue a message for Service Broker to handle the same problem you are trying to handle (detect changes, react to each row of new data).

Leave a Comment