Experimenting with Azure CDN

With the gradual piecing together of the Lego bricks forming the slow move over of the Frog & Pencil website to a more managed approach (building of a custom CMS and an all-around better ASP.NET MVC architecture) I thought it would be interesting to document the move over of Frog & Pencil images to a CDN. I was inspired to give this a go after watching Scott Hanselman make the switch for his podcast site images and other Azure Friday videos, as documented here:

Scott Hanselman lifting and shifting images over to a CDN.
Azure CDN with Akamai.

It seemed like a relatively painless process and is a step in the right direction for our site as a whole; so, let’s give it a go!

NOTE: A short way into this post I realised that I was making a few missteps. This is cool, I think, as I would rather document the journey I took with the mistakes listed, to be honest – #KeepingItReal! However, for sanity (mine and yours) I’ll specify the ‘correct’ order of events that you should follow here that you can marry up with the ramblings below:

  1. Sign in to the Azure Portal.
  2. Create a storage container, if you don’t already have one.
  3. Download and utilise a storage explorer application (such as Azure Storage Explorer).
  4. Create a CDN Profile and CDN endpoint (that ties explicitly to your storage container, in this instance).
  5. Go to your DNS settings and generate a CNAME property, mapping a custom domain to your CDN if you wish to.
  6. Optionally, learn how to programmatically interact with your storage container.

Azure Portal – First Steps (documenting the journey)

First things first, we must hop on over to the Azure Portal. I searched the marketplace for ‘CDN’ and clicked create in the right-hand pane, as shown:

Creating a CDN

Creating a CDN.

The next phase involves configuring a CDN profile. The profile needs to be given a name and should be attached to an Azure Subscription. I’ve created a new Resource Group, by specifying a name for it, but it is possible to select an existing one for use here. There are some guidelines surrounding Resource Groups, such as items within a group should share the same lifecycle; more details can be found within this handy documentation article, read away!

The Azure CDN service is, of course, global but a Resource Group location must be set, which governs where resource metadata is ultimately stored. This could be an interesting facet to consider if there are particular compliance considerations regarding the storage of information and where it should be placed. I’m going with West Europe either way; a nice, easy choice this time around.

As for pricing, I have decided to head down the Akamai route, using the Standard Akamai pricing tier. I will have to see how this ultimately pans out cost wise over time, but it seems reasonable:

Azure CDN Provider Pricing

Azure CDN Provider Pricing.

At this point, we can explicitly create a CDN endpoint (where resources will be ultimately exposed). The endpoint has a suffix of ‘.azureedge.net’ and I’ve simply specified the first part of our domain, ‘frogandpencil’ as the prefix.

This is where I hit a bit of a revelation with the ‘Origin Type’ drop down. You can select from Storage, Cloud service, Web app or Custom origin (which is cool!), of which I want to use Storage. After selecting this I can pick an ‘Origin hostname’. The light bulb moment here, for me, is that I should have created a storage container first! I’d watched enough videos to have dodged this little problem, but I still managed to stumble…all part of the learning process πŸ˜‰

So… Let’s Create a Storage Container

Back to the market place then. The obvious pick seems to be ‘Storage account – blob, file, table, queue’, so I’ve gone ahead and clicked create here:

Setup Azure Storage.

Setup Azure Storage.

When creating the storage account there are a fair few options to consider, a good number that read as if they will impact pricing. I had to use the documentation found here to make choices. I settled on the setup described here (for images, and as the site isn’t yet using https, I’ve gone with the secure transfer feature being disabled – one for review in the future):

As an overview, the guidance suggests the use of the ‘Resource manager’ type of ‘Deployment model’ for new applications. There doesn’t seem to be a penalty for using the ‘StorageV2’ ‘Account kind’, which extends the types that can be stored outside of just blob data, so that is what I am going for.

Performance wise, the ‘standard’ option seems like an acceptable setting at the moment and for the kind of data I’ll be storing (images for now, and possibly other static content later down the line) I can opt out of any geo-redundant replication options. In the event of resource downtime, I can easily switch to the use of resources local to the website. Plus, there will not be any data being lost really, all easily rebuilt and recoverable.

As for the ‘Access tier’, I’m heading down the ‘Hot’ route as images will be accessed quite frequently (we have the CDN to consider here so I might tinker later on down the line).

I then pick a Subscription, give the Resource Group a name and select my region of choice before continuing.

I then get a new blade on the dashboard (which took a minute to create) and, on accessing, am presented with the following:

Storage Setup.

Storage Setup.

Managing the Storage Container

The first and perhaps most obvious choice for managing and actually getting some content up into the storage container is the Azure Storage Explorer, which I’ll be downloading and using.

After a painless install process, you should see the following, where you will be asked to connect to Azure Storage:

Connect to Azure Storage.

Connect to Azure Storage.

I simply used my Azure account sign in details here. I did notice however that the Azure Portal does expose, under ‘Access Keys’ (within the storage container dashboard), keys and connection strings. I’m assuming this is for other kinds of, including programmatic, access; which I’ll give a go I think as part of this post (as a wee bonus).

I used the right-click context menu to create a new container called ‘images’ and then used the upload button to push up a test image:

Azure Storage Explorer Upload Image.

Azure Storage Explorer Upload Image.

Again, against the container I used the right-click context menu to select ‘Set Public Access Level…’, which I’ve set as follows to allow public access to the blob data but not the container:

Container Public Access Setup.

Container Public Access Setup.

I now have a blob container with a single image in it with appropriate access rights configured. The question is can I access the image in its current state? We’re looking pretty good from what I can see.

Successful Access.

Successful Access.

Adding a custom domain

Next up, I plan on adding a custom domain to the storage account. To do this, I access the ‘Custom domain’ option as shown here:

Register Custom Domain.

Register Custom Domain.

I followed option 1 as listed here and created a CNAME record to map frogandpencilstorage.blob.core.windows.net to images.frogandpencil.com (I’m happy to wait for this to propagate).

Register images.frogandpencil.com.

Register images.frogandpencil.com.

Once the CNAME record is created you simply have to place your target URL in the text box provided and hit save.

New CNAME property.

New CNAME property.

Lastly, let’s take it for a spin and see whether we can access the image in the storage container via the custom URL…and voila:

Custom Domain Active.

Custom Domain Active.

Back to the CDN bit!

We’ve come full circle! With a storage container in place I can now use that to feed a configured CDN. Consequently, I backtracked and followed the previously listed steps being sure to select my ‘Origin hostname’ to point to the newly created storage container:

CDN Profile & Endpoint Configuration.

CDN Profile & Endpoint Configuration.

On clicking create it takes a short time for the CDN to be configured.

So, what do I do now

Looking through the videos I made another discovery. This is where I want to adjust the previously created CNAME property (that I setup for the storage container) and hook this up to the CDN endpoint instead. The portal exposes custom domain mapping for a CDN much like for a storage container:

Change CNAME to map to CDN.

Change CNAME to map to CDN.

Portal CDN Custom Domain Mapping.

Portal CDN Custom Domain Mapping.

Again, I had to wait a short time for the CNAME property change to propagate but, after that, I was all set. I then spent a little time verifying that the CDN was up and running. There are quite a few optimisation options including the ability to set a custom ‘Origin path’ (such as ‘images’) but I’m leaving these be for the time being.

The Bonus Section – Programmatically Add Items to Azure Storage

As promised, this next section discusses (in a very bare bones fashion) what is required to write to an Azure storage container. I’ve created a stub Console Application to get running with and the process itself is simple (not considering errors, existence checks and threading, of course!).

You need to:

  1. Reference the WindowsAzure.Storage NuGet package.
  2. Add a reference to System.Configuration (if you want to put connection strings, folder paths and container names in configuration files and read them out).
  3. Then simply follow the code outlined below to get started.

In my test setup, the ‘SourceDirectory’ is looking at ‘C:\test-files\’ (contains just images) and the ‘TargetContainer’ is called ‘images’, as per my earlier configuration. The connection string can be obtained from the Azure Portal, under ‘Storage Account > Settings > Access Keys’.

Test Files ready for upload.

Test Files.

Storage Access Keys.

Storage Access Keys.

The App.config for the test application is structured like this, with the connection string being set to the correct value as per the information found in the Azure Portal.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1" />
    </startup>
  <connectionStrings>
    <add name="FrogAndPencilStorageConnection" connectionString="[OBTAINED_FROM_THE_AZURE_PORTAL]" />
  </connectionStrings>
  <appSettings>
    <add key="SourceDirectory" value="C:\test-files\"/>
    <add key="TargetContainer" value="images"/>
  </appSettings>
</configuration>

Then, finally, the actual test code which…

  • Attempts to connect to the storage container creating a CloudStorageAccount object, based on the connection string information supplied.
  • Then uses the CloudStorageAccount object to get create a new CloudBlobContainer object (based on the container name stored in the configuration settings).
  • Finally, utilise this CloudBlobContainer, along with information about the files to process, to actually perform the upload.
using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.Blob;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.IO;
using System.Linq;

namespace WriteToAzureStorageTestApp
{
    /// <summary>
    /// Test application for writing to Azure Storage.
    /// Basic, test code only (throwaway code).
    /// </summary>
    internal class Program
    {
        #region Main (Entry Point) Method

        /// <summary>
        /// Main entry point method for this console application.
        /// </summary>
        /// <param name="args">Optional input arguments.</param>
        private static void Main(string[] args)
        {
            DemoWritingToAzureStorage();
        }

        #endregion Main (Entry Point) Method

        #region Private Static Methods

        /// <summary>
        /// Private static demo method illustrating how to upload to Azure Storage.
        /// </summary>
        private static void DemoWritingToAzureStorage()
        {
            // First use the FrogAndPencilStorageConnection connection string (for Azure Storage) to obtain a CloudStorageAccount, if possible
            CloudStorageAccount.TryParse(ConfigurationManager.ConnectionStrings["FrogAndPencilStorageConnection"].ConnectionString, out CloudStorageAccount storageAccount);
            if (storageAccount != null)
            {
                // We have a CloudStorageAccount...proceed to grab a CloudBlobContainer and attempt to upload any files found in the 'SourceDirectory' to Azure Storage
                Console.WriteLine("Obtaining CloudBlobContainer.");

                CloudBlobContainer container = GetCloudBlobContainer(storageAccount);

                Console.WriteLine("Container resolved.");

                Console.WriteLine("Obtaining files to process.");

                List<string> filesToProcess = Directory.GetFiles(ConfigurationManager.AppSettings["SourceDirectory"]).ToList();

                UploadFilesToStorage(container, filesToProcess);
            }

            Console.WriteLine("Processing complete. Press any key to exit...");
            Console.ReadLine();
        }

        /// <summary>
        /// Private static utility method that obtains a CloudBlobContainer
        /// using the container name stored in app settings.
        /// </summary>
        /// <param name="storageAccount">The cloud storage account to retrieve a container based on.</param>
        /// <returns>A fully instantiated CloudBlobContainer, based on the TargetContainer app setting.</returns>
        private static CloudBlobContainer GetCloudBlobContainer(CloudStorageAccount storageAccount)
        {
            CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient();

            return blobClient.GetContainerReference(ConfigurationManager.AppSettings["TargetContainer"]);
        }

        /// <summary>
        /// Private static utility method that, using a CloudBlobContainer, uploads the
        /// files passed in to Azure Storage.
        /// </summary>
        /// <param name="container">A reference to the container to upload to.</param>
        /// <param name="filesToProcess">The files to upload to the container.</param>
        private static void UploadFilesToStorage(CloudBlobContainer container, List<string> filesToProcess)
        {
            // Process each file, uploading it to storage and deleting the local file reference as we go
            filesToProcess.ForEach(filePath =>
            {
                Console.WriteLine($"Processing and uploading file from path '{ filePath } (then deleting)'.");

                // Upload the file based on name (note - there is no existence check or guarantee of uniqueness - production code would need this)
                container.GetBlockBlobReference(Path.GetFileName(filePath)).UploadFromFile(filePath);

                RemoveFileFromLocalDirectory(filePath);
            });
        }

        /// <summary>
        /// Private static utility method for deleting a file.
        /// </summary>
        /// <param name="filePath">The file path (full) to delete based upon.</param>
        private static void RemoveFileFromLocalDirectory(string filePath)
        {
            // Only attempt the delete if the file exists
            if (File.Exists(filePath))
            {
                File.Delete(filePath);
            }
        }

        #endregion Private Static Methods
    }
}
Test Upload Application Running.

Test Upload Application Running.

Test Files Uploaded.

Test Files Uploaded.

There you have it; a rather around the houses and off the wall tour of setting up an Azure storage container and then linking this to an Azure CDN. Plenty of images still need to be brought over into the new storage container (and a few code changes to boot), but I feel like I am on a pilgrimage to a better place. I hope this proves useful nonetheless and, until the next time, happy coding!

Addendum

After a further play I realised that the C# example I’d knocked up was not setting the content type correctly on upload, as follows:

Incorrect Content Type.

Incorrect Content Type.

To this end, I adjusted the UploadFilesToStorage method to set the content type on a CloudBlockBlob before the upload is triggered, as illustrated here:

/// <summary>
/// Private static utility method that, using a CloudBlobContainer, uploads the
/// files passed in to Azure Storage.
/// </summary>
/// <param name="container">A reference to the container to upload to.</param>
/// <param name="filesToProcess">The files to upload to the container.</param>
private static void UploadFilesToStorage(CloudBlobContainer container, List<string> filesToProcess)
{
	CloudBlockBlob blockBlob;

	// Process each file, uploading it to storage and deleting the local file reference as we go
	filesToProcess.ForEach(filePath =>
	{
		Console.WriteLine($"Processing and uploading file from path '{ filePath } (then deleting)'.");

		// Upload the file based on name (note - there is no existence check or guarantee of uniqueness - production code would need this)
		blockBlob = container.GetBlockBlobReference(Path.GetFileName(filePath));

		// Correctly configure the content type before uploading
		blockBlob.Properties.ContentType = "image/jpg";

		blockBlob.UploadFromFile(filePath);

		RemoveFileFromLocalDirectory(filePath);
	});
}

You should then see items with the correct content type in the container:

Correct Content Type.

Correct Content Type.

To access images via the custom domain, essentially my CDN, I had to ‘purge’ it also at this point.

Again, happy coding.

Generic Value Type List CSV Extension

I came across a piece of code on my travels whereby a comma-separated string was split and then parsed into long values, ultimately returned to the method caller as a list of longs. A similar method was also created for operating on and converting values to integers, not particularly DRY code, but functioned fine. All good and well, I thought, but there is no reason not to encapsulate this into a method (I opted to create a string extension) that encompasses working with value types in general. Not a 100% solution, but a start on the right track.

Thirty minutes of tinkering yielded the following string extension (and supporting unit tests), which is currently constrained to value types only but could possibly be further constrained. I’ve provided some unit test declarations to give you an idea of its usage. It has a piece of cheeky boolean handling in it which is perhaps not best placed as you can quickly end up on a dark road when shoehorning type-specific code into generic methods. For now, though it seems like an acceptable solution:

Here’s the extension for starters:

using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;

namespace GenericExtensions
{
    public static class ObjectExtensions
    {
        /// <summary>
        /// Public static string extension can convert a comma-separated list (string)
        /// into a List of type T (where T is a struct, just to make this more constrained).
        /// </summary>
        /// <typeparam name="T">The struct type to attempt a conversion to (for each value in the comma-separated string).</typeparam>
        /// <param name="csvString">The comma-separated source string to split into values and then attempt conversions on.</param>
        /// <param name="errorList">A List of type string that catches conversion errors.</param>
        /// <returns>A list containing types of T where a conversion is possible.</returns>
        public static List<T> GetValuesFromCsvString<T>(this string csvString, out List<string> errorList) where T : struct
        {
            List<T> convertedValues = new List<T>();

            errorList = new List<string>();

            // Only proceed (and attempt conversions) where the string provided contains content
            if (!string.IsNullOrWhiteSpace(csvString))
            {
                // Trim up csv values (we don't want whitespace to intefer with the conversion)
                IEnumerable<string> trimmedCsvValues = csvString.Split(',').Select(csv => csv.Trim());

                // Attempt the conversion for each value in the comma-separated list (value to type T). Note errors if and when they occur and store
                // errors/converted values in the appropriate lists
                foreach (string csv in trimmedCsvValues)
                {
                    try
                    {
                        // Trigger manual conversion for bool types. Not the most ideal but sufficient for basic needs
                        if (typeof(T) == typeof(bool))
                        {
                            switch (csv.ToLowerInvariant())
                            {
                                case "1":
                                case "yes":
                                case "on":
                                    convertedValues.Add((T)Convert.ChangeType(true, typeof(T), CultureInfo.InvariantCulture));
                                    break;

                                case "0":
                                case "no":
                                case "off":
                                    convertedValues.Add((T)Convert.ChangeType(false, typeof(T), CultureInfo.InvariantCulture));
                                    break;

                                default:
                                    // Conversion is not possible
                                    throw new InvalidCastException();
                            }
                        }
                        else
                        {
                            // Standard conversion attempt for other structs
                            convertedValues.Add((T)Convert.ChangeType(csv, typeof(T), CultureInfo.InvariantCulture));
                        }
                    }
                    catch (Exception ex)
                    {
                        errorList.Add($"Could not convert value '{ csv }' to type '{ typeof(T).Name }'. Exception type: { ex.GetType().Name }; Exception Message: { ex.Message }");
                    }
                }
            }

            // Return successfully converted values
            return convertedValues;
        }
    }
}

Lastly, here are the unit tests to put the extension through its paces:

using Microsoft.VisualStudio.TestTools.UnitTesting;
using System;
using System.Collections.Generic;

namespace GenericExtensions.Tests
{
    [TestClass]
    public class ObjectExtensionTests
    {
        [TestMethod]
        public void GetValuesFromCsvString_ConversionToBool_InRangeValuesConverted()
        {
            List<bool> booleans = "1,0, 1 ,20,test,on,off,No,YES,  3.40282347E+38   , 99.9 "
                .GetValuesFromCsvString<bool>(out List<string> errorList);

            Assert.IsTrue(booleans.Count == 7);
            Assert.IsTrue(errorList.Count == 4);

            Assert.AreEqual(true, booleans[0]);
            Assert.AreEqual(false, booleans[1]);
            Assert.AreEqual(true, booleans[2]);
            Assert.AreEqual(true, booleans[3]);
            Assert.AreEqual(false, booleans[4]);
            Assert.AreEqual(false, booleans[5]);
            Assert.AreEqual(true, booleans[6]);
        }

        [TestMethod]
        public void GetValuesFromCsvString_ConversionToLong_InRangeValuesConverted()
        {
            List<long> longs = "test,99.9,9223372036854775807 ,9223372036854775808"
                .GetValuesFromCsvString<long>(out List<string> errorList);

            Assert.IsTrue(longs.Count == 1);
            Assert.IsTrue(errorList.Count == 3);

            Assert.AreEqual(9223372036854775807, longs[0]);
        }

        [TestMethod]
        public void GetValuesFromCsvString_ConversionToFloat_InRangeValuesConverted()
        {
            List<float> floats = "1,99.998, 3.40282347E+38 ,9223372036854775808, random string "
                .GetValuesFromCsvString<float>(out List<string> errorList);

            Assert.IsTrue(floats.Count == 4);
            Assert.IsTrue(errorList.Count == 1);

            Assert.AreEqual(1f, floats[0]);
            Assert.AreEqual(99.998f, floats[1]);
            Assert.AreEqual(3.40282347E+38f, floats[2]);
            Assert.AreEqual(9.223372E+18f, floats[3]);
        }

        [TestMethod]
        public void GetValuesFromCsvString_ConversionToInt_InRangeValuesConverted()
        {
            List<int> ints = "1,2147483647, random string  , 3.40282347E+38 ,-2147483649, 22 "
                .GetValuesFromCsvString<int>(out List<string> errorList);

            Assert.IsTrue(ints.Count == 3);
            Assert.IsTrue(errorList.Count == 3);

            Assert.AreEqual(1, ints[0]);
            Assert.AreEqual(2147483647, ints[1]);
            Assert.AreEqual(22, ints[2]);
        }

        [TestMethod]
        public void GetValuesFromCsvString_ConversionToShort_InRangeValuesConverted()
        {
            List<short> shorts = "1,2147483647, random string  , 32767 ,32768,-32768,-32769,-2147483649, 22 "
                .GetValuesFromCsvString<short>(out List<string> errorList);

            Assert.IsTrue(shorts.Count == 4);
            Assert.IsTrue(errorList.Count == 5);

            Assert.AreEqual(1, shorts[0]);
            Assert.AreEqual(32767, shorts[1]);
            Assert.AreEqual(-32768, shorts[2]);
            Assert.AreEqual(22, shorts[3]);
        }

        [TestMethod]
        public void GetValuesFromCsvString_ConversionToUInt16_InRangeValuesConverted()
        {
            List<UInt16> UInt16s = "1,2147483647, random string, -1, -22,  , 65535 ,65536,-32768,-32769,-2147483649, 22 "
                .GetValuesFromCsvString<UInt16>(out List<string> errorList);

            Assert.IsTrue(UInt16s.Count == 3);
            Assert.IsTrue(errorList.Count == 9);

            Assert.AreEqual(1, UInt16s[0]);
            Assert.AreEqual(65535, UInt16s[1]);
            Assert.AreEqual(22, UInt16s[2]);
        }
    }
}

A very quick prototype piece of code for sure, which needs further testing. I’d also like to performance test this implementation and perhaps get a better idea of how Convert.ChangeType works under the hood. I hope everyone is having a super weekend and take care until the next time. πŸ™‚

Looking for a Lighthouse

Happy New Year all and I truly hope you all enjoyed a terrific festive period.

As a little weekend treat, I decided to pick myself up a copy of the ‘net’ magazine, mainly due to the included feature articles on which web development and design tools are ‘en-flique’ (that one is for Claire; a trip to the urban dictionary is in order) for 2018. There are also some amazing looking articles discussing things like colour palette choices, and tools that assist in creating them, which should prove handy as I will be looking to potentially change up the Frog & Pencil website a little as I craft a fully functional CMS this year; something that has been well overdue.

As a quick aside, I’ve decided to check out Google’s own automated page analysis tool, Lighthouse. This can run performance and accessibility analysis on public or password protected sites. Information for getting started can be found here.

I’m opting to run this within Chrome development tools, but you can run this from the command line or as a Node module if you prefer (which allows you to hook this into a continuous integration setup, which could be incredibly useful).

Up to this point, I have been using YSlow as a web page dissection tool, but I’m happy to bust out an alternative to keep things interesting.

So, what kind of feedback does the tool provide and how does it present it? I’ll run this against the Frog & Pencil homepage and show you the results (no matter how bad they turn out, I’ll be honest!). When using Chrome you just need to inspect the audits tab, within Chrome development tools (accessed via F12 or Ctrl+Shift+I, on windows), to get up and running as follows:

Lighthouse via the Audits Tab.

Audits Tab.

On clicking ‘Perform an audit…’ you be presented with options as below (I’ll leave them all checked for this particular test run):

Audit Options for Lighthouse.

Audit Options.

The report is, on first inspection, very detailed and, as you can see, I have a fair bit of work to do (although I’m happy with the accessibility rating at least). The report is downloadable using the highlighted button:

Lighthouse Report Header.

Lighthouse Report Header.

The tests performed also appear to be more strict that the YSlow V2 test, which is nice to see:

YSlow V2 Test.

YSlow V2 Test.

There have been some surprising opportunities for improvement highlighted. I’ve long known that I should switch out the entire site to run over https and when the site is overhauled I intend to make better use of bundling for static files and will consider the use of a CDN. I have plenty of work to do with image compression also.

Here are a few things that really caught my attention:

1) How poor the site ran under simulated 3G speeds:

Simulated 3G Speeds

Simulated 3G Speeds.

2) The scale of the improvements still to be made by reducing render blocking scripts/stylesheets (a boo boo that I should really be covering) and image management:

Performance Improvement Opportunities.

Performance Improvement Opportunities.

3) The report highlighted that I was using libraries with known vulnerabilities and that I have left in code that was writing errors to the console (doh!):

Third Party Libraries.

Third Party Libraries.

This does bring into focus the core need of revisiting the website this year and giving it a thorough tune-up, as opportunities towards the end of last year were at a premium. All in all, if you’ve not used Lighthouse yet I would suggest giving it a look; especially as it takes seconds to run. I’ll be working my way through the highlighted areas in the report in the meantime!

All the best πŸ˜‰

Inspect Object Locking with T-SQL

I recently came across a scenario whereby we wanted to programmatically, via T-SQL, inspect locking behaviour on objects in a particular database when operations were being performed on given data (actually just a SELECT (WITH (NOLOCK) and DELETE statement, on a table). The requirement was to get a direct view on whether the table was having an exclusive lock thrust upon it, or whether shared access was still in play (and get an insight into whether we were in the realms of row-level locking or not).

This turns out to be relatively easy through the use of the inbuilt Dynamic Management Views; specifically the sys.dm_tran_locks view, coupled with a cheeky join to sys.objects for information to identify the object being referenced (this is my own interpretation, from scratch, but you can easily find many similar examples so pick what floats your boat!):

/*
	Interrogate the lock types at database level (can see the lock type on a table when another statement is running)
	-------------------------------------------------------------------------------------------------------------------

	1) For information on the different lock modes in SQL Server visit the following resources:
		a) https://technet.microsoft.com/en-us/library/ms175519(v=sql.105).aspx
		b) http://lockergnome.com/2010/01/26/what-are-the-different-lock-modes-in-sql-server/
		c) https://logicalread.com/sql-server-lock-modes-mc03/ - Haven't actually read this one yet but looked intriguing enough to include as a 'bonus' :o)
	2) An interesting aside into the WITH (NOLOCK) hint: http://sqlsoldier.net/wp/sqlserver/thetruthaboutnolockhints
	3) The difference by IX and X locks: http://database.ittoolbox.com/groups/technical-functional/sql-server-l/difference-between-x-and-ix-lock-151903
*/
SELECT 
tl.resource_type AS [ResourceType]
, CASE 
	WHEN tl.request_mode = 'Sch-S'
	THEN 'Schema Stability (ensure entity cannot be dropped)'
	WHEN tl.request_mode = 'IS'
	THEN 'Intent Shared'
	WHEN tl.request_mode = 'IX'
	THEN 'Intent Exclusive (exclusive lock to subordinate resource in the lock hierarchy - table can still be accessed in part)'
	WHEN tl.request_mode = 'X'
	THEN 'Exclusive (full, exclusive access to the resource)'
	ELSE tl.request_mode
END AS [LockType]
, so.[object_id] AS [ObjectId] 
, so.name AS [ObjectName]
, so.type_desc AS [TypeDesc]
FROM sys.dm_tran_locks tl
	-- JOIN to sys.objects to get identifiable information on the resource marked with a lock
	INNER JOIN sys.objects so ON tl.resource_associated_entity_id = so.object_id
-- Show lock information for the database in scope (this could be parameterised - this could be placed in an stored procedure, for example)
WHERE tl.resource_database_id = DB_ID();

In the comments above the code snippet, I’ve included a couple of links to detail the various lock types (information that can be shown in the request_mode view column) that you may encounter. Just run the query whilst a transaction that you want to inspect (for locking activity) is in progress, and voila, you’ll get an idea as to how the resources are being accessed and what impact this is having locking wise.

As a quick illustration let’s knock up a test database and table, using the following script which incorporates a very trivial setup:

-- Test script for checking out the dm_tran_locks test script

-- 1) Create the test database
CREATE DATABASE [LockTestDatabase];
GO

USE [LockTestDatabase];

-- 2) Create the LockTest table within the LockTestDatabase database (super noddy example for illustration only)
CREATE TABLE [dbo].[LockTest]
(
	Id				INT				PRIMARY		KEY	IDENTITY(1, 1)	NOT NULL
	, SomeInfo		NVARCHAR(20)									NOT NULL
);

-- 3) Create some simple test data
DECLARE
@Counter		INT = 1;

WHILE (@Counter <= 250000)		-- Want a non-trivial amount of data so queries take a couple of seconds to complete
BEGIN

	PRINT (@Counter);

	INSERT INTO [dbo].[LockTest]
	(
		SomeInfo
	)
	VALUES
	(
		'Information' + CONVERT(NVARCHAR(6), @Counter)
	);

	SET 
	@Counter += 1;

END;

The insertion of test records takes a short while so it’s possible to run the dm_tran_locks test script and obtain the following results:

LockTest Table Lock State during Insert.

LockTest Table Lock State during Insert.

After the test insertions are complete, attempt to run a SELECT statement using the WITH (NOLOCK) hint:

SELECT 
lt.Id
, lt.SomeInfo
FROM dbo.LockTest lt WITH (NOLOCK);

You should hopefully observe a shared resource lock, for schema stability only, that essentially just prevents the resource from being dropped whilst information is being requested from the table:

LockTest Table Lock State during SELECT (WITH (NOLOCK)).

LockTest Table Lock State during SELECT (WITH (NOLOCK)).

I’m not going into any kind of detailed explanation with regards to the lock types here, I’m just proving that observing the lock types being applied is easily possible. Lastly, attempt a deletion (wrapped in a rollback, so it can easily be re-run):

BEGIN TRANSACTION

	DELETE
	FROM dbo.LockTest;

ROLLBACK TRANSACTION

A lock is applied that indicates exclusive access to the resource (due to the nature of the delete here, as all rows are being removed), aka table:

LockTest Table Lock State during Deletion.

LockTest Table Lock State during Deletion.

This gives you an insight into how straightforward it is to get an impression on how your SQL objects are being influenced, locking wise, by your code. If anyone has any suggestions on further reading, more useful snippets, etc. then please add a comment below. Thanks, all.

Where You At…

Hi there wonderful people! I hope everyone is keeping well and remembering to be awesome :o)

The great wave of life has risen up and crashed back down a fair few times in the last handful of months which has, in turn, lead to a serious drought on new content; apologies for this.

Being brutally honest I’ve found myself a little bit zapped of energy and inspiration in general. We had a very long and protracted house move which involved a good number of twists and turns, mixing that in with a busy period at work (and a stretch without good internet access at the new house, in the midst of all this) and I think I did approach (and am just getting out of the other side of) something close to burnout; it happens to all of us at some point or another. It has meant that I’ve fallen into a little rut of ‘imposter syndrome’; a dose of passion is required to climb out! With all of the change going on around me nagging self-doubt as to my abilities has come into the fold and I’ve been scratching my head on how best to deal with it; nothing but the honest truth from me.

Therefore, I’m planning on trying to take some responsibility for getting back into the game and rolling some more content on to the board; reflecting a drive to inject some confidence and passion into the proceedings once again. Thanks for sticking with this, it’s a key thing in the development business to keep reaching for new horizons (and to pick which horizons should be pursued and which should be ignored, as there are so many damn options) so getting the engine kick-started again, and carving out the time to keep it ticking over, is now a priority.

Content is on the way then…happy coding until the next time :o)

Session State Behaviour & Async Headaches

I was battling a little issue today surrounding an action method no longer being called asynchronously; the issue turned out to be related to some recent session-based code being added to our code base. In short, the minute session is detected in the underlying code, the ‘default’ behaviour for session state handling throws a monkey wrench in asynchronicity, regardless of the operation being performed on session data (i.e. writing to the session or just reading from the session). This, for me, turned into a performance headache.

There is an attribute that can be placed at controller level that states ‘I’m reading from session only, please continue to allow asynchronous operations’, which when used looks like this:

[SessionState(System.Web.SessionState.SessionStateBehavior.ReadOnly)]
public class TestController : Controller
{
          ……
}

However, if you want to implement a control mechanism at the action level you need to travel down the custom controller factory/attribute route. This post turned out to be a lifesaver: Session State Behaviour Per Action in ASP.NET MVC

In short, this setup enables you to set session state behaviour handling at the action level by adorning the target method with a custom attribute; bonza!

When inspecting this and underlying, base class, implementations you will most likely discover that it’s not immediately clear how to handle scenarios where overridden methods exist (where methods match by name but differ by signature). This, for me, caused several crunches into the dreaded AmbigiousMatchException.

The implementation below shows my modified override of the DefaultControllerFactory GetControllerSessionBehavior method that is designed to a) avoid exceptions and b) only try to ‘discover’ the attribute and apply custom session state behaviour handling where a single method is ‘matched’ (based on the supplied RequestContext). If the custom attribute is not found, or more than one method is found matching by name (or another error occurs) base logic kicks in and takes precedence:

        /// <summary>
        /// Public overridden method that looks at the controller/action method being called and attempts
        /// to see if a custom ActionSessionStateAttribute (determining how session state behaviour should work) is in play.
        /// If it is, return the custom attributes SessionStateBehaviour value via the Behaviour property, in all other instances
        /// refer to the base class for obtaining a SessionStateBehavior value (via base.GetControllerSessionBehavior).
        /// </summary>
        /// <param name="requestContext">The request context object (to get information about the action called).</param>
        /// <param name="controllerType">The controller type linked to this request (used in a reflection operation to access a MethodInfo object).</param>
        /// <returns>A SessionStateBehavior enumeration value (either dictacted by us based on ActionSessionStateAttribute usage or the base implementation).</returns>
        protected override SessionStateBehavior GetControllerSessionBehavior(RequestContext requestContext, Type controllerType)
        {
            try
            {
                // At the time of writing base.GetControllerSessionBehavior just returns SessionStateBehaviour.Default but to make this robust we should just call
                // base.GetControllerSessionBehavior if the controllerType is null so any changes to the base behaviour in future are adhered to
                if (controllerType != null)
                {
                    // Defensive code to check the state of RouteData before proceeding
                    if (requestContext.RouteData != null
                        && requestContext.RouteData.Values != null
                        && requestContext.RouteData.Values["action"] != null)
                    {
                        // Attempt to find the MethodInfo type behind the action method requested. There is a limitation here (just because of what we are provided with) that
                        // this piece of custom attribute handling (for ActionSessionStateAttribute) can only be accurately determined if we find just one matching method
                        string actionName = requestContext.RouteData.Values["action"].ToString();
                        List<MethodInfo> controllerMatchingActionMethods = controllerType.GetMethods(BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance)
                            .Where(method => method.Name.Equals(actionName, StringComparison.InvariantCultureIgnoreCase)).ToList();

                        // In order to avoid ambiguous match exceptions (plus we don't have enough information about method parameter types to pick the correct method in the case
                        // where more than one match exists) I needed to rig this in such a way that it can only work where one matching method, by name, exists (works for our current use cases) 
                        if (controllerMatchingActionMethods != null && controllerMatchingActionMethods.Count == 1)
                        {
                            MethodInfo matchingActionMethod = controllerMatchingActionMethods.FirstOrDefault();

                            if (matchingActionMethod != null)
                            {
                                // Does the action method requested use the custom ActionSessionStateAttribute. If yes, we can return the SessionStateBehaviour specified by the
                                // developer who used the attribute. Otherwise, just fail over to base logic
                                ActionSessionStateAttribute actionSessionStateAttr =
                                    matchingActionMethod.GetCustomAttributes(typeof(ActionSessionStateAttribute), false)
                                        .OfType<ActionSessionStateAttribute>()
                                            .FirstOrDefault();

                                if (actionSessionStateAttr != null)
                                {
                                    return actionSessionStateAttr.Behaviour;
                                }
                            }                       
                        }
                    }
                }
            }
            catch
            {
                // If any issues occur with our custom SessionStateBehavior inferring handling we're best to just let the base method calculate this instead (best efforts 
                // have been made to avoid exceptions where possible). Could consider logging here in future (but we're in an odd place in the MVC lifecycle, could cause
                // ourselves more issues by attempting this so will only do if absolutely required)
            }

            return base.GetControllerSessionBehavior(requestContext, controllerType);   
        }

This appeared to be a pretty robust solution in my case (and we gained back the asynchronous processing on the targetted methods = big plus), so, hopefully, this comes in handy for others at some point.

Cheers all!

Zoning out with Moment Timezone

I’ve recently been heavily embedded in implementing time zone sensitivity into a web application and I thought I’d share my first experiences on handling this from the perspective of the browser.

A great little library for handling this kind of tricky number can be found in the form of Moment Timezone, which sits proudly beside Moment.js, but as a full date parsing solution incorporating time zones.

The part of the library that really caught my attention was the time zone inferring abilities of the library; the superbly named ‘guess‘ function (loving the name!). The function, despite the name, is actually pretty sophisticated, so let’s take a look at a working example and how the documentation defines the ‘guts’ of its time zone guessing powers.

Moment Timezone can be installed and used in a number of different ways, as described here, but I went with the good old classic method of adding a NuGet package via Visual Studio:

Adding Moment Timezone via NuGet.

Adding Moment Timezone via NuGet.

Or, if you want to use the Package Manager Console then use this nugget instead:

Install-Package Moment.Timezone.js

Once the package is installed, alive and kicking we need to (as you would expect) reference the supporting Moment JavaScript library followed by the Moment Timezone based library, as follows:

<script src="~/Scripts/moment.min.js" type="text/javascript"></script>
<script src="~/Scripts/moment-timezone-with-data.min.js" type="text/javascript"></script>

You are then ready to utilise the guess function in a stupendous one-liner, just like this (wrapped in a jQuery document ready function, in this example):

<script type="text/javascript">
    // On page load grab a value denoting the time zone of the browser
    $(function () {
        // Log to the console the result of the call to moment.tz.guess()
        console.log(moment.tz.guess());
    });
</script>

The screenshots listed here show just a few examples of how the guess function works (by providing a tz database, or IANA database, value denoting which time zone Moment Timezone has inferred the client is in).

Moment Guess Usage London.

Moment Guess Usage London.

Moment Guess Usage Cairo.

Moment Guess Usage Cairo.

Moment Guess Usage Havana.

Moment Guess Usage Havana.

For newer, supporting browsers, Moment Timezone can utilise the Internationalization API (Intl.DateTimeFormat().resolvedOptions().timeZone) to obtain time zone information from the browser. For other browsers, Moment Timezone will gather data for a handful of moments from around the current year, using Date#getTimezoneOffset and Date#toString, to intelligently infer as much about the user’s environment as possible. From this information, a comparison is made against entries in the time zone database and the best match is returned. The most interesting part of this process is what happens in the case of a tied match; in this instance, a cities population becomes a deciding factor (the time zone linking to a city with the largest population is returned).

A full listing of tz database values can be found using the link below, showing the range of options available including historical time zones. It’s worth noting that the tz database also forms the backbone of the very popular Joda-Time and Noda Time date/time and timezone handling libraries (Java and C#, respectively; from the legendary Mr Skeet!).

List of tz database zones

For the project I was involved with, I ended up using Noda Time to actually perform conversions server side, utilising Moment Timezone to provide a ‘best stab’ at a user’s timezone on first access of the system. I’d like to give this the attention it deserves in a follow-up post.

Have a great week everyone, until the next time!