3

I have a number of state-full Windows Services that currently poll the database looking for new or changed records in specific Tables and/or Views. For obvious reasons this limits scalability and is adding unnecessary load to my SQL Server 2008 databases. I'd like to implement something akin to a publish & subscribe pattern where the database; through some mechanism; publishes messages to indicate when certain state changes occur.

This brings me to my question... What mechanisms reside within SQL Server 2008 natively; if any; which could support this pattern?

I’m open to other ideas, assuming I can get away from blind polling. As an extra bit of context, the message will need to carry only basic information to describe the nature of the change and what; by id; has changed.

Note: I’ve considered using SSIS packages and triggers but don’t think it’s appropriate, especially during times when the change rate is higher.

Improve this question
  • My knee-jerk response to this would be a trigger, especially if wrapping it within a web service, but I am curious why you think this wouldn't be appropriate? – OliverAsmus Jan 23 '12 at 21:27
  • @OliverAsmus It was my understanding that kicking off large numbers of concurrent SSIS packages is a relatively expensive choice... I could certainly be wrong on that though. Although this would probably only be a concern during peaking hours. – JoeGeeky Jan 23 '12 at 21:46
  • Sorry, I was referring to triggers. I think triggers would be a good benefit to use instead of a SSIS package. – OliverAsmus Jan 23 '12 at 21:52
  • @OliverAsmus Is there native support to call web services from a Trigger? If yes, then that could be a runner. – JoeGeeky Jan 23 '12 at 21:56
3

Sounds like your looking for query notifications.

Exactly as you describe, this feature allows you to subscribe to notifications which are generated when the results of a query change. Typically more efficient than repeatedly polling the database for changes.

Under the hood, notifications rely on the mechanisms used to maintain indexed views. Because of this, notification queries are constrained by the same restrictions as apply to indexed views. It's a lengthy set of requirements that you should review in detail to determine if it would make this a non-starter for your scenario.

Improve this answer
  • Yikes! That sure is a long list... I'm making my way through it but this may work for me. Thanks. – JoeGeeky Jan 24 '12 at 9:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.