SqlDependency Reliablity?

Question

My current situation is that I have an application that needs to be notified when new data arrives in a database table. The data is coming from an external source (that I have no control over--this is this only integration option). When new data arrives, my application needs to take certain actions--basically query for the new data, handle it, insert the result into a local table, etc.

I want to avoid polling if possible, as the data is expected to be handled in real time. That said, making sure no data ever gets missed is the #1 priority.

My questions:

1. Is SqlDependency generally considered reliable?
2. Do I need to be concerned about race conditions, e.g. I am handling one change when another arrives?
3. What happens when the database gets rebooted? Will my app recover and start receiving changes again, or will I need a fail-safe timer of some sort that will resubscribe to notifications periodically?
4. Most of the articles I have read on the topic address SQL Server 2005. I am using SQL Server 2008 R2. Is there a newer technique that is preferred over SqlDependency?
5. (Edit)Also, What if the application goes down?  I guess I would have to query for missed data on start up?

Answer 1

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).

Answer 2

Coming at it from the point of view of a .net developer to just wants to use it for cache invalidation it has been a real pain and isn't completely reliable.

Set up and troubleshooting has been particularly painful, we get it working okay in one environment but then it doesn't work in another. Figuring out why has been difficult and time-consuming.

Even when it is all running it isn't completely reliable. SQL Server can drop notifications if it under heavy load and there are known issues with it restarting and notifications not resuming: http://connect.microsoft.com/SQLServer/feedback/details/543921/sqldependency-incorrect-behaviour-after-sql-server-restarts.

I would avoided if there is an alternative technology the does what you want and is less troublesome.