Transcript SqlDependency

Sofia, Bulgaria | 9-10 October
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
Blogs: thedatafarm.com/blog blog.ziffdavis.com/devlife
Founder and leader of Vermont .NET
Vermont Software Developer Alliance Board
Sofia, Bulgaria | 9-10 October
Agenda
● SQL Server 2005 Query Notifications
● ADO.NET 2.0 Notifications
● ASP.NET 2.0 Cache Invalidation
Sofia, Bulgaria | 9-10 October
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
Sofia, Bulgaria | 9-10 October
How they work
SQL Server
Query from
Client
Data
Notification
Flag
Change
Detection for
rowset DML
SQL Server Service Broker
Listener
Services
Application
Message
Queue
SQL Server 2005
Sofia, Bulgaria | 9-10 October
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
Sofia, Bulgaria | 9-10 October
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....
Sofia, Bulgaria | 9-10 October
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
Sofia, Bulgaria | 9-10 October
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
Sofia, Bulgaria | 9-10 October
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
Sofia, Bulgaria | 9-10 October
Sofia, Bulgaria | 9-10 October
Demonstration
SQLDependency:
Basic Functionality
SqlDependency Steps
1. SqlDependency.Start(Connection)
•
•
Only needed once per app
e.g. in Global.ASAX
2. Create SqlCommand
3. Create SqlDependency
4. Attach SqlDependency to SqlCommand
5. Execute SqlCommand
6. Create delegate to listen for OnChange
7. and/or SqlDependency.Changed property
Sofia, Bulgaria | 9-10 October
The Plumbing
SqlDep
Start
“somethin
g
Data
changed!”
Container
Query w/
Depend
Application
Data Change
SQL Connection
Service Broker
•creates queue
•creates service
•creates sproc for cleanup
SQL Server 2005
Sofia, Bulgaria | 9-10 October
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”)
Sofia, Bulgaria | 9-10 October
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
Sofia, Bulgaria | 9-10 October
In the Middle Tier
● Cache Data at the Application Level
● All client sessions get data from that
cache
● Notification triggers the cache to
update
Sofia, Bulgaria | 9-10 October
Sofia, Bulgaria | 9-10 October
Demonstration
SqlDependency in the Middle Tier
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
Sofia, Bulgaria | 9-10 October
SqlNotificationRequest Steps
1.
2.
3.
4.
5.
Create QUEUE & SERVICE in SS2005
Create SqlCommand
Create SqlNotificationRequest
Point request to QUEUE & SERVICE
Attach SqlNotificationRequest to
SqlCommand
6. Execute SqlCommand
7. Run WAITFOR Query
8. Handle results of WAITFOR
Sofia, Bulgaria | 9-10 October
Create Queue & Service
CREATE QUEUE myqueue
CREATE SERVICE myservice ON QUEUE myqueue
([http://schemas.microsoft.com/SQL/Notifications/
PostQueryNotification])
Sofia, Bulgaria | 9-10 October
Sofia, Bulgaria | 9-10 October
Demonstration
SqlNotificationRequest
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
Sofia, Bulgaria | 9-10 October
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
Sofia, Bulgaria | 9-10 October
Sofia, Bulgaria | 9-10 October
Demonstration
SqlCacheDependency
SqlCacheDependency Steps
Similar to SqlDependency
1. SqlDependency.Start
2. Create SqlCommand
3. Create SqlCacheDependency
4. Attach SqlCacheDependency to command
5. Execute Command
6. Add SqlCacheDependency to the Cache of
the Response object
7. Set Cache properties
Sofia, Bulgaria | 9-10 October
<% OutputCache…>
● Remember the SqlDependency.Start
● Can be in global.asax
System.Data.SqlClient.SqlDependency.Start(Configura
tionManager.ConnectionStrings[“myConn”].Connectio
nString)
● 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 Sofia, Bulgaria | 9-10 October
Sofia, Bulgaria | 9-10 October
Demonstration
<% OutputCache…>
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
Sofia, Bulgaria | 9-10 October
Contact Info
Julie Lerman
[email protected]
www.thedatafarm.com
www.thedatafarm.com/blog
Sofia, Bulgaria | 9-10 October
Resources
Query Notifications in ADO.NET 2.0
Bob Beauchemin, MSDN Online April 2005
What’s New in ADO.NET 2.0
Julia Lerman, MSDN Magazine April 2005
Caching Improvements in ASP.NET Whidbey
G. Andrew Duthie, MSDN Online Feb 2004
Improved Caching in ASP.NET 2.0
Stephen Walther, MSDN Online, June 2004
Asynchronous Command Execution in ADO.NET 2.0
Pablo Castro, MSDN Online, July 2004
ADO.NET 2.0 and System.Xml v.2.0 – The Beta Version
Alex Homer, Dave Sussman, Mark Fussell
Addison-Wesley, April 2005
Sofia, Bulgaria | 9-10 October
Sofia, Bulgaria | 9-10 October
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!
Sofia, Bulgaria | 9-10 October