SQL Server Query Notification

We recently tried using SQL Server Query Notification (aka SQL Cache Dependency) for something much more involved than the usual “lookup table” (using it to send relevant data updates from a large OLTP system into a secondary aggregating system).

It’s been a whirlwind of “I’m impressed” moments (with a share of little snags too).

Here’s what we were impressed by:

  • how efficient it is – it uses SQL Server’s Indexed View mechanics to trigger update notifications, rather than polling (here's a great article that informed us of this)
  • when a Query Notification (QN) subscription is in-effect and SQL Server goes down or is inaccessible, QN will actually make repeated attempts to reconnect to SQL Server every 60 seconds (described here)
  • if the subscription is terminated without being cleanly closed, it will be cleaned up eventually, as QN does periodic checks for orphaned subscriptions

Here are the snags / lessons-learned:

  • we tried to use QN solely within SQL Server itself, but realized it works only in conjunction with ADO.NET (SqlClient) – and therefore had to develop a little .NET Windows Service to initiate QN
  • QN doesn’t tell you “what has changed”; only “that something” has changed – we had to write our own logic to narrow in on what changed
  • QN is subject to all the limitations of Indexed Views (no outer joins, no query hints, etc., etc.)
  • we ran into the (apparently known) issue where when attempting to stop the QN subscription immediately after a notification, the SqlNotification.Stop method hangs.  Our (brute-force) solution was to attempt to stop the subscription in a new thread, meanwhile calling Stop on our service, after giving the service 5 seconds of rope before terminating (using ServiceBase.RequestAdditionalTime(5000))

We also had to learn all the “usual” lessons, such as needing to enable the SQL Server Service Broker & CLR.

The “I’m impressed” moments outweighed the snags; and we’re glad to have Query Notification in our toolbox!

p.s.  SQL Server Change Data Capture also intrigued us, but it appears to be relegated to SQL Server 2008 Enterprise Edition (we needed to use SQL 2005).