Wednesday, January 30, 2008

Every Once-In-A-While, Connectivity Amazes Me

My clothes washing machine broke down last week, and we needed someone to come out and take a look.  The Sears guy pulled up in his van, which had a dish antenna on top.  He grabbed his Tablet PC out of the passenger side of his van, and came inside.

After coming inside, I showed him to the machine, and he punched the model number into his computer.  He then looked at the washer, diagnosed the issue, punched the parts he needed into the computer, whipped out a printer, and then handed me a work order.

Took about 5 minutes.

Tuesday, January 29, 2008

Explaining The .NET 3.5 and Visual Studio 2008 Upgrade to Functional and QA Folks

Recently, I had to explain the impact of the .NET 3.5 Framework and Visual Studio 2008 upgrade for our business analysts and quality assurance teams.  It was a little bit of a difficult sell, because our last upgrade, from .NET 1.1 to .NET 2.0 was a bit of an effort, so I needed to quell some fears of the functional folks, and also document the changes for the QA folks. 

I thought I'd share, in case you need to make the same case at your place of work.  It's pretty simple, but it might save you some time.

Definitions

In order to understand the impact of this task, it is important to define the components of this Visual Studio.NET 2008 and the .NET 3.5 Framework.

C# Language – C# is the language of choice. It is a general-purpose programming language developed by Microsoft, targeting the .NET Framework.

C# Compiler – The C# compiler parses the C# language and analyzes, optimizes and translates it into Common Intermediate Language (CIL).

Common Intermediate Language (CIL) – Common Intermediate Language is a transitional language that is platform and processor independent, allowing it to run on any machine that has the .NET Runtime installed. All .NET language implementations (C#, VB.NET, Managed C++, etc.) are compiled to this intermediate language.

.NET Runtime – The .NET Runtime executes the program. It converts the CIL into platform/processor specific byte-code and subsequently into operations that the CPU can execute.

.NET Framework – The .NET Framework is a software component that can be installed on the Microsoft Windows operating system. It is a library of pre-coded solutions to common program requirements. Examples of these solutions are accessing files and folders and using the computer’s network connection to get information from a resource on the Internet.

Integrated Development Environment (IDE) - An IDE is a software application that provides facilities to developers for the process of software development. An IDE consists of a source code editor, a compiler, and a debugger. Visual Studio.NET 2008 is an IDE that specifically targets the .NET 2.0 Runtime, the .NET 3.5 Framework, and the C# 3.0 language.

Which of These Components Are Changing With This Migration?

The following table will help visualize what is changing as a result of this migration:

Component

Prior to the Upgrade

After the Upgrade

Environment Component Applies To

C# Language

C# 2.0

C# 3.0

Development

C# Compiler

C# 2.0 Compiler

C# 3.0 Compiler

Development

Common Intermediate Language

.NET 2.0 CIL

No Change

Development, Production

.NET Runtime

.NET 2.0 Runtime

No Change

Development, Production

.NET Framework

.NET 2.0, .NET 3.0

.NET 2.0, .NET 3.0, .NET 3.5

Development, Production

Integrated Development Environment

VS.NET 2005

VS.NET 2008

Development

 

Hope this helps!

Friday, January 25, 2008

SQL Server 2005 Database Cache Invalidation Series Links

Here is a digest of the series of posts I made on SQL Server 2005 Cache Invalidation:

Part 1 OutputCache's SqlDependency and SqlDataSource's SqlCacheDependency

This post covers the two most simple options for using cache invalidation with ASP.NET.

Part 2 SqlCacheDependency

Shows how to create a dependency on a database table for your System.Web.Caching.Cache item.

Part 3 SqlDependency

Reviews an implementation that allows you to wire an event handler to handle the change notifications on your own.

Part 4 SqlNotificationRequest

Covers the lower level framework implementation of how SQL notifications work.  Provides essentially the same functionality as SqlDependency, but is a good intro to SQL Server Service Broker programming.

Part 5 Roll Your Own

This post covers how to use SQL Notifications to invalidate cache on a granular (row by row or even column by column) level.  If you've dismissed database cache invalidation in the past because your cache strategy is too complicated, you should check this out and see if it works for you.

Overview of SQL Server 2005 Database Cache Invalidation Options - Part 5 Roll Your Own

 

This is the last in my series of posts about different options you have with SQL Server 2005 database cache invalidation.

Up until now, we've seen 4 different options for receiving notifications in our web or Windows application that data has changed.  Each of the options there, had one limitation, in that you can only receive notifications if a set of data has changed. 

So, what if you've got a more granular caching system.  One that caches individual entities, as opposed to collections of entities?  Up until this post, none of the options would provide you the ability to receive a notification on a single row changing.

I'm going to cover how to do this in this post, by showing you how to roll your own notification messages using SQL Server Service Broker.

Setup

For each of these options, you'll need to ensure that you've enabled the SQL Server Broker on your database.  To do this, you can execute the following query (this query needs exclusive access to the database, so if you see it hanging for a while, chances are you'll need to open up SQL Server Activity Monitor and terminate any additional connections.  IISRESET works well too):

ALTER DATABASE AdventureWorks SET NEW_BROKER

Note that you'll need to do this whenever you restore a database.  Additionally, I've seen the following issue with database notifications occur when restoring a database that was backed up on another instance of SQL server.

UPDATE: Garry Grimshaw also pointed out to me that the version of SQL Server 2005 that you have installed makes a difference.  Certain versions of SQL Server 2005 don't include Notifications Services, and your app will immediately never notify you of cache changes (or if using SQLCacheDependency, an immediate invalidation of your cache item will occur.)

UPDATE #2: Updated post to include the issues that I've discovered after using this code for some time.  See this post about CPU and tempdb issues with SQL Service Broker.

Roll Your Own Cache Notification

Here is the list of tasks I'm going to be doing to get this to work:

  1. Create a SQL Server Service Broker Service and Queue
  2. Create a trigger on the table I'm interested in, that loops through the changed rows, and creates custom XML message with the identifying attributes of the row that has changed, and inserts that XML message into the Service Broker Queue.
  3. Write a background service in my ASP.NET application that listens for new entries in that Queue.

The first thing we have to do is create our SQL Service Broker Queue and Service.  These will be the structures for the queuing of notifications.  It's the same as in the SqlNotificationRequest example.

CREATE QUEUE PersonChangeMessages;
 
CREATE SERVICE PersonChangeNotifications
  ON QUEUE PersonChangeMessages
([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);

The result of this will be the following objects in SQL Server:

screen shot of Service Broker objects

The next thing we need to do is create our trigger on the table that we need to be notified of changes for, which will look like so:

CREATE TRIGGER [dbo].[tr_Person_NotifyCacheQueue]
   ON [dbo].[Person]
   AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
 
    -- Insert statements for trigger here
    DECLARE @Message XML
    DECLARE @Action VARCHAR(10)
    DECLARE @InsTblCnt INT
    DECLARE @DelTblCnt INT;
    SET @Action = '';
 
    SET @InsTblCnt = 0;
    SET @DelTblCnt = 0;
 
    SELECT @InsTblCnt = Count(*) FROM Inserted;
    SELECT @DelTblCnt = Count(*) FROM Deleted;
 
    IF @InsTblCnt > 0 AND @DelTblCnt > 0 SET @Action = 'UPDATE';
 
    ELSE IF @DelTblCnt > 0 SET @Action = 'DELETE';
 
    ELSE IF @InsTblCnt > 0 SET @Action = 'INSERT';
 
    ELSE SET @Action = 'NONE';
 
    IF @Action <> 'NONE'
        BEGIN
            DECLARE @tempTable TABLE ( [ID] INT )
 
            IF @Action = 'DELETE'
                SET @Message = (SELECT * FROM Deleted FOR XML AUTO, ROOT ('ChangedPersonRows'))
            ELSE
                SET @Message = (SELECT * FROM Inserted FOR XML AUTO, ROOT ('ChangedPersonRows'))
 
            DECLARE @DialogHandle UNIQUEIDENTIFIER
 
            BEGIN DIALOG @DialogHandle
            FROM SERVICE [PersonChangeNotifications]
            TO SERVICE 'PersonChangeNotifications'
            ON CONTRACT [http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
            WITH ENCRYPTION = OFF, LIFETIME = 30;
            SEND ON CONVERSATION @DialogHandle
            MESSAGE TYPE [http://schemas.microsoft.com/SQL/Notifications/QueryNotification] (@Message);
        End
 
END

The trigger loops through all the changes, and builds an Xml document out of it, then sends that Xml document to the SQL Server Service Broker Queue.

Just as before, we need to create a service in our application that listens to the queue for changes.  It's really similar to before, but in this case, we're getting the information from the queue message to construct our cache key.

public class Listener
{
    private static readonly string cacheKey = "Person:{0}";
    private static bool isAlreadyListening = false;
 
    public void Start()
    {
        if (isAlreadyListening) return;
 
        //create and call the delegate for the asynchronous call
        AsyncListenerDelegate asyncListener = new AsyncListenerDelegate(Listen);
        asyncListener.BeginInvoke(HttpContext.Current, new AsyncCallback(StopListening), null);
        isAlreadyListening = true;
        return;
    }
 
    private void Listen(HttpContext currentContext)
    {
        while (true)
        {
            //create the command that will listen to the queue
            using (SqlConnection connection = new SqlConnection(
                ConfigurationManager.ConnectionStrings["BlogExampleDBConnectionString"].ConnectionString))
            {
                connection.Open();
 
                SqlCommand command = connection.CreateCommand();
                command.CommandText = @"DECLARE @conversationHandle uniqueidentifier;
                        DECLARE @message AS XML;
                        WAITFOR (RECEIVE @conversationHandle = [conversation_handle],
                            @message = CAST(message_body AS XML) FROM PersonChangeMessages);
                            END CONVERSATION @conversationHandle;
                            SELECT @message;";
                command.CommandTimeout = 60 * 5; //listen in 5 minute increments
 
                try
                {
                    SqlDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        SqlXml sqlXml = reader.GetSqlXml(0);
                        XmlReader xmlReader = sqlXml.CreateReader();
                        while (xmlReader.Read())
                        {
                            while (xmlReader.MoveToAttribute("LastName"))
                            {
                                currentContext.Cache.Remove(string.Format(cacheKey, xmlReader.Value));
                                break;
                            }
                        }
                    }
                }
                catch (SqlException)
                {
                    //if the query times out, that means that no messages have
                    //been sent to the queue yet, so we should keep listening.
                }
            }
        }
    }
 
    private void StopListening(IAsyncResult result)
    {
        //no information needed here.
    }
}

And finally, here's my service that retrieves the person:

public static Person GetPerson(string lastName)
{
    string cacheKey = string.Format("Person:{0}", lastName);
 
    Person person = (Person)HttpContext.Current.Cache.Get(cacheKey);
 
    if (person == null)
    {
        DataClasses1DataContext ctx = new DataClasses1DataContext();
        var query = from p in ctx.Persons
                    where p.LastName == lastName
                    select p;
 
        person = query.Single();
 
        Listener listener = new Listener();
        listener.Start();
 
        HttpContext.Current.Cache.Insert(cacheKey, person);
    }
    return person;
}
You can see here that I'm interested in more granular caching information.  I'm caching by individual person id's, and I don't want to dump the cache of all individual people when the person table changes.  I want to clear the cache for individual items, and this method allows me to do it.

Tuesday, January 22, 2008

What's Going On With Palm?

I've been a big fan of the Palm OS since I was lucky enough to score a Palm V when I first started at my current employer.  After all, in 1999 they were giving them out like crazy.  The OS is utilitarian, it's easy to use, and it just works.  Lately though, I'm starting to have doubts on how much longer this OS is going to be around. 

Palm (or Access, not sure) finally released a version of the Palm Desktop software that works with Vista.  Roughly 14 months after the release of Vista.  On top of that, they've got this list of disclaimers on the download page:

Palm software disclaimer 1

And this as well:

Palm software disclaimer 2

Definitely not encouraging stuff.  There's more disclaimers than there are features.

Vista Service Pack 1 Installation Experience - Why Can't I Accept The License at the Beginning?

A few weeks ago, I went ahead and installed the RC of Vista Service Pack 1.  Why, you ask?  Because to me, software is like free hardware.  Since Service Pack 2 of XP, Microsoft has distributed it's Service Packs via a small initial download that contains just enough to execute a scan of your system to figure out what components you'll need for the update.  After building a list of which components you need, it downloads what you need locally, then executes that list of updates. 

Ultimately, this is a step forward in the installation experience for the user, and as the Windows operating systems start packing more and more functionality in, I can understand that all service packs will be getting bigger and bigger.

What bothers me is the license agreement acceptance is halfway through the install.  You can't just kick it off before you go to bed, you've got to kick it off, wait for the half hour scan, wait for the half hour download, and then accept the license agreement.  Lame.

Is there a reason that this can't be at the beginning of the install?  Does the license agreement change, depending on which components it needs to download and install?

I noticed that Service Pack 1 for Visual Studio 2005 was the same way, except it would ask you if you wanted to install 15 minutes after you double clicked the executable.

I'm pretty surprised that someone over there in Redmond hasn't barked about this issue.  Seems like it would be something that would bother a lot of people.

Saturday, January 19, 2008

Overview of SQL Server 2005 Database Cache Invalidation Options - Part 4 SqlNotificationRequest

In my previous posts, I reviewed three basic implementation of the Sql 2005 notifications for cache invalidation.  In this post, I'm going to review the SqlNotificationRequest class.

Setup

For each of these options, you'll need to ensure that you've enabled the SQL Server Broker on your database.  To do this, you can execute the following query (this query needs exclusive access to the database, so if you see it hanging for a while, chances are you'll need to open up SQL Server Activity Monitor and terminate any additional connections.  IISRESET works well too):

ALTER DATABASE AdventureWorks SET NEW_BROKER

Note that you'll need to do this whenever you restore a database.  Additionally, I've seen the following issue with database notifications occur when restoring a database that was backed up on another instance of SQL server.

UPDATE: Garry Grimshaw also pointed out to me that the version of SQL Server 2005 that you have installed makes a difference.  Certain versions of SQL Server 2005 don't include Notifications Services, and your app will immediately never notify you of cache changes (or if using SQLCacheDependency, an immediate invalidation of your cache item will occur.)

SqlNotificationRequest

The SqlNotificationRequest object is the lowest level class you can use for SQL Server 2005 notifications in the .NET Framework.  If you open up Reflector, you'll see that each of the options I outlined in previous posts use this class.  You can attach a SqlNotificationRequest to a SqlCommand that tells the SQL Server Service Broker that any changes to the results of this command should be sent to a Service Broker Queue.

Here is the high level outline of what is going on in the example below:

  1. User requests data for the first time, and it does not exist in cache.
  2. We create the SqlCommand and attach a SqlNotificationRequest to it.
  3. We then start a background thread that is listening to the Service Broker Queue
  4. We put the results of the command in the cache.
  5. When an item is received from the Service Broker Queue, we clear the cache.

The first thing we need to do is create the SQL Server Service Broker Service and Queue entities.  This is something that the SqlCacheDependency and SqlDependency objects did for us.

CREATE QUEUE PersonChangeMessages;
 
CREATE SERVICE PersonChangeNotifications
  ON QUEUE PersonChangeMessages
([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);

The result of this will be a service for monitoring changes, and a queue for changes, both of these in SQL Server:

service broker screen shot

The next thing we need to do is create our listener.  The listener will have a method that is called asynchronously.  This method will execute a query that listens to the message queue for new messages.  If it receives a message, it will process that message, and then start listening again.

public delegate void AsyncListenerDelegate(HttpContext currentContext);
 
public class Listener
{
    private static readonly string cacheKey = "personsCacheKey";
    private static bool isAlreadyListening = false;
 
    public void Start()
    {
        if (isAlreadyListening) return;
 
        //create and call the delegate for the asynchronous call
        AsyncListenerDelegate asyncListener = new AsyncListenerDelegate(Listen);
        asyncListener.BeginInvoke(HttpContext.Current, new AsyncCallback(StopListening), null);
        isAlreadyListening = true;
        return;
    }
 
    private void Listen(HttpContext currentContext)
    {
        while (true)
        {
            //create the command that will listen to the queue
            using (SqlConnection connection = new SqlConnection(
                ConfigurationManager.ConnectionStrings["BlogExampleDBConnectionString"].ConnectionString))
            {
                connection.Open();
 
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "WAITFOR (RECEIVE * FROM PersonChangeMessages);";
                command.CommandTimeout = 60 * 5; //listen in 5 minute increments
 
                try
                {
                    SqlDataReader reader = command.ExecuteReader();
                    if (reader.HasRows)
                        currentContext.Cache.Remove(cacheKey);
                }
                catch (SqlException)
                {
                    //if the query times out, that means that no messages have
                    //been sent to the queue yet, so we should keep listening.
                }
            }
        }
    }
 
    private void StopListening(IAsyncResult result)
    {
        //no information needed here.
    }
}

Now that we have our listener, we can update our Person getting method to add the SqlNotificationRequest to the command.  Note that in my previous examples I used Linq to SQL, however in this one, I am using plain old ADO.NET.  I was unable to use the SqlNotificationRequest with the DataContext object.  Maybe someone out there has figured that one out.  If so, let me know. 

Also note that we still need to be using a valid query for notification.

public static List<Person> GetPeople()
{
    string cacheKey = "personsCacheKey";
 
    List<Person> people = (List<Person>)HttpContext.Current.Cache.Get(cacheKey);
 
    if (people == null)
    {
        people = new List<Person>();
 
        using (SqlConnection connection = new SqlConnection(
            ConfigurationManager.ConnectionStrings["BlogExampleDBConnectionString"].ConnectionString))
        {
            connection.Open();
 
            SqlCommand command = connection.CreateCommand();
            command.CommandText = "SELECT PersonID, FirstName, LastName FROM dbo.Person;";
 
            SqlNotificationRequest notificationRequest = new SqlNotificationRequest();
            notificationRequest.UserData = Guid.NewGuid().ToString(); //uniquely identifies notification
            notificationRequest.Options = "Service=PersonChangeNotifications"; //specifies service to use
            notificationRequest.Timeout = 60 * 4;  //make sure this is less than your listener timeout
 
            command.Notification = notificationRequest;
 
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                people.Add(new Person() {
                    PersonID = reader.GetGuid(0), FirstName = reader.GetString(1), LastName = reader.GetString(2)
                });
            }
        }
 
        Listener listener = new Listener();
        listener.Start();
 
        HttpContext.Current.Cache.Insert(cacheKey, people);
    }
    return people;
}

As you can see, this method is a bit more involved, but from here, you start getting even more of an idea of what is going on with interaction between the SQL Server Service Broker and the ASP.NET runtime, especially in light of the previous methods I discussed in earlier posts.

I've got one more to go in this series of posts, and it covers a roll-your-own approach that provides ultimate flexibility in you cache notifications.

Tuesday, January 15, 2008

The Four Phases of the 20% Programmer

There's been a bit of meme about the 80%/20% rule around programmers.  I actually firmly believe that this phenomenon exists, and I'm lucky enough to work in an organization which consists of 20%'ers. 

With the opportunity to work with people closely for a long period of time, you begin to notice patterns, and if you take the time to think about them, they help you realize some things about yourself.  One thing I've noticed is that there's a pattern that is common among the  20%'ers as they grow and develop as programmers.

Now, none of this is a bad thing.  What we should all realize, despite the fact that at some of these phases the outcomes appear negative, is that I'm talking about extremely good programmers to begin with.  The negative outcomes I describe are relative, and each are extremely great learning experiences that each great programmer goes through on their path to software development Zen.

Phase 1 - "Are You Sure I Should Check This In?"

This is your first day on the job, where you first see this mountain of code, and folks are throwing around acronyms and technologies that you've never heard of.  Source control?  Unit Testing?  Continuous Integration?  Coding Standards?  These things are unheard of in undergrad or even grad school in most cases, so you're likely dealing with this all for the first time.  However, you can probably whip up a quick sort in no time.

So, you get your first production ready task, probably fixing some low-touch bug, and you pour over the changes for hours.  Something that normally takes a couple minutes may take you a couple hours because you pseudocoded the two line change, and then mentally executed the fix, over and over, and over.

Then it comes time to commit the change, in which you update your source, recompile, check your change again, then finally commit and stare at the continuous build until it completes.  Crossing your fingers that you're not going to be the target of non-stop "You broke the build newbee" ridicule.

Ultimately this is a good phase to be in, sans the fact that it took you three hours to correct a spelling error on a web page.

Phase 2 - Danger is My Middle Name

Otherwise known as cowboy programming, this is where a great programmer does the most damage.  By now you've figured everything out.  You're familiar with the code base, the technology and the processes.  On top of that, you've gotten pretty efficient at finding your the bugs and fixing them.  You've probably got some ownership of a piece of functionality, something you've developed from the ground up, so you know it inside and out.

This is where you get dangerous, and start feeling invincible.  You get the attitude that your code is bug free, impenetrable to any security threats, and deployment will be a breeze.  Additionally, you're willing to get started right away on some functionality that hasn't been thought through or communicated completely, but that's ok, you're just so excited at how efficient of a programmer you've become, you can't wait to hear the business analyst say "You're done already?"

This is also the phase at which you see the most pattern abuse.  At this stage you're just learning patterns, and everything starts to look like a visitor, an observer or a decorator.

Likely what happens at this phase is that your code causes many bugs, your client's security audit identifies a dozen XSS vulnerabilities, and you end up being responsible for an additional QA build because you forgot to include all your resource files in source control.

This is always where most folks start to learn to estimate, but often do their worst estimating.  The cowboy attitude applies to estimating here too, and and you're so quick to deliver a number, that your estimates can be off by 300%.

We've all gone through a cowboy programming phase, where our world is about clearing our plate as quickly as possible, and not about making sure what we've done is done well.  And once you do make these mistakes, you never, ever want to make them again, and that leads to the next phase, which is at the other end of the spectrum.

Phase 3- Hell Hath No Fury Like a Programmer Scorned

Once you've been burned one too many times by cowboy programming, you come almost full circle back to phase 1, where you triple check your work, hesitate to take on anything too risky, and heavily pad your estimates to account for any issues.

Usually when you're at this phase you go overboard.  You start saying no to requests, without real good reason, other than the fact that you want to insulate yourself from any risk.  If the request comes through anyway, you try to over-estimate your way out of the request, figuring that if it costs too much, then the person requesting it will nix it and save you the risk.

You might also find yourself being a rigid stickler to waterfall processes, requiring final documentation before estimating or writing a drop of code, ensuring that you've got every last nook and cranny identified prior to providing an estimate that you'll be accountable for.

Ultimately I feel that this behavior is the result of the fact that you still don't know exactly everything that is involved in delivering software yet, and that uncertainty causes you to insulate everything from risk blindly.

Phase 4 - "Neal Naysayer, meet Peter Pragmatist"

This is the phase that the 20%'er comes into their prime.  The balance between the cowboy programmer and the super-conservative engineer is struck and you start to settle into your stride.  You can deliver quality solutions in an efficient manner.  You've mastered how to use source control, become proficient at refactoring, and you begin to pay attention to the continous build again.

At this phase you've honed in on exactly what steps are needed to deliver high quality work on time.  You know what's involved with the tasks that you need to complete.  You've thought of everything, broken it down logically, thought about the impact, planned for interruptions, and refactoring.

Instead of immediately saying "No" to the business analyst, you start providing pragmatic options that balance functionality and the limited resources of the project.  You understand the information you need to collect to glean then things that have an impact on a projects or tasks success.

Ultimately this is where you want to be as a programmer.  It's certainly where I want to be.  It's hard not to revisit the other phases from time to time though, especially phase 2 and 3.  You feel pressures from different directions, and it's hard to keep your head about it.

The master programmers aren't phased by these pressures, and have faith in the fact that their pragmatic approach to things will serve them best in all situations.  That is where I want to be.

Have you noticed any patterns of your own?  Are there phases that I'm missing here?  Am I generalizing too much?  Does this apply to everyone, and not just the 20%'ers?

I'd love your feeback.

Saturday, January 12, 2008

Overview of SQL Server 2005 Database Cache Invalidation Options - Part 3 SqlDependency

In my previous two posts, I reviewed a basic implementation of the Sql 2005 notifications for cache invalidation.  In this post, I'm going to go over the SqlDependency class and how to use with SQL Server 2005 notifications services.

Setup

For each of these options, you'll need to ensure that you've enabled the SQL Server Broker on your database.  To do this, you can execute the following query (this query needs exclusive access to the database, so if you see it hanging for a while, chances are you'll need to open up SQL Server Activity Monitor and terminate any additional connections.  IISRESET works well too):

ALTER DATABASE AdventureWorks SET NEW_BROKER

Note that you'll need to do this whenever you restore a database.  Additionally, I've seen the following issue with database notifications occur when restoring a database that was backed up on another instance of SQL server.

UPDATE: Garry Grimshaw also pointed out to me that the version of SQL Server 2005 that you have installed makes a difference.  Certain versions of SQL Server 2005 don't include Notifications Services, and your app will immediately never notify you of cache changes (or if using SQLCacheDependency, an immediate invalidation of your cache item will occur.)

SqlDependency

The SqlDependency class is a bit more flexible than the other two examples that I gave an overview for in my previous posts, as it is a lowe level class than the SqlCacheDependency and the ASP.NET page output caching.  This option has no dependency on the ASP.NET framework, an can be used with or without an HttpContext (which in turn means you can use it with Windows Forms application or even a console application).

What the SqlDependency class allows you to do is register an event that gets fired when a SqlCommand's result changes.  Again, the query needs to be a valid notification query (just as in the previous posts).

Below is the example:

public class PersonService
{
    private static List<Person> _people = null;
 
    public static List<Person> GetPeople()
    {
        if (_people == null)
        {
            DataClasses1DataContext ctx = new DataClasses1DataContext();
            var query = from p in ctx.Persons select p;
 
            SqlDependency dependency = new SqlDependency(ctx.GetCommand(query) as SqlCommand);
            SqlDependency.Start(ctx.Connection.ConnectionString);
            dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
 
            _people = query.ToList();
        }
        return _people;
    }
 
    static void dependency_OnChange(object sender, SqlNotificationEventArgs e)
    {
        if (e.Type == SqlNotificationType.Change)
            _people = null;
    }
}

You see that my operation that retrieves the collection, looks to see if the collection exists in a static class-scoped variable.  If it does not, then I load up the collection, store it in the static variable, and wire up the event to handle the notifications coming from SQL Server.

Note that there's a couple different notification types for the notification event that is handled.  The other two options in the SqlNotificationType enumeration are "Subscribe" and "Unknown".

You can see how some the higher level classes discussed in my previous posts work after looking at the SqlDependency class in action.

Now, I mentioned being able to use this in Windows Forms applications, but this may not be the best option for cache invalidation in this case.  Having many, many clients listening for Sql Server 2005 notification is a very bad idea.  You'll quickly end up running out of available connections, and even before that, experiencing some  serious network and database performance issues.

Friday, January 11, 2008

Overview of SQL Server 2005 Database Cache Invalidation Options - Part 2 SqlCacheDependency

In my previous post, I reviewed a basic implementation of the Sql 2005 notifications for cache invalidation.  In this post, I'm going to go over the SqlCacheDependency class and how to use with SQL Server 2005 notifications services.

Setup

For each of these options, you'll need to ensure that you've enabled the SQL Server Broker on your database.  To do this, you can execute the following query (this query needs exclusive access to the database, so if you see it hanging for a while, chances are you'll need to open up SQL Server Activity Monitor and terminate any additional connections.  IISRESET works well too):

ALTER DATABASE AdventureWorks SET NEW_BROKER

Note that you'll need to do this whenever you restore a database.  Additionally, I've seen the following issue with database notifications occur when restoring a database that was backed up on another instance of SQL server.

The next thing you'll have to do is update the Global.asax to setup the SqlDependency in the Application_Start method.  You do this with the following code:

protected void Application_Start(object sender, EventArgs e)
{
    SqlDependency.Start(ConfigurationManager.ConnectionStrings["BlogExampleDBConnectionString"].ConnectionString);
}

When you call this method, the runtime sets up the relationship between the application domain and the connection string that will be used later during the notification registration process.

UPDATE: Garry Grimshaw also pointed out to me that the version of SQL Server 2005 that you have installed makes a difference.  Certain versions of SQL Server 2005 don't include Notifications Services, and your app will immediately never notify you of cache changes (or if using SQLCacheDependency, an immediate invalidation of your cache item will occur.)

SqlCacheDependency

SqlCacheDependency is an implementation of the System.Web.Caching.CacheDependency class, and can be used as an invalidation mechanism.  It registers a SqlCommand or a table for change notification and clears the associated cache items when it is notified. 

If you don't need a granular query to notify you of when a change occurs, and you are already using the System.Web cache, then this option will probably work best for you.

Below is how to use the class, and you can see how simple it is.

public static List<Person> GetPeople()
{
    string cacheKey = "personsCacheKey";
 
    List<Person> people = (List<Person>) HttpContext.Current.Cache.Get(cacheKey);
    if (people == null)
    {
        DataClasses1DataContext ctx = new DataClasses1DataContext();
        var query = from p in ctx.Persons select p;
        people = query.ToList();
 
        SqlCacheDependency dependency = new SqlCacheDependency(ctx.GetCommand(query) as SqlCommand);
 
        HttpContext.Current.Cache.Add(cacheKey, people, dependency, DateTime.MaxValue,
                                              TimeSpan.Zero, CacheItemPriority.Normal, null);
    }
    return people;
}

Any changes that occur within the database will trigger a notification to this dependency class, and will automatically clear the cache for you.

Note how you can get the SqlCommand from the Linq query to wire to the SqlCacheDependency.  The Linq team really did a good job at covering their bases.  I have yet to hit an issue with this language that has prevented me from accomplishing something.  It's very smart.

Tuesday, January 08, 2008

Overview of SQL Server 2005 Database Cache Invalidation Options - Part 1 OutputCache's SqlDependency and SqlDataSource's SqlCacheDependency

One of the challenges  of a load balanced environment is cache invalidation across application boundaries.  If you've got an data-centric application that makes heavy use of the in-memory ASP.NET cache (or any other in memory cache for that matter), and also have decided to provide a reliable hosting solution to you clients by using load balancing, then eventually you'll face the challenge of how to invalidate the cache across application domain boundaries.

In most cases, the one thing that a farm of servers share is the the database, so using the database to trigger the cache to invalidate is a reliable option.  In SQL Server 2000, your only option for determining from ASP.NET when data has changed, is by polling.  With SQL Server 2005, the introduction of the Service Broker has provided the ability for SQL Server to notify your application.

For ASP.NET 2.0, you've got a few options for using the database to invalidate the cache.  Each of the options build upon each other, and as I explain them in this series of posts, they become more and more specific and thus more and more flexible.  For this series of posts, I am going to be focusing on using these caching features with SQL 2005 Notification.

Setup

For each of these options, you'll need to ensure that you've enabled the SQL Server Broker on your database.  To do this, you can execute the following query (this query needs exclusive access to the database, so if you see it hanging for a while, chances are you'll need to open up SQL Server Activity Monitor and terminate any additional connections.  IISRESET works well too):

ALTER DATABASE AdventureWorks SET NEW_BROKER

Note that you'll need to do this whenever you restore a database.  Additionally, I've seen the following issue with database notifications occur when restoring a database that was backed up on another instance of SQL server.

The next thing you'll have to do is update the Global.asax to setup the SqlDependency in the Application_Start method.  You do this with the following code:

protected void Application_Start(object sender, EventArgs e)
{
    SqlDependency.Start(ConfigurationManager.ConnectionStrings["BlogExampleDBConnectionString"].ConnectionString);
}

When you call this method, the runtime sets up the relationship between the application domain and the connection string that will be used later during the notification registration process.

UPDATE: Garry Grimshaw also pointed out to me that the version of SQL Server 2005 that you have installed makes a difference.  Certain versions of SQL Server 2005 don't include Notifications Services, and your app will immediately never notify you of cache changes (or if using SQLCacheDependency, an immediate invalidation of your cache item will occur.)

Output CachING

Once you've started the application listening for SQL Commands, if you specify "CommandNotification" for the value of the SqlDependency attribute within the OutputCache page directive, all SqlCommands that execute for the processing of this page will be registered for change notification in the SQL Server Service Broker. 

Note that there are limitations on the T-SQL you can execute to be successfully registered for notifications.  There is a SQL Server BOL article that goes over valid queries for SQL Notifications.

This is what the page directive ends up looking like:

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

And this is what the rest of the page looks like:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="sqlcachedependency._Default" %>
<%@ OutputCache SqlDependency="CommandNotification" Duration="3600" VaryByParam="none" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <%= System.DateTime.Now.ToString() %>
        <asp:GridView ID="myGridView" runat="server" DataKeyNames="PersonID" DataSourceID="myDataSource"
            AutoGenerateColumns="false">
            <Columns>
                <asp:BoundField HeaderText="First Name" DataField="FirstName" />
                <asp:BoundField HeaderText="Last Name" DataField="LastName" />
            </Columns>
        </asp:GridView>
    </div>
    <asp:SqlDataSource ID="myDataSource" runat="server" ConnectionString="<%$ConnectionStrings:BlogExampleDBConnectionString%>"
        SelectCommand="SELECT PersonID, FirstName, LastName FROM dbo.Person" SelectCommandType="Text" Sql />
    </form>
</body>
</html>

Note that ALL commands executed with valid SQL will cause the page to be cached.  Those include statements cached within the page's SQLDataSource controls, as well as any valid queries that are in the business objects used by the page.

SQL Server and ASP.NET manage this by creating a call context and the SqlCommand uses that context during the execution of the query to register the query for notification.

SqlDataSource

In addition to providing the ability to cache the entire page, you do have the option to cache commands for individual SqlDataSources as well.  Below is the same SqlDataSource as above, but with the additional two attributes EnableCaching and SqlCacheDependency.

<asp:SqlDataSource ID="myDataSource" runat="server" ConnectionString="<%$ConnectionStrings:BlogExampleDBConnectionString%>"
    SelectCommand="SELECT PersonID, FirstName, LastName FROM dbo.Person" SelectCommandType="Text"
    EnableCaching="true" SqlCacheDependency="CommandNotification" />

In this example, the wiring that ASP.NET needs to do is far easier.  It can use the commands provided to the control to wire the SQL 2005 notifications.

You can see there's quite a bit of power in just these two applications of SQL 2005 cache dependencies.  In the posts following this one, I'll go over the the SqlCacheDependency object, the SqlDependency object and also dive into SQL Server 2005 Service Broker programming.