An integration sample project for Umbraco and SQL Server Service Broker. 10/22/2015

Check out an integration sample back-end project for Umbraco using SQL Server Service Broker.

Download the source code

Last updated: 10/22/2015

Photo credit: Sydney Buses new Scania by Simon Clancy on Flickr

Background

The purpose of this project was to demonstrate in a self contained how SQL Server Service Broker can be used with the Umbraco CMS.

The scenario in which this demo takes place is that of an Umbraco CMS instance that has some integration requirements.  These synthetic requirements are:

  • When a member is created, the corporate CRM system should be notified to initiate a Welcome Pack email workflow.
  • This company use a 3rd party recommendations engine that supplies links to be used elsewhere on the site.  Each time a new blog post is published, you need to invoke the recommendation engine provider's API.
  • A CDN is used to serve image files from this site, while the CDN picks up the image paths automatically, you must set a policy on new resources in the CDN, perhaps to set a custom expiry or something.  Further more, when an image is deleted from the CMS, you should invalidate it's path on the CDN because the boss is cranky about how much the CDN costs these days... anyway :-)

These were picked for this scenario to be examples of back office calls that may be long running and have their own API semantics.  Therefore it would be ideal to have these tasks be executed asynchronously from the user's flow through the app.

Also these example requirements suite fire-and-forget messaging.  If the response from any of those APIs needed to be presented immediately to the content author for example, then handling it synchronously in the Umbraco application event handlers would be the better choice.

Follow along:

No prior experience with Umbraco or Service Broker is necessary.  I hope this blog post explains the key points in this architecture to a point where you could try implementing something similar in your own project.

The code for this project is available on Github.

Overall architecture

The basic idea is to use SQL Server Service Broker as a queue.  Out of the box it provides asynchronous reliable message delivery that remains consistent with backups (because a queue is a structure in the database).   

While Umbraco is capable of running on SQL Server Compact (SQL CE), SQL Azure and MySQL all of my customer projects on Umbraco have been running on Microsoft SQL Server.  Given the hypothetical installation in this sample Microsoft SQL Server is assumed throughout.

That said, SQL Server Service Broker is included in SQL Server Express, and this demo works on Express edition.  The main limiting factor when using Service Broker on Express edition is remote invocation is not possible, limiting scale-out usage scenarios on that edition.

Going forward with the assumption that SQL Server is the database server for the scenario, SQL Server Service Broker is a good choice for a queue considering it is all in the same box.

Omissions

Two nice feature of Service Broker is the ability to correlate the exchange of related messages by conversation_handle & conversation_group_id; and Activation.  These features didn't make this demo.

Also error handling omitted to keep things brief (to read and to author :-)

Native XML as message body format

SQL Server's native XML type is used for the message body.  In Service Broker the message body can be anything that can cast to VARBINARY(max) however the native XML type works well for passing complex types around.  

Additionally Service Broker can validate the messages placed on a queue using an XML Schema Collection.  You can set VALIDATION to VALID_XML or even NONE in your CREATE MESSAGE TYPE statements to dial back the validation.

Console app to simulate the asynchronous message processor.

The ConsoleQueueReader project in the solution is a simple console app to simulate running your asynchronous processes in response to queue messages. 

For simplicity this console app polls the database in a loop and prints what it finds to screen.  A more efficient strategy is to use a SqlDependency on a SqlCommand, there are examples of this technique elsewhere online.

Set the connection string in app.config to connect to your Umbraco database, build the project and find the compiled binary.  You will run this later in the demo. 

The majority of this project is switch casing over message types as they are received off a queue.  Remember that with XSD validation on message types we can be confident the message body will parse in a predictable way.

Umbraco Application Events

Umbraco provides hooks to application events via Umbraco.Core.ApplicationEventHandler.  In the ApplicationStarted override you can setup event handlers on the following Umbraco services:

  • ContentService
    ContentTypeService
  • DataTypeService
  • FileService
  • MediaService
  • MemberService
  • LocalizationService

By checking the type alias or other properties of the entities effected by the event the event handlers are able create messages in using ServiceBroker library.

For example, in this demo project we are listening to the Saved event on the ContentService, and if the Content Type Alias is "BlogPost" we extract the summary generic property from the blog post, and use those values when putting a [//Recommendations/UpdateBlogPostMessage] message on the [ContentQueue] queue.

In this example (event handler hookup here) we are listening for Saved events on the content and media services as the Created event fires when a new draft is created where as the Saved event fires once the content is written to the database.

Service Broker library 

Umbraco Event handlers are implemented in the class library project called ServiceBroker.  This project serves to abstract invoking the stored procedures which interact with our queues.

One class is implemented for each queue's operations (ContentQueue.cs, MediaQueue.cs & MemberQueue.cs), like the stored procs they are variations on a theme and there is a 1:1 relationship between stored procs and methods.

Transaction semantics

This library enrolls each operation in an explicit transaction.  It is important to note that SQL Server Service Broker uses the transaction's success or failure to acknowledge a message accepted off the queue.  If you roll back the transaction the message will be returned to the queue, and if a message is returned to the queue 5 times that queue will be disabled.

Setup and Installation

In order to run the demo, clone the repo into a new path.  Make sure to have access to an instance of SQL Server 2008 or later (Express Edition is fine) and create a blank database. 

Umbraco installation

This repo contains a new blank install of Umbraco 7.3 in the project UmbracoWeb.  

To set up the web site project to be the web root in IIS or just run it in Cassini through Visual Studio.  Visiting this web site should take you straight to the Umbraco installation process.   Select a custom installation and specify the details for the SQL Server database you wish to use.  Once installation is complete, copy the connection string generated by Umbraco and inserted in your Web.config file and update the App.config file in the console app project to match.

Note that this project is not exactly blank, it should already have a reference to the ServiceBroker class library project in the solution.

The demo scenario is calls for having a Blog Post content type.  To save you having to create one in the demo, I have included a package in the repo that will set it up.  Install the UmbracoWeb\Blog_Post_Content_Type_1.zip local package using the Umbraco Developer back office dashboard.

Database installation

Once your Umbraco installation has completed, the Service Broker DDL can be run.

The repo contains a folder called SQLServiceBroker which contains the SQL Scripts, and a Management Studio solution file.

To set up the database for this example run the following two files in order:

  1. Setup\01-Setup-broker.sql.  This file creates the message types, contracts, services and queues needed to represent the messages in our integration scenario.
  2. Setup\02-Setup-sprocs.sql.  This file creates stored procedures that encapsulate access to the queues.  Service Broker has some particular syntax and abstraction here is useful.

That's it.

There are a couple of other files in this path that may be useful later if you take the demo further:

  • Setup\03-Remove-all.  Backs out all DDL added by 01-Setup-broker.sql & 02-Setup-sprocs.sql
  • Manage\CleanTransmissionQueue.sql.  Diagnostic script.
  • Manage\ServiceBrokerViews.sql.  Selects from views that describe your Service Broker setup.

Running the demo

Once your Umbraco installation is running and the Service Broker queues are configured, start the console app and let it continue to poll while you do the following in the CMS

  • Create a new member
  • Create or delete a new media item
  • Create a new page of type Blog Post and save it.

Moments after interacting with the UI you should observe the console app receiving the message and echoing the mock action to the console window, thus demonstrating the reliable async message delivery.

comments powered by Disqus