While SQL Server data update notifications through the SqlDependency object seemed like a great idea, I now believe the architecture is fundamentally flawed...I'm looking forward to changes in this area to make the feature more robust in the next release(s) of SQL Server and/or the .NET Framework. Here are some of the problems I've run into:
- Massive amounts of required permissions in the default run mode
- Query types available are incredibly restrictive
- Ineffective tear down of resources, again, in default run mode, with no programatic workaround. (The worst part? The problem is actually most acute while doing active development...)
- Severe ramifications for any issue, whether caused by environment or poor coding
- Complex (and maybe unusable) setup for custom service/queue implementation to alleviate the first two issues. I never really even figured out that setup when operating in a non-default schema in a restricted permission environment.