1 / 32

SQL Server 2005 Query Notifications

SQL Server 2005 Query Notifications. in ASP.NET 2.0 & ADO.NET 2.0. Julie Lerman The Data Farm. About Me. Independent Software Developer 20+ years development experience Microsoft .NET MVP ASPInsider INETA Speaker Various publications & conferences

gay
Download Presentation

SQL Server 2005 Query Notifications

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Server 2005 Query Notifications in ASP.NET 2.0 & ADO.NET 2.0 Julie Lerman The Data Farm

  2. About Me • Independent Software Developer • 20+ years development experience • Microsoft .NET MVP • ASPInsider • INETA Speaker • Various publications & conferences • Blogs: thedatafarm.com/blog blog.ziffdavis.com/devlife • Founder and leader of Vermont .NET • Vermont Software Developer Alliance Board

  3. Agenda • SQL Server 2005 Query Notifications • ADO.NET 2.0 Notifications • ASP.NET 2.0 Cache Invalidation

  4. Query Notifications • New feature in SQL Server 2005 • Works with SQL Server Service Broker • Enables asynchronous database apps • Uses queues as first class objects • T-SQL can interact with the queues • Transactional Message Processing • Reliable Distributed Processing • *Not* Notification Services

  5. Query from Client NotificationFlag How they work SQL Server Data Change Detection for rowset DML SQL Server Service Broker Message Queue Services Listener Application SQL Server 2005

  6. Uses • Queries that do not change frequently • Queries that need to stick around for a while Look-up Tables • When details of change are unimportant • Only reports that “something has changed” • Web Applications • Page level or middle tier • Windows Apps • Server tier (e.g. remoting or web services) • Client side: no more than 10 concurrent users

  7. Query Rules • Use explicit columns in command text • Required for Change Detection internals • “select *” will not work • Use two-part names to reference tables • e.g.: owner.tablename or schema.tablename • NO! • UNION, Outer Joins, TOP, DISTINCT, COUNT*, aggregates (AVG, MAX, etc.), INTO, more... (see resources for list) • TEMP, table variables, multiple views, system tables/views, queues, more....

  8. SQL Server Rules • Enable Service Broker on database • SQL Server security has this off by default ALTER DATABASE mydb SET ENABLE_BROKER • Database Compatibility Level = 90 • Older databases (e.g. pubs) might be 80 or lower • sp_dbcmptlevel or Database/Properties/Options • Permissions for non-admins • One time setup per account

  9. Non-Admin setup TSQL • sql_dependencey_subscriber role in SQL Server • EXEC sp_addrole 'sql_dependency_subscriber’ • Permissions needed for users to Start • GRANT CREATE PROCEDURE to startUser • GRANT CREATE QUEUE to startUser • GRANT CREATE SERVICE to startUser • GRANT REFERENCES on CONTRACT:: [http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to startUser • GRANT VIEW DEFINITION TO startUser • Permissions needed for users to Execute • GRANT SELECT to executeUser • GRANT SUBSCRIBE QUERY NOTIFICATIONS TO executeUser • GRANT RECEIVE ON QueryNotificationErrorsQueue TO executeUser • GRANT REFERENCES on CONTRACT:: [http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to executeUser • EXEC sp_addrolemember 'sql_dependency_subscriber', 'executeUser' *Sushil Chordia on blogs.msdn.com/dataworks

  10. ADO.NET 2.0 Integration • SqlDependency class [System.Data.SqlClient namespace] • Uses SqlQueryNotificationService in MSDB database • SqlNotificationRequest class [System.Data.Sql namespace] • Requires you to write your own listener • Works with DataReaders & DataAdapters

  11. Demonstration SQLDependency:Basic Functionality

  12. SqlDependency Steps • SqlDependency.Start(Connection) • Only needed once per app • e.g. in Global.ASAX • Create SqlCommand • Create SqlDependency • Attach SqlDependency to SqlCommand • Execute SqlCommand • Create delegate to listen for OnChange • and/or SqlDependency.Changed property

  13. Data Change • Service Broker • creates queue • creates service • creates sproc for cleanup SQL Connection The Plumbing SqlDepStart “something changed!” DataContainer Query w/ Depend Application SQL Server 2005

  14. SqlDependency.Start • Creates static non-pooled connection • Creates default queues and services • Optional • Create your own custom services & queues in SQL Server and pass the queue name as a parameter with Start SqlDependency.Start(connString) or SqlDependency.Start(connString,”myQueue”,”myService”)

  15. SqlNotificationEventArgs • Notification is not always about a change • Type • Source • Info • One of 18 Enums can be returned • Update • Invalid: Query does not follow rules • Options: Not all SQL Server options are set • more… • Notification returns immediately for problems

  16. In the Middle Tier • Cache Data at the Application Level • All client sessions get data from that cache • Notification triggers the cache to update

  17. Demonstration SqlDependency in the Middle Tier

  18. SqlNotificationRequest • Lower level than SqlDependency • Does not use default queue in SQL Server • Custom Service and Queue must be created in SQL Server in advance • Does not require an application wide “Start” • Definitely for the middle tier

  19. SqlNotificationRequest Steps • Create QUEUE & SERVICE in SS2005 • Create SqlCommand • Create SqlNotificationRequest • Point request to QUEUE & SERVICE • Attach SqlNotificationRequest to SqlCommand • Execute SqlCommand • Run WAITFOR Query • Handle results of WAITFOR

  20. Create Queue & Service CREATE QUEUE myqueue CREATE SERVICE myservice ON QUEUE myqueue ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification])

  21. Demonstration SqlNotificationRequest

  22. Cache Invalidation 1.x • Invalidate triggers with: • File Change • Key Change (pointing to different cache) • Time Based • Database change invalidation • Complicated trickery • Can be done with bells and whistles involving SQL Server TRIGGERS and file dependency

  23. Cache Invalidation 2.0 • SqlCacheDependency class • System.Web.Caching namespace • Inherits from System.Web.Caching.CacheDependency • Internals similar to SqlDependency • Plugs into the Output Cache • Two methods • Server-side code using SqlCacheDependency • Client side within <% OutputCache > directive

  24. Demonstration SqlCacheDependency

  25. SqlCacheDependency Steps Similar to SqlDependency • SqlDependency.Start • Create SqlCommand • Create SqlCacheDependency • Attach SqlCacheDependency to command • Execute Command • Add SqlCacheDependency to the Cache of the Response object • Set Cache properties

  26. <% OutputCache…> • Remember the SqlDependency.Start • Can be in global.asax System.Data.SqlClient.SqlDependency.Start(ConfigurationManager.ConnectionStrings[“myConn”].ConnectionString) • SqlDependency=“CommandNotification” • Flags ASP.NET to use SQL Server 2005 Query Notification • Applies to all valid queries related to page • SqlDependency= anything else • Will use polling method to check for changes to database • Requires additional setup in web.config & aspnet_regsql.exe command line tool • Works with SS2005, SS2000, SS7

  27. Demonstration <% OutputCache…>

  28. Conclusion • Query Notification is a powerful feature for ASP.NET Applications • Best target is read-mostly lookup tables • Use in middle with ADO.NET classes • Use at page level with ASP.NET classes

  29. Contact Info Julie Lerman jlerman@thedatafarm.com www.thedatafarm.com www.thedatafarm.com/blog

  30. Resources Query Notifications in ADO.NET 2.0Bob Beauchemin, MSDN Online April 2005 What’s New in ADO.NET 2.0 Julia Lerman, MSDN Magazine April 2005 Caching Improvements in ASP.NET WhidbeyG. Andrew Duthie, MSDN Online Feb 2004 Improved Caching in ASP.NET 2.0Stephen Walther, MSDN Online, June 2004 Asynchronous Command Execution in ADO.NET 2.0Pablo Castro, MSDN Online, July 2004 ADO.NET 2.0 and System.Xml v.2.0 – The Beta VersionAlex Homer, Dave Sussman, Mark Fussell Addison-Wesley, April 2005

  31. Please fill out the survey forms!They are the key to amazing prizes that you can get at the end of each day Thank you!

More Related