Partager via

SURVEY – Let us know what you think about Query Notification in SQL 2005

  • Article

We need your help!

We want to hear from you about our products and your experience with them.

Through this blog, we would be conducting periodic surveys to gather your feedback and try to channel it to the next versions.

For starters, we will start with a new feature in SQL 2005 called Query Notification (QN).

Please answer these questions and help us serve you better. Most of these questions are a Yes/No type.

  1. Have you used QN?

  2. Is there enough documentation and sample code for you to learn about the implementation details of QN?

  3. If not, then which topics would you like to see more information on?

  4. What new features would you like to see in QN? Any wish list would be very helpful for us.

Once again, thank you very much for your time to answer this survey.

PS – The current blog software doesn’t allow us to conduct surveys with all the jazz - radio buttons for choices, text boxes for free form answers etc. Instead, we would like you to post a comment with your answers to bullet points (1, 2, 3 etc) above. We hope you can still participate. 

Thank you once again.

POSTED BY : Sanchan Saxena

Comments

  • Anonymous
    September 30, 2006
  1. I have investigated QN thoroughly for use in my ASP.Net application and I believe I have several scenarios for which QN was specifically designed that I'd like to be able to use it with.  However, the fact that QN does not work on any table with a computed column (even if the computed column is not involved in the query) makes it a deal breaker for me. See the post titled "Query Notification / SQLDependency fails on table w/ computed column" at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=304164&SiteID=1

    So I've used it in theory but can't in practice.

    2. Yes and No.  For the mechanics of how to use QN from the standpoint of getting a notification it's fine.  But for the issues of setting up the security privileges required (for example in an ASP.Net app) see below.

    3.  This biggest documentation issue I ran into was how to implement QN in an ASP.Net application while trying to adhere to the principle of least privilege for the connection from the web server to the database.  I finally found on some blog somewhere the exact set of permissions to use and how to implement a user defined queue for the notifications.  I also spent a lot of time in reflector trying to figure out how the "automatic queue creation" stuff works in the guts of the SQLClient code.

    4. If only QN would support working on a table with a computed column if the query does not involve that computed column then I would consider QN one of the greatest features ever.
  • Anonymous
    September 30, 2006
    No. I am aware of it, but never used it so far. The issue is that Microsoft provides a lot of new features with every release to address commonly recurring issues, but somehow there is a reluctance on part of the architects/DBAs to adopt them right away - may be because they don't have enough background or they are just being a bit cautious in adopting something new. Features like SQL Broker are great, but I haven't seen any great adoption of it either. Another reason in some cases could be overlapping functionalities - for instance MSMQ, Biztalk and SQL Broker. Obviously they all have different purposes but address the same issue of messaging. Sometimes this message gets lost.
     
    I don't think there is much. The more documentation, the better adoption rate. Giving more code definetely helps for someone like me to understand the feature. It would be better to have case studies so as to convince people to adopt something new.


    As I said case studies would definetely help selling the idea to architects and DBAs.
     
    None from my perspective as I haven't used the feature yet to evaluate it's weaknesses and strengths.

    Hope this helps.
  • Anonymous
    September 30, 2006
  1. YES
    2. Yes and NO
    3. I would extend the event types documentation.
    4. I am happy with the current features.
  • Anonymous
    October 01, 2006
  1. No. I tried but its too complicated.2. No, no.3. Configuration, C# integration. More samples.4. More easy use.
  • Anonymous
    October 02, 2006
  1. Yes2. Good documentation3. I appreciate the topic is complex but I think the documentation is very wordy. Could do with some more simple pages.Some of the samples are outdated. This page refers to the wrong definition for servicenamems-help://MS.VSCC.v80/MS.MSDN.v80/MS.VisualStudio.v80.en/WD_ADONET/html/2dcb5c4e-1a83-4cf7-af65-721470be9deb.htm4. Implement the same for TSQL. The documentation states that they are available in "SQL Server 2005 and SQL Native Client", I take the former to mean TSQL but thast not true.
  • Anonymous
    October 09, 2006
    The comment has been removed
  • Anonymous
    October 23, 2006
  1. No - the restriction on not being able to use views is a "deal breaker " - all the systems we develop use views to abstract away the actual database implementation.
  2. Seems likely but is too "scattered" amongst various places.
  3. Can't comment.  
  4. Ability to use views as part of the QN is probably the biggest area.
  • Anonymous
    October 23, 2006
  1. YES
  2. YES but should be concentrated in a area/zone of msdn, also must be provided well and single documents about configuration.
  3. Whould be nice to have more data on how the cach work and how interops with memmory.
  4. So far, for the use i gave to QN, i'm happy.
  • Anonymous
    October 24, 2006
    I went through substantial and prolonged attempts to use Query Notification using ASP.NET 2.0 SqlCacheDependency. It is promising, but even though it would have been a tremendous boon to my site to keep using it, I had to abandon it and recode using my own custom caching. The primary reason for abandoning it was that SQL Server 2005 provides no easy method of (a) cleaning out the millions of old [i.e., bad] records that built up in the endpoint tables, and (b) there was no way to figure out how all of those records built up.  Every time I thought I had it figured out, they would start building up again.  I even opened a support case with Microsoft, something I have only done one other time in my career. With that being said, here is what needs to be changed/added:
  1. Documentation with lots of simple, real-world examples.  We don't need the entire multi-server SAP model documented, we need simple examples that will help people grasp concepts.  The documentation for what is an acceptible query is severly lacking, and very inconsistent.  There are no tips for speed considerations.  For example, is it better to use a parameterized query or a fully constructed character string based on the actual values?  Can a stored procedure be used?  How about a view?  What are the rules for using a UDF?  Etc.
  2. Direct access to tables, or else some quick and reliable way to clear everything out.  There is an ALTER DATABASE clause that can be used to reset everything, but it's not very clear what kind of troubles that will cause.  Obviously someone who wants to clear everything out doesn't care if all the messages are delivered, so just let them be cleared out!  If you want to have the ability to lock everything down, then fine, but then have the ability to open everything up also.
  3. Configuration wizard(s) and/or checklists.  The security and config setups are WAY too complicated, and I'm convinced I still didn't get everything right.  In general, SQL Server 2005 needs much better certificate management, with a control panel interface.  Some stuff currently even requires command prompt commands, which is insane.  Security is extremely important, and should be something that every single administrator can easily handle, whether it is a one-man-team or a big company.
  4. A control panel interface for service broker.  It needs something to manage all the message types, contracts, services, queues, etc.
  5. Ability to modify contracts without dropping and recreating them.
  6. While you're at it, please, please, please create some kind of wizards and control panel interface for Notification Services.  It's nutty that there is no way to create NS applications and setup the service without hand-creating XML files.  I really want to utilize NS, but it is too daunting at the moment, because it means I have to drop everything else for a while to learn/master it. Thanks for listening.
  • Anonymous
    November 02, 2006
  1. Yous.
  2.  Available documentation is (mostly) for starters and it's supposed that everything will be OK (and this can not be true).
  3. Personally, I lack documentation about QN internals (step-by-step description how it works and why each step can fail). I spent quite a lot of time trying to figure out why it doesn't work. Part of our problems ended in hotfix for .NET framework and part of them are still an issue (currently we solve it manually with custom DB-level trigger, but it's a workaround anyway, it'd be better if it worked without this).
  4. Wishlist includes: a) Some troubleshooting tool (like DB engine tuning advisor). It shoult include 'test QN' functionality (like 'Test account settings' in Outlook or 'Test connection' in data access tools) with detailed log of steps and results. b) 'Control center' for all QN-related activities (maybe a part of more general 'Service broker control center' which I also lack). Now I have to examine a dozen of different system views, joining them together, running profiler and looking at     performance counters to guess what's going on and still in some cases it's not enough. c) Fix for SQL Server to deal with the problem of stranded conversation dialogs that arise when using QN mechanism (well, it's not only about QN itself, but is closely related anyway)
  • Anonymous
    December 14, 2006
    Microsoft developers need to come up with the Authorty for implementations.  There are far too many developer blogs, msdn articles, etc that take us to all corners of the world for information that is most of the time incomplete or not working.