Updating AppFabric Cache via SQL Service Broker External Activator
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
(
[http://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
