Udostępnij za pośrednictwem

Updating AppFabric Cache via SQL Service Broker External Activator

  • Artykuł

 Event Driven Updates

If I can't have magic then I want something easy. I'd like to have changes that occur on certain tables be reflected in AppFabric Cache. I'd also like this to occur in an event based manner. "Use SqlDependency and be done with it" you say? That's certainly one answer but it also creates a coupling between your application and managing cache refreshes or it means you're deploying yet another domain specific service.

External Activator is an engine designed to invoke external code in response to an event in SQL Server. This is exactly what I want. In this specific case I am using it to update my cache data but it's not hard to imagine many use cases where this is useful and being able to do it with a simple executable rather than a full blown service has many implications in terms of xcopy deployment etc.

I want to be clear up front that this idea is in the investigatory stage. My results are encouraging but you should expect to do some analysis and tweaking if you attempt the technique for a production system.

Finally, before I jump in this example assumes you have AppFabric Cache installed and working and you have at least read about External Activator.  If you need more information about External Activator and service broker before starting see the SQL Service Broker Team Blog.

 

Creating the Service Broker Queues

The first step to getting going was to download the External Activator from the feature pack page and diligently follow all of the directions. Be sure to download the appropriate 64 bit or 32 bit msi for your platform.

Next, I created a database called CacheUpdateSample and made sure to enable Service Broker and grant the service account that the activator would be running under the necessary access.

Once that was done I issued the following commands:

use CacheUpdateSample

CREATE MESSAGE TYPE GenericXml VALIDATION = WELL_FORMED_XML

GO

CREATE CONTRACT GenericContract

(

      GenericXml SENT BY ANY

)

GO   

CREATE QUEUE MessageQueue

GO

CREATE QUEUE UpdateCacheQueue

GO

CREATE SERVICE MessageQueueService ON QUEUE MessageQueue (GenericContract)

GO

CREATE SERVICE UpdateCacheService

ON QUEUE UpdateCacheQueue

(

      [https://schemas.microsoft.com/SQL/Notifications/PostEventNotification]

);

GO

CREATE EVENT NOTIFICATION UpdateCacheNotification

ON QUEUE MessageQueue

FOR QUEUE_ACTIVATION

TO SERVICE 'UpdateCacheService' , 'current database'

GO

This set up the service broker infrastructure I needed. Now I had to figure out a way to get messages flowing.

 

Service Broker Shenanigans

The vehicle I chose to drive the updates within the database might be viewed as slightly unconventional:

create procedure [dbo].[uspSendGeneric]

(@xml xml)

as

begin

      DECLARE @dh UNIQUEIDENTIFIER;

      BEGIN DIALOG CONVERSATION @dh

      FROM SERVICE [MessageQueueService]

      TO SERVICE 'MessageQueueService','current database'

      ON CONTRACT GenericContract

      WITH ENCRYPTION = OFF;

      SEND ON CONVERSATION @dh MESSAGE TYPE GenericXml(@xml);

end

GO

If you look closely you’ll see that it's talking to itself!

I did this because it was easy to make the updates fire the way I wanted them to and it was easy to make sure I cleaned up my conversations. There is a lot of material out there on conversation patterns, serializing conversation handles, explaining why both sides should end the conversation and all kinds of things that are very interesting but I just wanted the thing to fire when I wanted it to and go away when I was done and this worked well in my testing.

Once I had my narcissistic procedure done I hooked it up to a simple trigger

create trigger [dbo].[sendGenericTrigger]

on [dbo].[TestTable] FOR Insert, Update

as

begin

      declare @xml xml;

      set @xml = (select * from inserted for xml auto,root('SSBData'),elements)

      exec uspSendGeneric @xml

end

GO

 

That Which Is Invoked

Once the database side is all hooked up it’s time to create something for the External Activator to activate. So, I quickly created a small application to drive the cache updates.

The heart of main looks like this:

using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings[dbKey].ConnectionString))

{

    con.Open();

    var clean = ProcessMessages(con);

    EndConversations(con, clean);

    con.Close();

}

The ProcessMessages function retrieves the messages , populates the cache. And returns the conversation handles. The EndConversation routine spins through the handles and closes them.

When you read the sample code that contains the body of the routines you'll see they are very simple for demo purposes. In production you *could* develop an elaborate dispatching system using dynamic assembly loading or various validation checks etc. The one thing to keep in mind however is to not affect the simplicity of the design. Prefer creating another event and executable that you map in the config file to a monolithic solution. By doing this you'll be more able to take advantage of the flexibilty of this aproach.

 Impressions

There were several things I liked about this technique:

  • It was faster than I expected even though External Activator is invoking the executable each time the event fires.
  • I was able to edit and recompile code without restarts/ file locking
  • Using the log file produced by external activator was easy.

One facet I will investigate further to see if there is a more elegant way to do things is having the queue talk to itself. While this makes conversation cleanup easy it does result in an extra invocation of the executable. Another might be a detailed measurment of cache misses/hits % using this method vs. deploying a dedicated service. 

Code for the sample can be found here.

 

 

Thanks to teammates Mark Simms, Emil Velinov, Jaime Alva Bravo and James Podgorski for their review

Comments

  • Anonymous
    January 18, 2011
    Great example, but after following the code sample to the letter I don't get any error messages but it just seems to sit there.  I've had a sweep through the permissions, both for the EA Service, as well as database side.  The EATrace.log file shows that the external activator service has started up correctly but that's it, no other entries.  The messages in the "MessageQueue" (after inserting a record in the test table and having the trigger fire) aren't going anywhere.. its as if the Event notifications aren't getting picked up.  Are there any additional logs I can look at to see if event notifications are being fired?  Just wonding why the External Activator service seems to be inert.  Any help/advice appreciated
  • Anonymous
    January 21, 2011
    Hey Marc. Sorry for the delay but we're moving the blog and it looks like our code is moving with it! Anyways I found the code locally after digging and I ran everything from scratch. . Bam it fired promptly after editing the table and the trace also promptly told me I didn't have perms :) so I went in and gave the Network Service user perms in the db and made sure the cache was running then restarted the broker. Edited the table again and then everything went great.I'm assuming you used the supplied config and edited paths appropriately for your machine? Did you use the tools here msdn.microsoft.com/.../bb522922.aspx to check SB?