Microsoft.NET

……………………………………………….Expertise in .NET Technologies

SQL Server 2005 Notification Services with ASP.NET 2.0

Posted by Ravi Varma Thumati on December 21, 2009

This article explains SQL Server 2005 notification services with ASP.NET 2.0 and demonstrates how to set up and test it using code samples. It initially provides a brief overview of the services and then examines various configuration aspects. It also deeply covers some of the common issues that prevent queries from working with SQL Server 2005 query notifications. At the end of the article you will learn the concept of polling based Invalidation techniques with the help of source code.

Article Contents:

  • Introduction
  • Notification based configuration
  • Polling-based Invalidation
  • Related Links
  • Downloads
  • Conclusion

Introduction

Caching is one of the common techniques that is used to increase system and application performance. Caching is used by web applications to cache frequently used web pages. Some of the key considerations to caching data are if:

  • The data is expensive to create.
  • The data does not change very frequently.
  • The data is used frequently.

Caching usually holds data in memory. With caching also comes the responsibility of ensuring that updated data is served as soon as possible and also managing how much to cache. I recently put together an article that talks about using kernel mode caching in IIS. Today, I will discuss another advanced caching technique provided by ASP.NET 2.0 using SQL Server 2005 using notification services. This new caching method is known as SQL Cache dependency. SQL Cache dependency enables a developer to cache pages that are dependent on data from SQL Server tables. This technique has 2 methods – Polling based and Notification based cache invalidation. In the polling based method, applications query if the underlying data changed at specific time intervals. In notification based services, SQL Server sends out a notification to the “subscriber” of the data. The application can then take whatever action it wants.

Notification based technique is most likely a preferred technique over the previous polling based method. With this technique, any application that depends on data within a SQL Server 2005 database can subscribe to be notified by SQL Server, when the underlying data changes. The way it works is that the application queries the data from SQL server and puts in a request to be notified when the data that was just queried changes. It then caches the results of the query as long as no notification is received. When a change notification is received by the application, it invalidates the cache and then re-queries the data with a new change notification request. The next request to be handled by the application will show the updated data. SQL notification services can send timely and personalized messages to millions of subscribers. These subscribers can be applications or devices.

One of the biggest advantages of this technique is that your cached data is valid as long as the underlying data has not changed. Unlike in earlier versions, the application need not poll the database with a specified time interval to see if the data has changed and then update the cache.

I will focus first on how we can setup use notification services with ASP.net 2.0 web applications. There is a lot of content out there that describes this, but probably very few that explain how it works and how to set this up and test. SQL Server 2005 Notification-based cache invalidation uses the query change notification mechanism of SQL Server 2005 to detect changes to the results of queries. Query notifications are sent over SQL connections, just like ADO.NET queries. Think of query notifications like long running queries that wait for a “notification” from SQL Server indicating that the underlying data has changed. This allows the application to invalidate the cache.

SQL Server 2005 System Requirements: Notification based configuration

A notification based dependency can be directly configured using the OutputCache directive of an ASPX page with the CommandNotification attribute. This indicates to ASP.NET runtime that a notification based dependency should be created for the page or the DataSource control. Let us first look at setting up Notification services.

1.    Run SQL Server 2005 Setup and then select Notification Services along with other features that you wish to use.

2.    Grant login and query notification rights to the appropriate Windows account on your Sql Server 2005 machine. To allow login for NETWORK SERVICE, open SQL Server management studio, expand Security, and add NETWORK SERVICE to the list of allowed login. Not only do you need to add this account to the list of allowed logins, but also add db_datareader and db_datawriter permissions to your database.

NOTE: The username will be NT AUTHORITY\NETWORK SERVICE if SQL Server is installed on the same machine as the web server. If SQL server is on a remote computer and you are using NETWORK SERVICE as the application pool identity, then add NT AUTHORITY\<computername>$.

The security identity running the query must have rights to register queries for notification in Sql Server 2005. This right can be granted with the following T-SQL command from SQL Server Query analyzer.

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO username.

The security identity running the query must also have rights to send query notifications from Sql Server 2005. This right can be granted with the following T-SQL command:

GRANT SEND ON SERVICE::SqlQueryNotificationService TO username.

NOTE: SQLExpress does not support Query notifications and therefore will not work. For NETWORK SERVICE account, enclose the username in double quotes.

ASP.NET 2.0

1.    Create your web page.

2.    Enable cache notification for SqlCacheDependency.

3.    Configure your page output caching. Set the “SqlDependency” attribute on the page to “CommandNotification.”  Any commands issued against a SQL Server 2005 back-end will automatically get wired up to use SQL Server 2005 query notifications. You can do the same thing on a SqlDataSource control.

Listing 1: On an ASPX page

<%@ OutputCache Duration="999999" SqlDependency="CommandNotification"
VaryByParam="none" %>

Listing 2: On a datasource control

<asp:SqlDataSource EnableCaching="true" SqlCacheDependency="CommandNotification"
CacheDuration="Infinite" />

Call System.Data.SqlClient.SqlDependency.Start() & System.Data.SqlClient.SqlDependency.Stop() methods somewhere in the application before the first SQL query is executed. You could ideally add these methods in Application_Start() & Application_End() events in global.asax file. These methods require a connection string as its parameter. SqlDependency.Start() starts a listener for receiving dependency change notifications from the instance of SQL Server specified by the connection string and SqlDependency.Stop() stops listening for change notifications. You can read the connection string from a web.config or code it per your requirement. The line of code below reads the second connection string from the local web.config.

Listing 3

System.Data.SqlClient.SqlDependency.Start(
ConfigurationManager.ConnectionStrings[1].ConnectionString.ToString());
System.Data.SqlClient.SqlDependency.Stop(
ConfigurationManager.ConnectionStrings[1].ConnectionString.ToString());

Note: ConfigurationManager.ConnectionStrings[n] is zero based and allows reading the desired connection string. You could also use the connection string name, which is recommended to make the code more readable. Eg:

ConfigurationManager.ConnectionStrings[“MyConnectionString”].ConnectionString

Whenever a command is issued to SQL Server, ASP.NET and ADO.NET will automatically create a cache dependency that listens to change notifications sent from the SQL Server. As data is changed in SQL Server, these notifications will cause the cached queries to be invalidated on the web server. The next time a page or data source control associated with the dependency is requested, the page or data source control will be executed again as opposed to serving cached information.

Example 1: In the first example I used I have a simple ASPX page with a SqlDataSource control and a GridView control that is bound to the data source. I am caching the page using the output cache parameter as described above and then also adding Response.Write(“Last Updated: ” + DateTime.Now.ToString())  in the Page_Load event so that I can see the time when the data was last updated. I also have a Global.asax page that has the SqlDependency.Start and SqlDependency.Stop methods.

It is pretty easy to see this in action. When you run the page, you should see the Last Update value remain static as long as the underlying data changes. If you manually use SQL Server manager to change the underlying data, then the page should be updated. You could also use SQLProfiler to see what SQL Server is doing.

Example 2: In the second example, I have an ASPX page without the OutputCache directive. I then set the caching options for the SqlDataSource control to cache only the underlying data and not the entire page. I have then configured the following properties for the SqlDataSource control and then bind the GridView to this data source control.

  • SelectCommand
  • CacheDuration
  • EnableCaching
  • SqlCacheDependency

Listing 4

<asp:SqlDataSource  ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:ASPNETDemoConnectionString %>"
SelectCommand="SELECT ID, Name FROM dbo.Customers"
CacheDuration="Infinite" EnableCaching="True"
SqlCacheDependency="CommandNotification">
</asp:SqlDataSource>

In both examples I also have the Global.asax page that has the SqlDependency.Start and SqlDependency.Stop methods. You may use the SQL Profiler with a custom tracing template (Just some basic logging enabled to illustrate the point) to see the notification that is sent to the application which then invalidates the DataSet cache. I have used the ClientProcessID column in SQL profiler to map it to the web application that is setting up the subscription. The w3wp.exe (in task manager) process hosts the web application. Also, check the account that is making the request. Depending on how your application is configured (impersonation enabled/disabled or using web service calls with specific credentials), you will need to use the appropriate account in the GRANT statements discussed earlier to allow subscription and notification for queries.

Common issues that prevent queries from working with SQL Server 2005 query notifications

If it appears that data is not being cached, and instead is being executed on every page request, it is likely that either the query does not follow the constraints required by SQL Server for notification or that SQL Server generated an error when attempting to setup notifications for that query. As far as I know, if an error condition occurs when attempting to setup a cache dependency in ASP.NET, it will silently fail with the end result being that the cache dependency is always invalid and any associated queries are always executed on each page request.

There are several restrictions on the syntax of queries that can be used in query notifications. It is documented in SQL Server books online and also included below:

  • Queries must explicitly include column names in the SELECT statement. Using “SELECT *” results in a query that will not be registered with SQL Server query notifications.
  • Table names in queries must include the ownername. Example: A query definition against the authors table in the pubs database, the query must reference the table as “dbo.authors.”
  • All tables referenced in the statement must be in the same database.
  • The statement may not use unnamed columns or duplicate column names.
  • The statement must not reference tables with computed columns.
  • The projected columns in the SELECT statement may not contain aggregate expressions unless the statement uses a GROUP BY expression. When a GROUP BY expression is provided, the select list may contain the aggregate functions COUNT_BIG() or SUM(). However, SUM() may not be specified for a nullable column. The statement may not specify HAVING, CUBE, or ROLLUP.
  • A projected column in the SELECT statement that is used as a simple expression must not appear more than once.
  • The statement must not include PIVOT or UNPIVOT operators.
  • The statement must not include the INTERSECT or EXCEPT operators.
  • The statement must not reference a view.
  • The statement must not contain any of the following: DISTINCT, COMPUTE or COMPUTE BY, or INTO.
  • The statement must not reference server global variables (@@variable_name).
  • The statement must not reference derived tables, temporary tables, or table variables.
  • The statement must not reference tables or views from other databases or servers.
  • The statement must not contain subqueries, outer joins, or self-joins.
  • The statement must not reference the large object types: text, ntext, and image.
  • The statement must not use the CONTAINS or FREETEXT full-text predicates.
  • The statement must not use rowset functions, including OPENROWSET and OPENQUERY.
  • The statement must not use any of the following aggregate functions: AVG, COUNT(*), MAX, MIN, STDEV, STDEVP, VAR, or VARP.
  • The statement must not use any nondeterministic functions, including ranking and windowing functions.
  • The statement must not contain user-defined aggregates.
  • The statement must not reference system tables or views, including catalog views and dynamic management views.
  • The statement must not include FOR BROWSE information.
  • The statement must not reference a queue.
  • The statement must not contain conditional statements that cannot change and cannot return results (for example, WHERE 1=0).
  • The statement can not specify READPAST locking hint.
  • The statement must not reference any Service Broker QUEUE.
  • The statement must not reference synonyms.
  • The statement must not have comparison or expression based on double/real data types.

There is a bug within the .net Framework that sometimes causes notifications to be lost. It is described in the following knowledge base article: Some notifications might not be received when an application subscribes to query notifications by using ADO.NET 2.0

Polling-based Invalidation

Polling based invalidation technique involves checking at frequent intervals if the data was updated. This method may cause stale data to be served from the cache if the underlying data changed between the polling intervals. The longer the polling interval, the longer the time it takes to pickup the changes and invalidates the cache. With this method, you will also need to use aspnet_regsql.exe command to enable notifications for the database/tables.

Following are the steps to use SqlDependency with this technique

1)      Enable notifications for the database using the aspnet_regsql.exe tool. The syntax is provided below.  aspnet_regsql.exe -S <Server> -U <Username> -P <Password> -ed -d Northwind -et -t Employees If you want to look up what these switches mean, here is the link to MSDN. Example: aspnet_regsql.exe -S “MachineName\InstanceName” -E -d “MyDatabase” -ed NOTE: This only needs to be done once for each database.

2)      Enable notifications for the table(s). aspnet_regsql.exe -S “MachineName\InstanceName” -E -d “MyDatabase” -et -t “MyTable”

3)      Configure polling in web.config file of the application.

Listing 5

<system.web>
     <caching>
       <sqlCacheDependency enabled="true" pollTime="1000" >
         <databases>
           <add name="FriendlyDatabaseName"
              connectionStringName="ConnectionStringNameToUse" />
         </databases>
       </sqlCacheDependency>
     </caching>
 </system.web>

The poll time specifies how often (in milliseconds) the application checks to see whether the data has changed.

4) Configure SQL dependency using the OutputCache directive below or for the DataSource control:

On an ASPX page:

Listing 6

<%@ OutputCache Duration="999999" SqlDependency="DatabaseName:TableName"
VaryByParam="none" %>

On a datasource control:

<asp:SqlDataSource EnableCaching="true" CacheDuration="Infinite"
SqlCacheDependency="DatabaseName:TableName" />

Conclusion

That is pretty much it! I hope this article was helpful in getting SQL notification services running for your applications. Please feel free to send me your feedback.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: