44

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?
Phil Sandler
  • 27,544
  • 21
  • 86
  • 147
  • When you say that your application needs to do database actions, is it possible for the db to just do those actions? – Kermit Sep 17 '12 at 02:54

2 Answers2

55

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

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • Regarding your last point, when the app starts up it will query all data to see what has not been processed yet--same as when it gets a notification. I think the wording of my question may not have been misleading--I think the problem I need to solve is identical to one of caching even though it has nothing to do with caching. That said, I will read all the links you provided and reconsider my approach. Thanks for the excellent answer. – Phil Sandler Sep 17 '12 at 13:06
  • @Remus Rusanu, I read your wonderful blog post, but how can it even be useful for cache invalidation if "When the SQL Server 2005 is restarted, all query notification subscriptions are notified and ended." It seems if the server controls subscription duration, then there is no way to guarantee cache consistency? My use case is refreshing an Elastic Search index. – John Zabroski Dec 01 '15 at 15:42
  • 1
    @JohnZabroski The server cannot guarantee that no data has changed while the instance was offline. An admin could had copied over and replace the DB files, for instance. I recommend you ask a separate question. – Remus Rusanu Dec 01 '15 at 15:54
  • @RemusRusanu done - http://stackoverflow.com/questions/34031454/updating-elastic-search-index-when-sql-server-table-changes thank you – John Zabroski Dec 01 '15 at 22:30
  • I know it has been a couple of year later, but I just want to share for someone else that SqlDependency seem not totally reliable, read https://connect.microsoft.com/SQLServer/feedback/details/420996/avoid-false-positives-in-query-notifications-when-updating-unmonitored-columns – Jamo Jan 26 '18 at 23:36
5

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.

Mant101
  • 2,705
  • 1
  • 23
  • 27
  • 1
    How saying you using an alternative if it exists is better ? Do you know of an alternative? – Noctis Apr 22 '14 at 03:44
  • might be a bit late, but you could try https://github.com/dyatchenko/ServiceBrokerListener/issues – Choco Oct 24 '15 at 03:28