AdaTheDev

Troubleshooting Azure Function Consumption Plan Scaling

2021-12-17T13:02:00.003+00:00

I recently encountered an issue with an Azure Function Event Hub trigger running on a Linux Consumption Plan, whereby events stopped being consumed by the trigger once it went to sleep. I learned a lot about consumption plan scaling and diagnosing what was going on, so in this post I'm going to share the key takeaways that I picked up as it was a bit of a journey.

Background: Azure Function Event Hub trigger stopped processing events

This was in a low-throughput scenario for testing - so the rate of events coming into the Event Hub was low/sporadic. What I was seeing was:

my Event Hub trigger (C#) would stop consuming events - no errors, no failures, events were still hitting the Event Hub but just weren't being consumed
I went into the Function App in the Azure Portal to investigate, and it would then kick back into life and process the events despite not changing anything
inevitably, it would then stop consuming events again after a short period of time once I came out of the Portal

Based on some Googling, I saw others raising similar issues going back to 2017. But either there was no real outcome as the raised issues went cold and closed, or they were due to issues that had now been resolved and weren't the issue in my case.

Background: Scale controller

A good intro can be found in the MS docs here. The key points are:

the scale controller monitors events from whichever source is triggering your Azure Function (Event Hub in my case) to determine when instances need to scale out or in
this will scale instances down to 0 when no events are coming in, and your app will then go to sleep
this should then scale instances back up when it detects events are coming in and the number of current instances is not sufficient to process
in the scenario where it needs to scale back up from 0 instances, you will experience a cold start - a slight delay while it spins up an instance

In my scenario, no matter how long I waited, it was not scaling up from 0 despite new events coming in. It only processed them when I went into the Azure Function in the Portal - this is explained by the fact that when you go in there, it actually forces it to be loaded onto an instance. This is bypassing the scale controller. Once you know this behaviour, it helps focus you on to "why isn't the scale controller doing its thing?"

Diagnosing scale controller

The key to identifying the underlying issue, was to enable scale controller logging by adding an appsetting for SCALE_CONTROLLER_LOGGING_ENABLED. I wanted logging out to Application Insights so I set it to AppInsights:Verbose. Alternatively you can log out to a blob if you prefer, as per the docs, but the following relates to diving into AppInsights. There is info on how to query those logs here - I used this query to see everything going on, which was key:


traces 
| extend CustomDimensions = todynamic(tostring(customDimensions))
| where CustomDimensions.Category == "ScaleControllerLogs"
| order by timestamp desc

I could then see this error:


FunctionName: 'TestEventHubTrigger'. Trigger's Event Hub name '%EventHubSettings:HubName%'
failed to resolve from AppSettings. Error: ''%EventHubSettings:HubName%' does not resolve
to a value.'.

Here's my trigger method signature:


[FunctionName("TestEventHubTrigger")]
public async Task Run(
    [EventHubTrigger("%EventHubSettings:HubName%",
        Connection = "EventHubSettings:Connection",
        ConsumerGroup = "%EventHubSettings:ConsumerGroup%")]
    EventData[] events,
    ILogger log)

My Function *does* work and processes events when I nudge it in the Portal, those appsettings are configured correctly - yet the scale controller is having an issue resolving them. As we're targeting Linux, I had the appsettings configured in the portal using the __ form - i.e. EventHubSettings__HubName. So I tried out changing that setting from %EventHubSettings:HubName% to %EventHubSettingsHubName% so it's not nested but root-level instead and updated the appsetting to EventHubSettingsHubName. Restarted the Function App - bingo! Well, nearly - it didn't like the ConsumerGroup setting either, but showed I was on the right path.

Once I rolled this out, I then spotted a consistent higher level of requests on the Event Hub, even when events were not coming in. This looks like a good base indicator of a healthy scale controller as I believe it shows the scale controller monitoring the Event Hub for incoming messages. Here's a screenshot of the Requests metric from my Event Hub namespace dashboard - you can clearly see the increase in the base level of Requests as soon as I rolled out the fix:

Resolution

Changing those appsettings to avoid the multiple levels was the fix. Once I updated them all, including the Connection one (which does not required the %), I then started seeing the Event Hub trigger behaving properly. Via the scale controller logging, I could see no more errors. Waiting for it to log it had changed the instance count down to 0 + a bit of extra time to pass, it then correctly scaled out when I fired an event in. The cold start resulted in about a 10-15 second delay before it picked up the event.

This feels like a workaround and perhaps an issue within the scale controller - given the trigger works and pulls in those appsetting substitutions fine.

Key Takeaways

Use SCALE_CONTROLLER_LOGGING_ENABLED appsetting temporarily to help troubleshoot what is going on
Avoid any issues with appsetting substitution, by keeping them as simple top-level settings - not nested levels
The Requests metrics graph in the Event Hub dashboard is a good indicator of base health

Samsung Gear Sport vs SWIMTAG - Swimathon 2018

2018-04-29T11:51:00.001+01:00

This post is a bit different to my normal ones in that it's not my usual ramblings about something software development related. Instead, it's a cross between a write-up of my taking part in Swimathon 2018, and a side-by-side comparison of how my swim was tracked by 2 different bits of kit: a Samsung Gear Sport and SWIMTAG. For those that sponsored me, this is proof that I held up my side of the deal! :) If you are here for the proof, feel free to scroll down - I won't judge you....much.

Swimathon is an event to raise money for 2 very worthwhile charities: Cancer Research UK and Marie Curie. You pick a swimming challenge to complete from 400m, 1.5km, 2.5km or 5km and raise money by getting sponsors. Simple as that. I signed up to complete 1.5km, which I did on Sunday 29th April, 2018.

Background

In 2017, I took up swimming after picking up a knee injury during my annual 10K road race. Being unable to run without pain left a huge gap in my exercise routine, so I naively thought I'd migrate my running fitness level over to swimming. However, having not swum properly since my school days 20-odd years ago, and never having done lane swimming, it was a wet slap round the face and a bit disheartening when I struggled to complete more than a couple of lengths without feeling like I'd run a fast 5K. It turns out, running fitness doesn't necessarily translate to swimming fitness. There were a number of problems I had:

unrealistic expectation of translating running performance over to swimming
poor technique
going too fast / not wanting to be "that person" who holds everyone up in the lane - the ego factor
no way to track my progress

So I researched into swimming - read articles and watched videos on technique, slowed everything down, got my breathing sorted etc. I'm lucky enough to have a local leisure centre that has SWIMTAG. SWIMTAG tracks your swim and gives you insights into your swim - how many lengths, split times, stroke types, DPS (Distance Per Stroke), tracks your PBs, allows you to enter competitions to compete against others locally, nationally or globally. After a swim, the data is sync'd to your account and then available online or via a mobile app. As a Software Developer stat geek, this is right up my street.

I analyse things.

ALL TEH THINGZ.

I like to see my progress as that drives me on to do more and do better - I'm one for continual improvement and SWIMTAG has been a big part of that. Soon I was making progress; my times were dropping, my distances were increasing. And 9 months after picking up my knee injury, I was also able to get back to running. #Winning!

Samsung Gear Sport - The Chosen One

It's my Jose Mourinho of smartwatches. Earlier this year, I started looking into smart watches/fitness trackers. I went with a Samsung Gear Sport as it ticked the main boxes for what I was looking for:

GPS for tracking runs outside
waterproof to 5ATM (50 metres) with swimming tracking
offline storage for Spotify music
heart rate monitor
slick, responsive UI - TizenOS is great
good battery life (for a smartwatch!)

The Gear Sport is, for me, a great option if you want a watch that has a good combination of smartwatch functionality crossed with fitness tracking. SWIMTAG is dedicated to swim tracking so I'd expect that to be more accurate. In previous swims where I wore one or the other, but not both at the same time, they seemed on a par in terms of accuracy of tracking lengths and stroke types. Both missed the odd length at times. But one huge advantage of the Samsung Gear Sport is the fact it has a visual display, plus can give you feedback via vibration every x lengths - if you're like me and get distracted by thinking about what's for lunch halfway down a length and lose your count, this is a very useful feature. The Samsung display, as you'd expect, is awesome, though I would like a slightly stronger vibration and sometimes during a swim you might not always feel it. There are a few apps you can install to track swims - I used the one as part of Samsung Health, as personally I liked all my fitness activity activity going into one place. I have also tried the Speedo app briefly in the past, which would be another option.

Swimathon 2018 result

I achieved my goal of completing the 1.5km swimming without rests in just under 38 minutes. Despite aspiring to glide through water like a cross between Adam Peaty and a dolphin, I knew that would not be the case - **obviously** only because of the reduced streamlining I introduced by wearing a Gear Sport on one wrist, and a SWIMTAG and locker key on the other ;)

SWIMTAG

tracked all 60 lengths correctly
tracked the stroke type correctly
reported a time of 37m 43s

Samsung Gear Sport

did not track the lengths correctly - it went wonky twice, missing 2 lengths in total. I found this weird, as both times, I'd paused at the end for 5 seconds to let someone pass so it should have been easier if anything, to detect the end of a length. This shows up in the breakdown you get in Samsung Health as 2 length times are about double the rest.
tracked the stroke type correctly
reported a time of 37m 48s

Verdict
The Samsung Gear Sport is a great watch. For those that want the best fitness tracker, I'm sure there are better options that are more fitness focused. In terms of swimming, I was disappointed that it didn't track the full swim accurately. Usually, a brief pause at each end is enough for the tracking to be spot on. Perhaps the Speedo app would have performed differently - unfortunately, I couldn't track in both apps at the same time.

For me, SWIMTAG is my primary source of tracking. As a dedicated swim tracker, I like having all my history in there with PBs tracked, competition leagues and a sense that it should be the most accurate. I like the Gear Sport as a secondary tracker - much better for feedback while you're swimming as opposed to SWIMTAG which is only helpful post-swim.

Getting started with SignalR

2016-10-11T22:56:00.002+01:00

Follows on from previous post: Real-time SignalR and Angular 2 dashboard

I had a couple of questions from someone who forked the project on GitHub and is using it as the basis for their own custom dashboard, which reminded me that there are a few key things I picked up on my learning journey that I should share. These are primarily focused around SignalR aspects.

An instance of a SignalR hub is created for each request

A single instance is not shared across all clients connected to that hub. This is much like a controller in ASP.NET MVC, whereby a new instance of the controller is created for each request. Hence in my SignalRDashboard project, the mechanism for holding a single instance of shared data for all connections, is via a singleton instance that gets passed into each hub's constructor.

Setup callback methods in Javascript for SignalR hubs BEFORE starting the SignalR connection

To open a SignalR connection, you use the following:


    $.connection.hub.start();

To be able to receive messages from the server-side SignalR hub in client-side Javascript, you have to hookup a method to be called. You'd do this using something like:


    var hub = $.connection.myDemoHub;
    hub.client.updateMeWhenYouHaveUpdates = function(message) {
        // Do something useful with what we've just received from the server
    };

The import thing to remember is once you've started the SignalR connection, you can't hook up these callbacks. So make sure they are done BEFORE you start the connection. The correct ordering would be:


    var hub = $.connection.myDemoHub;
    hub.client.updateMeWhenYouHaveUpdates = function(message) {
        // Do something useful with what we've just received from the server
    };
    $.connection.hub.start();

You can connect to multiple SignalR hubs over the same connection

This is great as it means, you can connect to multiple hubs easily - all you need to do is setup the callback methods in Javascript before you open the connection, as per the previous point.


    var hub1 = $.connection.myDemoHub;
    hub1.client.updateMeWhenYouHaveUpdates = function(message) {
        // Do something useful with what we've just received from the server
    };

    var hub2 = $.connection.myOtherHub;
    hub2.client.callMePlease = function(message) {
        // Do something useful with what we've just received from the server
    };
    $.connection.hub.start();

In the SignalRDashboard project, you'll see this being handled in dashboard.js as follows:

each Angular 2 dashboard component registers itself with dashboard.js when it is constructed
once the component has been initialised (ngOnInit event), it lets dashboard.js know that it's finished and registration is completed
once all components have completed registration, then the initialiseComponents() method is called which then calls in to each registered component (each must expose a setupHub() method) so they can set up the callbacks they're interested in from the hubs they use
finally, the connection is then started

The basics are covered well in the official SignalR site, but the points above were some of the main things that I found myself looking a bit deeper for.

Real-time SignalR and Angular 2 dashboard

2016-10-07T09:14:00.001+01:00

tl;dr Check out my new SignalRDashboard GitHub repo
Followup post: Getting started with SignalR

For a while now, I've had that burning curiosity to do more than just the bit of hacking around with SignalR that I'd previously done. I wanted to actually start building something with it. If you're not familiar with it, SignalR is a:

...library for ASP.NET developers that makes developing real-time web functionality easy.

It provides a simple way to have two-way communication between the client and server; the server can push updates/data to any connected client. There are a number of great starting examples out there, but to give a very quick overview, a typical basic example is web-based chat functionality. When a user navigates to the chat page using their browser-of-choice, a connection is made to a SignalR "hub" on the server, using Javascript. This connection provides the channel for bi-directional client-server communication. When a user enters some text in the UI and presses send, a message is sent across this channel to the server, invoking a method on the hub. In the case of a basic chat example, this then broadcasts the message out to all the clients who are connected to that hub, which results in a Javascript method on the client being called to then render that message out to each user's screen.

No need to make AJAX requests to send requests to the server. No need to make AJAX requests to make polling-style requests to the server for updates. SignalR handles the approach used to maintain the communication channel between the client browser and the server, depending on the functionality supported by the browser. It also handles the nuts and bolts of how to broadcast out to any connected clients.

Enter the dashboard

In the dev room I work in, we've had a large screen showing a dashboard of metrics for years - evolving over time as we realise the value of new metrics. I decided that a great learning project to work on in my spare time would be to create a real-time web-based replacement (the original one was a WPF app). I'm a big fan of giving visibility of relevant and timely, high-level application metrics to the business. Current CI build broken? Show it. Recent system error rates? Show it. How many stories do the test team have left to accept? Show it. That ability to take a quick glance, and get a quick feel for the current state of play across a wide area of the business, is invaluable. It's also handy to have things flash and/or play a sound, when there's something of particular importance to flag up.

From a technical perspective, I set myself a number of goals I wanted to achieve:

a centralised dashboard accessible across all areas of the business (it's an ASP.NET MVC 5 web app...job done!)
no matter how many clients are connected to the dashboard, I don't want to increase the load on the systems/services that the dashboard is drawing data from
try out a new (to me) Javascript framework to aid the creation of slick, dynamic views
create an dashboard framework that's easy to extend, with some demo dashboard components so hopefully others interested in the technology can find it useful, and better still, put it to use in their own environments

Moar learning!

AngularJS has also been on my radar for a while, and with Angular 2 coming along as the successor to Angular 1, it seemed like a chance to have a play around with that too. When I first started hacking about with it, an early beta version was available which I started off using. More recently, the Final Release has become available - so I have since upgraded to that, patching up where there were breaking changes.

GitHub repo

The result is a cunningly named (does-what-it-says-on-the-tin) SignalRDashboard project GitHub repo. There's a few demo components as examples, and I plan to add to those as and when I have time. It is still very much work in progress and evolving!

Here's a quick screenshot with some of the demo components currently written (all randomly generated data, refreshing every 15 seconds):

As always, all feedback is welcome - if you do find it useful/end up using it, drop me a message as it would be great to know!

dm_exec_query_plan returning NULL query plan

2014-08-12T16:03:00.004+01:00

I recently hit a scenario (SQL Server 2012 Standard, 11.0.5058) where I was trying to pull out the execution plan for a stored procedure from the plan cache, but the following query was returning a NULL query plan:


SELECT plan_handle,usecounts, cacheobjtype, objtype, size_in_bytes, text, 
    qp.query_plan, tqp.query_plan AS text_query_plan
FROM sys.dm_exec_cached_plans cp
 CROSS APPLY sys.dm_exec_sql_text(plan_handle) t
 CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
 CROSS APPLY sys.dm_exec_text_query_plan(plan_handle, NULL, NULL) tqp
WHERE  text LIKE '%MyStoredProcedure%'
 AND objtype = 'Proc'

Each time I ran the stored procedure, the usecounts was incrementing, but I just could not get the query plan to be returned. Initially I thought I'd found the answer on this blog post:

Getting a NULL query_plan from dm_exec_query_plan, scratched head for a bit, then found this - http://t.co/DGcF2qMvK4 #sqlserver
— Adrian Hills (@AdaTheDev) August 11, 2014

However, dm_exec_text_query_plan also returned NULL for the plan handle so it was a dead end for this scenario. So, a bit more digging around and came across this question on StackOverflow. This was pretty much the scenario I was experiencing - my stored procedure had a conditional statement that wasn't being hit based on the parameters I was supplying to the stored procedure. I temporarily removed the IF condition, ran it again and hey presto, this time an execution plan WAS returned. Re-instating the condition then, sure enough, made it no longer return the plan via `dm_exec_query_plan`. I tried to create a simplified procedure to reproduce it, with multiple conditions inside that weren't all hit, but a query plan was successfully returned when I tested it - so it wasn't as straight forward as just having multiple branches within a procedure.

I was just starting to suspect it was something to do with temporary table jiggery-pokery that was being done within the conditional statement, and trying to create a very simplified repro when...

@AdaTheDev read this one? http://t.co/g7XKObB24E
— Arthur Olcot (@sqlserverrocks) August 12, 2014

This was pretty much exactly the scenario I was hitting. I carried on with my ultra-simplified repro example which shows the full scope/impact of this issue (see below). As noted in the forum post provided above, it's an issue that occurs when using a temp table in this context, but table variables do NOT result in the same behaviour (i.e. testing a switch over to a table variable instead of a temp table sure enough did result in query plan being returned by dm_exec_query_plan ). N.B. It goes without saying, this is not an endorsement for just blindly switching to table variables!


-- 1) Create the simple repro sproc
CREATE PROCEDURE ConditionalPlanTest 
 @Switch INTEGER
AS
BEGIN
 CREATE TABLE #Ids (Id INTEGER PRIMARY KEY)
 DECLARE @Count INTEGER

 IF (@Switch > 0)
  BEGIN  
   INSERT INTO #Ids (Id) VALUES (1)
  END 

 IF (@Switch > 1)
  BEGIN
   INSERT #Ids (Id) VALUES (2)
  END

 SELECT * FROM #Ids
END
GO

-- 2) Run it with a value that does NOT result in all conditions being hit
EXECUTE ConditionalPlanTest 1
GO

-- 3) Check plan cache - no query plan or text query plan will be returned, 
--    usecounts = 1
SELECT plan_handle,usecounts, cacheobjtype, objtype, size_in_bytes, text, 
    qp.query_plan, tqp.query_plan AS text_query_plan
FROM sys.dm_exec_cached_plans cp
 CROSS APPLY sys.dm_exec_sql_text(plan_handle) t
 CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
 CROSS APPLY sys.dm_exec_text_query_plan(plan_handle, NULL, NULL) tqp
WHERE text LIKE '%ConditionalPlanTest%'
 AND objtype = 'Proc'
GO

-- 4) Now run it with a different parameter that hits the 2nd condition
EXECUTE ConditionalPlanTest 2
GO

-- 5) Check the plan cache again - query plan is now returned and 
--    usecounts is now 2.
SELECT plan_handle,usecounts, cacheobjtype, objtype, size_in_bytes, text, 
    qp.query_plan, tqp.query_plan AS text_query_plan
FROM sys.dm_exec_cached_plans cp
 CROSS APPLY sys.dm_exec_sql_text(plan_handle) t
 CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
 CROSS APPLY sys.dm_exec_text_query_plan(plan_handle, NULL, NULL) tqp
WHERE text LIKE '%ConditionalPlanTest%'
 AND objtype = 'Proc'
GO

-- 6) Recompile the sproc
EXECUTE sp_recompile 'ConditionalPlanTest'
GO

-- 7) Confirm nothing in the cache for this sproc
SELECT plan_handle,usecounts, cacheobjtype, objtype, size_in_bytes, text, 
    qp.query_plan, tqp.query_plan AS text_query_plan
FROM sys.dm_exec_cached_plans cp
 CROSS APPLY sys.dm_exec_sql_text(plan_handle) t
 CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
 CROSS APPLY sys.dm_exec_text_query_plan(plan_handle, NULL, NULL) tqp
WHERE text LIKE '%ConditionalPlanTest%'
 AND objtype = 'Proc'
GO

-- 8) This time, run straight away with a parameter that hits ALL conditions
EXECUTE ConditionalPlanTest 2
GO

-- 9) Check the plan cache again - query plan is returned and usecounts=1.
SELECT plan_handle,usecounts, cacheobjtype, objtype, size_in_bytes, text, 
    qp.query_plan, tqp.query_plan AS text_query_plan
FROM sys.dm_exec_cached_plans cp
 CROSS APPLY sys.dm_exec_sql_text(plan_handle) t
 CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
 CROSS APPLY sys.dm_exec_text_query_plan(plan_handle, NULL, NULL) tqp
WHERE text LIKE '%ConditionalPlanTest%'
 AND objtype = 'Proc'
GO

-- 10) Now change the sproc to switch from temp table to table variable
ALTER PROCEDURE ConditionalPlanTest 
 @Switch INTEGER
AS
BEGIN
 DECLARE @Ids TABLE (Id INTEGER PRIMARY KEY)
 DECLARE @Count INTEGER

 IF (@Switch > 0)
  BEGIN  
   INSERT INTO @Ids (Id) VALUES (1)
  END 

 IF (@Switch > 1)
  BEGIN
   INSERT @Ids (Id) VALUES (2)
  END

 SELECT * FROM @Ids
END
GO

-- 11) Execute the sproc with the parameter that does NOT hit all the conditions
EXECUTE ConditionalPlanTest 1
GO

-- 12) Check the plan cache - query plan is returned, usecounts=1
SELECT plan_handle,usecounts, cacheobjtype, objtype, size_in_bytes, text, qp.query_plan, 
    tqp.query_plan AS text_query_plan
FROM sys.dm_exec_cached_plans cp
 CROSS APPLY sys.dm_exec_sql_text(plan_handle) t
 CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
 CROSS APPLY sys.dm_exec_text_query_plan(plan_handle, NULL, NULL) tqp
WHERE text LIKE '%ConditionalPlanTest%'
 AND objtype = 'Proc'
GO

-- 13) CLEANUP
DROP PROCEDURE ConditionalPlanTest
GO

70-486 Developing ASP.NET MVC 4 Web Applications

2013-09-19T09:59:00.001+01:00

Last week I passed the 70-486 Microsoft exam - Developing ASP.NET MVC 4 Web Applications, so thought I'd knock up a quick post on my experience and what materials I found useful as part of my preparation.

Going into this exam, I had just over a year and a half's commercial experience using ASP.NET MVC 2 & 3. Before that, I had experience in ASP.NET and prior to that, when dinosaurs still roamed, classic ASP. It's no secret I'm a bit of a data nerd (a lot of my blog is db related, SQL Server, MongoDB...) and I have tended to be backend focused, but I wanted to even out the balance a bit by pushing myself in this area, and in JS/HTML5/CSS3. I took the opportunity to upgrade the web solution at my current company from MVC 3 to MVC 4, and being able to do that at the start of my preparation was really useful - this is how I like to learn, by actually "getting stuff done". That is the obvious, number 1 recommendation - do not just read and swot up on the theory, actually "do". My brain likes me to be stupid and make practical mistakes - when I then work out how I've done something daft, it reinforces the learning and makes the knowledge stick.

Preparation

The first thing I was disappointed to find was that there is (as of time of writing) no Microsoft Exam prep book for this exam. There is one due out I believe at the beginning of October 2013 - Exam Ref 70-486 : Developing ASP.NET MVC 4 Web Applications by William Penberthy (ISBN-10: 0735677220 | ISBN-13: 978-0735677227). So I obviously can't comment on how good that book is. The book I went with was Professional ASP.NET MVC 4 from Wrox, by Jon Galloway (Twitter), Phil Haack (Twitter), K. Scott Allen (Twitter) and foreword by Scott Hanselman (Twitter). While the book alone isn't enough for the exam, for me it gave a good coverage on quite a few areas I needed so it is definitely worth a read.

Having a Pluralsight subscription was good and while not necessarily geared towards the exam, you can never go wrong with a bit of Pluralsight training. I went through a number of the MVC courses - ASP.NET MVC Fundamentals, ASP.NET MVC 2.0 Fundamentals, MVC 4 Fundamentals and Building Applications with MVC 4. One of the things I like about Pluralsight is you can control the playback speed so for most of the stuff I already knew, I glossed over at a faster speed. I also went through the "Building Web Apps with ASP.NET Jump Start" training on the Microsoft Virtual Academy (Scott Hanselman, Jon Galloway and Damian Edwards (Twitter)) - that was quite entertaining!

I found some great study guide blog posts that collated together a lot of links to some good material:

These give a lot of useful links to MSDN articles, MS resources, blogs, interesting StackOverflow questions etc.

Last but definitely not least, there was my practical setup - Windows 8 on a VM, with Visual Studio 2012 and a Windows Azure account. To re-iterate what I said before - the best way to learn, is to do...and make daft mistakes. I started work on a new web app from scratch, with a real-world mindset on (i.e. writing production-worthy code) putting to good use the new things I was learning. I also had a scratch-pad web app where I would just dump rough code to try out short, simple snippets.

Bottom line

Overall, I put a lot of time and effort into preparation for this exam and it paid off. The biggest benefit for me is what I learned along the way and the challenge it gave me.

SQL Server 2008 R2 in-place upgrade error

2013-06-05T21:18:00.000+01:00

Today I encountered the following error during the process of performing an in-place upgrade of a SQL Server 2008 instance to 2008 R2:

The specified user 'someuser@somedomain.local' does not exist

I wasn't initially sure what that related to - I hadn't specified that account during the upgrade process so I went looking in the Services managemement console. The SQL Server service for the instance I was upgrading was configured to "Log On As" that account and it had been happily running prior to the upgrade.

This domain account did exist but to sanity check, the first thing I did was switched the service to use another domain account that also definitely existed and retried the Repair process. Same error. Then I spotted further down the list, another service had specified an account in the "somedomain\someuser" form. So I switched the SQL Server instance service to specify the account in that form (Down-Level Logon Name) instead of the UPN (User Principal Name) format (reference).

Bingo.

While I was waiting for the Repair process to run through again, I carried on searching and found this question on the MSDN Forums. The very last answer there confirmed it. The SQL Server 2008 R2 upgrade does NOT like user accounts in the UPN format.

.NET Project File Analyser

2013-03-21T09:53:00.001+00:00

I've started knocking together a little app to automate the process of trawling through a folder structure and checking .NET project files (C# .csproj currently) to extract some info out of them. The DotNetProjectFileAnalyser repo is up on GitHub. I've been working against Visual Studio 2010 project files, but could well work for other versions assuming the project file structure is the same for the elements it currently looks at - I just haven't tried as yet.
Currently, it will generate an output file detailing for each .csproj file it finds:

Build output directory (relative and absolute) for the configuration/platform specified (e.g. Debug AnyCpu). Useful if you want find which projects you need to change to build to a central/common build directory.
List of all Project Reference dependencies (as opposed to assembly references). Useful if you want to find the projects that have Project References so you can switch them to assembly references

Usage


DotNetProjectFileAnalyser.exe {RootDirectory} {Configuration} {Path}

{RootDirectory} = start directory to trawl for .csproj files (including subdirectories)
{Configuration} = as defined in VS, e.g. Debug, Release
{Platform} = as defined in VS, e.g. AnyCpu

Example:


DotNetProjectFileAnalyser.exe "C:\src\" "Debug" "AnyCpu"

More stuff will go in over time, with ability to automatically update csproj files as well to save a lot of manual effort.

GB Post Code Importer Conversion Accuracy Fix

2013-03-13T23:09:00.001+00:00

In a post last year (Ordnance Survey Data Importer Coordinate Conversion Accuracy) I looked into an accuracy issue with the conversion process within the GeoCoordConversion DLL that I use in this project (blog post). Bottom line, was that it was a minor with an average inaccuracy of around 2.5 metres and a max of ~130 metres by my reckoning. I've since had a few requests asking if I can supply an updated GeoCoordConversion DLL with fixes to the calculations.

After getting in contact with the owner of the GeoCoordConversion project, they've kindly added me as a committer. I've now pushed the fixes up to it, rebuilt the DLL (now v1.0.1.0) and pushed up the latest DLL to the Ordnance Survey Importer project on GitHub.

SQL Server Table Designer Bug With Filtered Unique Index

2013-03-08T13:11:00.001+00:00

A colleague was getting a duplicate key error when trying to add a new column to a table via the Table Designer in SQL Server Management Studio (2008R2 - not tested on other versions), despite there being no violating data in the table. After a bit of digging around, I tracked the problem down to what appears to be a bug in Table Designer when there is a unique, filtered index in place on the table and the table is being recreated (i.e. you're adding a new column, but not at the end after all the existing columns).

Steps to reproduce

In SSMS in Tools -> Options -> Designers -> Table and Database Designers, uncheck the "Prevent saving changes that require table re-creation" option

Create table:


CREATE TABLE [dbo].[Test]
(
Column1 INTEGER NOT NULL,
Column2 INTEGER NOT NULL,
Column3 INTEGER NULL
);

Create dummy data:


INSERT dbo.Test(Column1, Column2, Column3) VALUES (1, 1, 1);
INSERT dbo.Test(Column1, Column2, Column3) VALUES (1, 1, NULL); -- OK as Column3 is NULL

Now run the following, duplicate key error is correctly thrown:
```
-- Errors, Duplicate Key exception as expected
INSERT dbo.Test(Column1, Column2, Column3) VALUES (1, 1, 2); 
```
So at this point we have 2 rows in the table, no violations of the unique filtered index.
Right click the table in SSMS -> Design
Insert a new column "Column4" before Column3 and the press Save.

The error that occurs is:


'Test' table
- Unable to create index 'IX_Test_Column1_Column2'.  
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.Test' and the index name 'IX_Test_Column1_Column2'. The duplicate key value is (1, 1).
The statement has been terminated.

So what it appears to be doing, is losing the WHERE filter on the index. This can be confirmed by clicking "Generate change script" in the Table Designer instead of Save - at the end of the generated script:


CREATE UNIQUE NONCLUSTERED INDEX IX_Test_Column1_Column2 ON dbo.Test
 (
 Column1,
 Column2
 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Now, if there was no data in the table, or there were no rows with the same Column1 and Column2 value combination when you go into Table Designer, then you can save the table change and be blissfully unaware that the filter has been lost from the index. i.e. repeat the repro steps again, but this time move step 3 and 4 (insert dummy data) to the end of the process. The previously OK 2nd data row will now error upon insert.

Personally, I almost never use the Table Designer and as a safeguard, will be recommending to the rest of the team that the "Prevent saving changes that require table re-creation" option is checked as a basic guard.

Update:The following Connect items relating to this issue:
The filter expression of a filtered index is lost when a table is modified by the table designer
2008 RTM, SSMS/Engine: Table designer doesn't script WHERE clause in filtered indexes
Referring to comments in that 2nd item, my "Script for server version" setting was set to "SQL Server 2008 R2".

Sounds like this may have been addressed in SQL 2012, but still a problem in 2008/2008R2.

MongoDB ASP.NET Session Store Provider v1.1.0

2013-03-03T22:05:00.000+00:00

Since I created the MongoDB ASP.NET Session State Store Provider (v1.0.0), a few things have moved on in the MongoDB C# Driver. I've pushed a number of changes up to the project on GitHub (which I've incremented to v1.1.0), so it now uses v1.7.0.4714 of the driver. There is no change to way it is configured in web.config, so if you are using v1.0.0 of my provider it should be painless. Of course, I'd recommend thorough testing first :)

The changes relate to:

SafeMode is obsolete. I've replaced with WriteConcern.
C# Driver Jira case
WriteConcern C# Driver reference
MongoServer.Create is obsolete. I've replaced with MongoClient.GetServer.
MongoClient C# Driver reference
QueryComplete is obsolete.

web.config recap
These web.config settings have not changed, so should continue working as before.


<configuration>
  <connectionStrings>
    <add name="MongoSessionServices" connectionString="mongodb://localhost" />
  </connectionStrings>
  <system.web>
    <sessionState
        mode="Custom"
        customProvider="MongoSessionStateProvider">
      <providers>
        <add name="MongoSessionStateProvider"
             type="MongoSessionStateStore.MongoSessionStateStore"
             connectionStringName="MongoSessionServices"
             writeExceptionsToEventLog="false"
             fsync="false"
             replicasToWrite="0" />
      </providers>
    </sessionState>
  </system.web>
</configuration>

replicasToWrite is interpreted as follows: < 0 = ignored. Treated as 0.
0 = will wait for acknowledgement from primary node.
> 0 = will wait for writes to be acknowledged from (1 + {replicasToWrite}) nodes.

Please Note, per the MongoDB Driver docs, if (1 + {replicasToWrite}) equates to a number greater than the number of replica set members that hold data, then MongoDB waits for the non-existent members to become available (so blocks indefinitely).

It still treats write concerns as important, ensuring it waits for acknowledgement back from at least one MongoDB node.

As always, all feedback welcome.

Importing twitter archive into ElasticSearch

2013-02-10T19:53:00.000+00:00

I recently downloaded my full history of tweets from Twitter (via the "Request your archive" button on the Settings page), which gives the data in CSV and JSON formats. The download comes with a simple web UI so you can browse locally, all your historical tweets, as well as perform basic searches. When I tried performing a wildcard search, it resulted in an unresponsive script warning and nobbled the browser tab.

Cue ElasticSearch

I've kept meaning to have another hack around with ElasticSearch for a while now, as I started looking into it in 2011 and was very impressed with it. So I decided to knock up a little .NET app to take the .js files provided by Twitter in the export, extract the tweet data and push into ElasticSearch. It seems like a good little starter project to learn more about ElasticSearch and actually could give me some useful insights into all the tweets I've made. I'm sure I'm going to find some really good links I tweeted and then couldn't find again!

At the moment there's a very basic GUI (and I mean basic!) exposing the functionality to import the tweet data into ElasticSearch. I plan to stick a basic search GUI together too and then...well, I'm not quite sure yet, but I'm sure as time goes on I'll see more ways to extract interesting information from my tweet history.

The ElasticTweets project as I've called it, can be found on GitHub here - feel free to use and abuse it, and let me know how you get on!

Configuring Azure Storage Emulator SQL Server Instance

2012-05-28T10:20:00.002+01:00

If you're using Windows Azure Storage, you are almost certainly going to be running the storage emulator during development, instead of working directly against your storage account up in the cloud. This emulator (which comes in the Windows Azure SDK - see the "other" category), allows you to test locally against local instances of Blob, Queue and Table services.

As per the Overview of Running a Windows Azure Application with Storage Emulator reference, the emulator needs a local SQL Server instance. By default, it's configured to run against a SQL Server Express 2005 or 2008 database.

If you want to point it at a different instance of SQL Server, for example a shared development database server, you can do this using the DSInit command line tool. I originally came across this MSDN on How to Configure SQL Server for the Storage Emulator, which led me to try the following in the Windows Azure Command Prompt:

DSInit /sqlInstance:MyDevServerName\MyInstanceName

This tried to create the storage database, but failed with the following:

Creating database DevelopmentStorageDb20110816...
Cannot create database 'DevelopmentStorageDb20110816' : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

One or more initialization actions have failed. Resolve these errors before attempting to run the storage emulator again. These errors can occur if SQL Server was installed by someone other than the current user. Please refer to http://go.microsoft.com/fwlink/?LinkID=205140 for more details.

The correct way when trying to use a different database server instead of the local machine, is to use the SERVER switch instead:

DSInit /SERVER:MyDevServerName\MyInstanceName

See the full DSInit Command-Line Tool reference.

When you then run the Storage Emulator, it will target that database server/instance. You can easily cleardown/reset that database by right clicking the Windows Azure Emulator icon in the taskbar, select "Show Storage Emulator UI" and the click "Reset". NB. Just to be clear, this will delete everything in your local storage emulator database.

An added "gotcha" to watch out for, if you have the storage account connection stored in a web/app.config and want to specify to use the local emulated instance, you need to use

UseDevelopmentStorage=true

exactly as it appears here. If like me, you initially give an ending semi-colon, you will get a FormatException stating with the error: "Invalid account string".

Error Creating Azure Blob Storage Container

2012-05-24T09:19:00.001+01:00

I received a rather...vague...error when trying out a bit of .NET code to connect to a Windows Azure Blob Storage account, and create a new container in which to store some blobs.

The code


CloudStorageAccount storageAccount = CloudStorageAccount.Parse(
    "DefaultEndpointsProtocol=https;AccountName=REMOVED;AccountKey=REMOVED");

CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient();
            
CloudBlobContainer container = 
    blobClient.GetContainerReference("TestContainer1");

container.CreateIfNotExist(); // This error'd

The error

A StorageClientException was thrown saying "One of the request inputs is out of range.". An inner WebException showed that "The remote server returned an error: (400) Bad Request."

The cause

I'd assumed (incorrectly) that a container name could be pretty much any string. But that's not the case. As per this MSDN reference, a container name must:

be in lowercase (this was the cause in my case)
start with a letter or a number and only contain letters, numbers and hyphens (multiple consecutive hyphens are not allowed)
be between 3 and 63 characters long

The "Naming and Referencing Containers, Blobs and Metadata" reference is worth a bookmark.

ASP.NET MVC Performance Profiling

2012-05-23T09:33:00.000+01:00

Building up a profile of how a web application functions, all the database interactions that take place and where the server-side time is spent during a request can be a challenging task when you are new to an existing codebase. If you're trying to address the generic/non-specific "we need to improve performance / it's slow" issue, you need to get a good picture of what is going on and where to prioritise effort.

There are a number of ways to identify specific problems depending on the technologies. For example, if your application is backed by SQL Server, you can query a set of DMVs to identify the top n worst performing queries and then focus your effort on tuning those. Identifying a badly performing query and tuning it can obviously yield huge benefits in terms of the end user's experience. But this doesn't necessarily flag up all the problems. If you are looking at a particular page/view within the application, then you could start a SQL Profiler trace to monitor what's going on during the lifecycle of the request - this is another common and valuable tool to use. Personally, I usually have SQL Profiler open most of the time during development. If you're developing against a shared dev database with others, you can filter out other people's events from the trace - injecting your machine name into the connection string as the ApplicationName, and then filtering on this is one of a number of ways to achieve this which works nicely.

MiniProfiler For The Win

Recently, I've started using another extremely valuable tool within an ASP.NET MVC solution - MiniProfiler which is (quote):

A simple but effective mini-profiler for ASP.NET MVC and ASP.NET

It was developed by the team over at StackOverflow. Simply put, it can render performance statistics on the page you are viewing that detail where the time was spent server-side, fulfilling that request. But the key thing for me, is it provides an ADO.NET profiler. Say you're using LINQ-to-SQL - by wrapping the SqlConnection in a ProfiledDbConnection before then passing it to the constructor of a DataContext, info on the SQL queries executed within the lifetime of a request are then also included in the statistics displayed. (It can also profile calls via raw ADO.NET / Entity Framework etc, minimal effort required).

Make the invisible, visible

Since integrating this into an MVC application, the benefits have been priceless. The key thing for me is: VISIBILITY. It provides extremely value visibility of what is happening under the covers. Going back to the start of this post, if you're new to a codebase, then having this information provided to you as you browse is invaluable. It enables you to identify problems at a glance, and increases visibility of problems to other developers so the "life expectancy" of those problems is lower - they're a lot less likely to hover undetected just under the radar if the information is being pushed right in front of the developer on screen. It also helps you build up a picture of how things hang together.

MiniProfiler includes functionality to flag up N+1 and duplicate queries, a common potential problem you could encounter with ORMs if you're not careful. If a view were performing 100 low hitting queries, these may not show themselves as queries to be tuned. But the fact that 100 database roundtrips are being made, could scream out that perhaps they could be replaced with a single roundtrip and a performance improvement gained there.

I'm now a big fan of MiniProfiler, especially due to it's simplicity to integrate into a codebase. Working on ASP.NET MVC/ASP.NET applications? You might want to give it a try!

Trust your developer instinct and experience

2012-04-13T11:59:00.000+01:00

That feature you're implementing, that bug you're fixing, that performance improvement you're trying to make...it's using up a fair few brain CPU cycles trying to analyse how best to implement it. Scalability, performance, ease of maintenance, clean code...just some of the things you are mentally scoring your potential approaches against to determine which route to take. And that's without any mention of business domain logic. We've all been in the situation where you start heading down one path only to find a hurdle. It's OK though, you're an athlete (very debatable in my case) - you can jump over that hurdle. So you do. But then you find a 7 foot high wall with barbed wire on top. No problem - you have a ladder and wire cutters; it's a bit unwieldy carrying them around with you everywhere, but hey - over you go. All good. Until you come across Chuck Norris, who is angry about that "Yo' mamma.." joke you made about his mother. That wall you climbed...that was protecting his private property. Your ladder? Oh, that's still on the outside of the wall - you couldn't pull it over with you so you jumped down without it. Brace for pain.

Those voices in your head

There's always unexpected problems and hurdles that crop up. It's all part of being a developer after all. But maybe at the point you got your ladder and wire cutters out to climb over that wall, you heard a little voice inside say "Hmm, this doesn't quite feel right" or perhaps you just felt something niggling away with you about the approach. That voice, your instinct and experience as a developer built up over your career, is worth listening to. When it pipes up, sit back and take 5 to just take a bird's eye view.

Trust that instinct and experience - it's usually right. At the very least, it's highlighting the need to at least re-justify to yourself that you are heading down the right path.

Ordnance Survey Data Importer Coordinate Conversion Accuracy

2012-03-14T22:11:00.000+00:00

Update 13 March 2013: Please see latest blog post (fix) on this here.

Thanks to a comment on my original post around my project that imports Ordnance Survey CodePoint data into SQL Server, I was made aware of a potential issue with the (awesome) third party GeoCoordConversion DLL I use to convert the Eastings/Northings coordinates supplied in the Ordnance Survey data files, into Latitude/Longitude coordinates. The issue relates to an inaccuracy in the conversion process, specifically to do with integer divisions instead of double.

So, this blog post is to document my analysis of that and try to quantify what, if any, impact there is on the accuracy of the conversion of Eastings/Northings to Latitude/Longitude. So what I've done is:

Import the Ordnance Survey data set using the released version (1.0.0.0) of the GeoCoordConversion library (i.e. the version my project uses)
Downloaded the source code for GeoCoordConversion and corrected the parts of the calculations where the raised issue noted an inaccuracy
Reran the data import into a separate table using the "corrected" conversion code
Ran the following query to try and quantify how much of a difference it actually makes:


;WITH CTEDiff AS 
(
    SELECT orig.OutwardCode, orig.InwardCode,
        orig.GeoLocation.STDistance(new.GeoLocation) Dist
    FROM PostCodeData orig
 JOIN PostCodeDataWithFix new ON orig.InwardCode = new.InwardCode 
            AND orig.OutwardCode = new.OutwardCode
)
SELECT MIN(Dist) AS MinDifferenceInMetres, 
    MAX(Dist) AS MaxDifferenceInMetres, 
    AVG(Dist) AS AverageDifferenceInMetres, 
    SUM(CASE WHEN Dist = 0 THEN 0 ELSE 1 END) AS NumberOfDifferences
FROM CTEDiff

Results

The MAXIMUM distance between the 2 conversions for a given postcode is: 124.9 metres.
The MINIMUM distance between the 2 conversions for a given postcode is: 0 metres.
The AVERAGE distance between the 2 conversions for a given postcode is: 2.5 metres.
The TOTAL NUMBER of differences was 1,652,458 (out of 1,705,177 conversions).

This map shows the case that had the biggest difference (postcode: NR34 8JW). Plotted points are as follows:
(A) = position from conversion process before fix
(B) = position from conversion process with fix
(C) = position Google Maps gives for location
Note (B) and (C) are in the same spot, so that's why you can't see both!

View Larger Map

Summary

For me, and what I see as potential uses for this data, I personally don't see this as much of an issue especially with an average difference of 2.5 metres. I will add a comment on to the GeoCoordConversion project issue page, referencing this post. Though there doesn't look to be any activity on the project, so whether a fix can be applied is yet to be seen. That's my preferred route at present, to see if the author of that project is around to apply a fix as I currently don't have full knowledge of what I changes I can make to the source code and what would be acceptable in order to distribute the amended source code as part of my project (my project is released under a different, but compatible license...but things might get murkier when it comes to amending/distributing those amendments). In a nutshell, I just don't have time to try to make head and tail of what I can legitimately do (and I don't want to change the license for my project)!

All feedback welcomed

Quick win - check your table variable use

2012-03-05T09:30:00.000+00:00

Quick wins are awesome. Making a change that takes minimal effort and yields a significant performance improvement is very satisfying.

This particular potential quick win relates to the use of table variables vs. temporary tables. Have a non-trivial stored procedure that produces some intermediary results and stores in a table variable which then goes on to be used further in the stored procedure? Consider evaluating a switch to a temporary table instead. This very quick change, can give a great performance boost for such little effort due to the points outlined on MSDN (quote):

table variables are not supported in the SQL Server optimizer's cost-based reasoning model. Therefore, they should not be used when cost-based choices are required to achieve an efficient query plan. Temporary tables are preferred when cost-based choices are required. This typically includes queries with joins, parallelism decisions, and index selection choices.

Queries that modify table variables do not generate parallel query execution plans. Performance can be affected when very large table variables, or table variables in complex queries, are modified. In these situations, consider using temporary tables instead......Queries that read table variables without modifying them can still be parallelized.

Indexes cannot be created explicitly on table variables, and no statistics are kept on table variables. In some cases, performance may improve by using temporary tables instead, which support indexes and statistics.

I've seen cases where this alone, with no other modifications, results in noticeable better performance. This could then be built on further by adding supporting indices. Of course, you should be considering whether you need temporary tables/table variables at all in your stored procedure. If you can remove the need entirely, then look to do that.

Automating Web Performance Stats Collection in .NET

2012-02-20T21:36:00.000+00:00

You have a web application. You're a .NET developer. Maybe you already have some automated UI testing in place via Selenium, or maybe you don't. What you want to do is automate the collection of some performance metrics about your application.

Q. How would you go about doing that in .NET?
A. Use the following recipe for success.

Ingredients

BrowserMob Proxy by Webmetrics, which (quote) is:
A free utility to help web developers watch and manipulate network traffic from their web applications
Selenium, which (quote):
automates browsers. That's it.
BrowserMob Proxy .NET Library, a .NET library to provide a simple way to work with BrowserMob Proxy and Selenium, written by David Burns (blog | twitter) and myself (you're already on my blog | twitter).

Preparation

Download the BrowserMob Proxy from GitHub
Download the BrowserMob Proxy .NET Library from GitHub (binaries, or get the source and build yourself)
Reference Selenium in your .NET project (available via nuget, or from seleniumhq)
Reference the BrowserMob Proxy .NET Library in your .NET project

Make and Bake

Example:


using AutomatedTester.BrowserMob.HAR;
using OpenQA.Selenium;
using OpenQA.Selenium.Firefox;

namespace AutomatedTester.BrowserMob.Example
{
    public class ExampleClass
    {
        public void ExampleUse()
        {
            // Supply the path to the Browsermob Proxy batch file
            Server server = new Server(@"C:\BrowserMobProxy\bin\browsermob-proxy.bat");
            server.Start();

            Client client = server.CreateProxy();
            client.NewHar("google");

            var seleniumProxy = new Proxy { HttpProxy = client.SeleniumProxy };
            var profile = new FirefoxProfile();
            
            profile.SetProxyPreferences(seleniumProxy);
            // Navigate to the page to retrieve performance stats for
            IWebDriver driver = new FirefoxDriver(profile);
            driver.Navigate().GoToUrl("http://www.google.co.uk");

            // Get the performance stats
            HarResult harData = client.GetHar();           
 
            // Do whatever you want with the metrics here. Easy to persist 
            // out to a data store for ongoing metrics over time.
            
            driver.Quit();
            client.Close();
            server.Stop();
        }   
    }
}

What's great is that if you already have some Selenium tests in place, you can add in the collection of performance metrics quickly and easily. This gives you the ability to collate performance metrics over time - a perfect way to identify problem areas to investigate and to quantify performance improvements you make. To learn more about what is in the performance data, check out these links which go into more detail about the HAR format (HTTP Archive) - this is what Webmetric's BrowserMob Proxy returns, which we expand out into a POCO structure (HarResult type).

BrowserMob Proxy allows you to do some pretty funky stuff, such as:

blacklisting / whitelisting content
simulate network traffic / latency

The .NET library wrapper we've made available supports this functionality. Hopefully, this will come in useful for those in the .NET world!

Excluding nodes from XML data before returning from SQL Server

2012-01-16T10:36:00.000+00:00

This post follows on from a question I recently replied to, for how to exclude a specific node from an XML column value before returning it, using TSQL.

Example setup


CREATE TABLE Example
(
ID INTEGER IDENTITY(1,1) PRIMARY KEY,
XmlField XML
)

INSERT Example (XmlField) VALUES ('<Root><ChildA>Value I want to see</ChildA><ChildB>Value I do not want to see</ChildB></Root>')

So if you want to return the XML minus the ChildB node, how do you do it?

Modify it

Literally, using the modify method that is supported on the XML data type. To quote MSDN, modify:

Modifies the contents of an XML document. Use this method to modify the content of an xml type variable or column. This method takes an XML DML statement to insert, update, or delete nodes from XML data. The modify() method of the xml data type can only be used in the SET clause of an UPDATE statement.

That last statement, to me at least, seems slightly open for misinterpretation as strictly speaking it isn't only valid in an UPDATE statement - you can use it as part of a standalone SET statement on an XML variable (though yes, to UPDATE that variable).

So, while you can't do something like this:


SELECT XmlField.modify('delete /Root/ChildB')
FROM Example
WHERE ID = 1

as it results in the following error:


Msg 8137, Level 16, State 1, Line 1
Incorrect use of the XML data type method 'modify'. A non-mutator method is expected in this context.

you can instead do this:


DECLARE @xml XML
SELECT @xml = XmlField
FROM Example
WHERE ID = 1

-- Delete the node from the XML in the variable
SET @xml.modify('delete /Root/ChildB')

SELECT @xml

That does the trick.

Dealing with multiple rows

If you wanted to do something like this from the database side when returning multiple rows then you'd need to either:

create a scalar UDF to perform the removal. e.g.
```
CREATE FUNCTION dbo.RemoveNode(@xml XML)
 RETURNS XML
AS
BEGIN
 SET @xml.modify('delete /Root/ChildB')
 RETURN @xml
END
GO

SELECT dbo.RemoveNode(XmlField) AS XmlField
FROM Example
```
Note the parameter to the modify method must be a literal string, so you can't parameterise the UDF to make it a bit more generic by using that parameter in the call to modify().
SELECT the xml values into a temp table/table variable, do an UPDATE like this:
```
UPDATE #Temp
SET XmlField.modify('delete /Root/ChildB')
```
and then SELECT from the temp table/table variable.
Use SQLCLR

Of course, the typical question should be asked - do you need to do this on the database side, or can you return the XML as-is from SQL Server and modify it accordingly in your calling code? In the original case acting on a single XML value, it's OK but when you start looking into scalar UDF's etc for multi-row requirements then you should be careful to check performance.

Round up of 2011

2012-01-02T22:32:00.000+00:00

Another year is done and dusted so it's that time again - time to reflect on the past year.

Quick Timeline

March: Mongo UK - London
A chance to attend my first NOSQL conference, having recently entered in the world of MongoDB for some projects. Picked up some good pointers and food-for-thought.
Just some words: NOSQL, hard seats, gonna-need-a-bigger-venue, enlightening.
April: SQLBits 8 - Beside The Seaside - Brighton
Highlight of the year for me, conference-wise (was unable to make SQLBits 9 - Liverpool). Fantastic venue, truly awesome speakers/technical content and a really great crowd of people - a true community. Out of all conferences I've gone to over the years, SQLBits never fails to stand out and this one took things to a whole new level.
Just some words: Crappy code, community, awesome, invaluable, bean bags.
April: Microsoft Community Contributor Award
I really enjoy chipping in on sites like StackOverflow and the MSDN forums, and this was a nice (humbling) surprise when I got the email to say I'd received this acknowledgement (blog).
Just some words: Surprise
July: Job Change
After a great 4 and a half years, it was time to move on to a new role. Took the opportunity to work with fellow SQLSoton attendee Matt Whitfield (twitter) - who by the way, has some great (FREE) SQL Server tools which can be found here.
Just some words: New challenge, acquisition, end of an era.
September: Mongo UK - London
A chance to brush up on the latest in the world of MongoDB after a few months away from it.
Just some words: NOSQL, comfy seats, information gems, lego man USB stick.
Monthly: SQLSoton User Group
A really good year of UserGroup meets down here in Southampton every month. Thanks to Mark Pryce-Maher (twitter) for his time and effort, and all the speakers who have taken the time to give a talk. From the first meeting just over a year a go - a small group, in a tiny side room, with no projector - to now - a larger crowd, in a bigger hall, with a projector - it's obvious SQLSoton is providing a valuable SQL Server community service to the Southampton/South Coast area. Check out all the UK SQL Server UserGroups over at SQLServerFAQ.
Just some words: Community, pizza, swag, network.

Top 3 Blog Posts By Views

* OK, so that's the top 4, not the top 3. But that's because I suspect #3 is actually skewed by people looking for "an entirely different kind of site"...I know I had to be careful which links to click when googling for Cassandra (as in the NOSQL database, obviously) and .NET!

Open Source Projects
I started on a few little side projects, which I stuck up on to GitHub for the world to see and (hopefully) use. First came a .NET app to load some of the freely available geographical datasets from Ordnance Survey into SQL Server (GitHub) which relates to blog post #4 above. Next came a MongoDB-backed ASP.NET session state store (GitHub). It was a lot of fun working on these, even though they are pretty trivial things, and was great to actually get some nice feedback from people who found and started using them.

Bottom Line
2011 was a good year. A lot of learning and new challenges meant I was kept on my toes. Looking back though, there are definitely things I'm not pleased about. I dropped the blogging ball in the second half of the year - activity pretty much flatlined for various reasons. So I'm disappointed about that. To quote a tweet by John Sansom (twitter)...

blogging is like going to the gym. Once you fall out of a routine it's tougher to get back into your rhythm

True that. Looking forward to 2012!

OS CodePoint Data Geography Update Sept 2011

2011-09-15T21:44:00.000+01:00

History:
GB Post Code Geographic Data Load to SQL Server using .NET
OS CodePoint Data Geography Load Update

Following a comment on my original post listed above, it appears the structure of the CodePoint data file has changed. This means that the importer will fail when attempting to import the latest data files supplied by Ordnance Survey as the columns in the files have changed.

I've pushed an update to the project on GitHub to address this - it now takes a sensible approach so the index positions are no longer hard coded. Instead, the CodePoint importer now takes an extra argument - the path to the CSV file that contains the column header listing as supplied in the supporting docs in the download. The column indices we are interested in (for Easting, Northing and PostCode) are worked out from this. Obviously makes it a bit more future proof, so if the structure changes again in the future, it should pick up on that automatically.

This is an update to the CodePoint import only at the moment as I haven't had time to check the Scale Gazetteer import as yet. See the project/readme on GitHub for more info.

Getting Started with Riak and .NET

2011-08-18T21:44:00.001+01:00

Earlier in the year, I started playing around with MongoDB using .NET and wrote up a Getting Started guide. Now it's Riak's turn.

Nutshell
Riak is a distributed, schemaless, data-type agnostic key-value "NoSQL" database written primarily in Erlang.

Check out the "What is Riak?" page on Basho's wiki.

Riak does not run on Windows so you'll need to choose your supported OS of choice to install Riak on. Having used Ubuntu before for MongoDB, I went down that route so my notes here are oriented that way. So, after getting a Ubuntu VM set up...

Step 1
Install the latest custom .deb package, as per the Basho guide.

Step 2
Before you start Riak up, you need to tweak the configuration in Riak's app.config file. Follow the Basic Cluster Setup guide, but note that when you update the IP address for the http interface per those instructions, also make the same change to the pb_ip config value - this is for the Protocol Buffers interface. For performance, I wanted to use the Protocol Buffers interface instead of the HTTP REST API and had some initial problems as the pb_ip configuration was still set to 127.0.0.1.

You then need to edit vm.args, per the setup guide.

Step 3
Now you're ready to fire up Riak. Run:

$ riak start

Then make sure all is well using:

$ riak ping

If all is well, it will respond with "pong".

Step 4
Now, back in the comfort of Windows (my Linux-fu is seriously lacking) we're ready to get going. There is no official .NET client library at time of writing, but Basho list a few community contributed projects. When I first looked, only 2 were listed and these appeared to be inactive with no updates since last year. It was a bit disappointing to see the lack of support for those of us in the .NET world. I then discovered CorrugatedIron, very hot-of-the-press, under development by OJ Reeves (Twitter | Blog) and Jeremiah Peschka (Twitter | Blog).

The simplest way to get everything you need to get going is via NuGet.

Nu-what?

NuGet. It's a Visual Studio extension that you can download from here. Once installed, fire up VS and create a new .NET 4.0 Framework project. Then in the Solution Explorer, right click the project and select "Manage NuGet Packages...".

In the dialog, search online packages for: CorrugatedIron.

Click Install, and it will download all the assemblies/dependencies you need. When it's finished, you'll see you have references to CorrugatedIron, Newtonsoft.Json and protobuf-net.

Open up app.config and you'll see some initial example config for CorrugatedIron. Configure that accordingly to point at your running Riak node. For example, my Riak node is running on a VM called "Riak1", with the REST interface listening on port 8098 and the protocol buffers interface listening on port 8087:

<?xml version="1.0" encoding="utf-8"?>

<configuration>

  <configSections>

    <section name="riakConfig" type="CorrugatedIron.Config.RiakClusterConfiguration, CorrugatedIron" />

  </configSections>

  <riakConfig nodePollTime="5000" defaultRetryWaitTime="200" defaultRetryCount="3">

    <nodes>

      <node name="Riak1" hostAddress="Riak1" pbcPort="8087" restScheme="http" restPort="8098" poolSize="20" />      

    </nodes>

  </riakConfig>

</configuration>

Step 5
A quick check to make sure you can communicate with your Riak node:

static void Main(string[] args)

{

    IRiakCluster cluster = RiakCluster.FromConfig("riakConfig");

    IRiakClient client = cluster.CreateClient();

    RiakResult result = client.Ping();

    Console.WriteLine(result.IsSuccess);

}

Fingers crossed, and you will get a successful response! From here, you can start playing around with Riak from .NET and find your way round the client.

CorrugatedIron is still very new, so keep an eye on the site and/or on the GitHub repository. Give it a whirl, and be sure to let the guys know how you get on - I'm sure they'd be keen to hear from those using it and the project deserves support from those of us in the .NET world.

Useful tool
It's useful to have some form of GUI over Riak so you can actually "see stuff". I've been using Rekon which I found useful to get up and running with Riak. See the download/install instructions on it's GitHub repository page.

Hopefully, this post will help anyone else wanting to give Riak a try from a .NET perspective!

sp_executesql change between 2005 and 2008

2011-06-27T20:28:00.000+01:00

Today I tripped over what turned out to be a difference in the way sp_executesql behaves between SQL Server 2005 and 2008 when executing a string containing a parameterised stored procedure call.

Take this simplified example:

DECLARE @SQL NVARCHAR(256)
SET @SQL = 'sp_help @obj'
EXECUTE sp_executesql @SQL, N'@obj NVARCHAR(100)', 'sp_help'

In SQL Server 2008 (10.0.4000.0), the above executes successfully.
In SQL Server 2005 (9.00.1399.06), it throws the following exception:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'sp_help'.

Adding the "EXEC(UTE)" before the stored procedure name in @SQL resolves the issue in 2005. As standard, I (usually) standardise on ensuring all stored procedure calls are made with "EXEC(UTE)" even when it is the only statement in the batch. Obviously, in this case it was overlooked and tripped me up!

The importance of "Working Set"

2011-06-24T20:55:00.000+01:00

One of the things that I see cropping up pretty often is this thing called "working set". After recently chipping in on another StackOverflow question on the subject of "What does it meant to fit 'working set' in RAM?", I thought it was a good subject for a blog post. This is really just a copy and extension of my input on that question and focused in certain parts on MongoDB, but is also as relevant to other databases.

"Working set" is basically the amount of data and indexes that will be active/in use by your system at any given time.

Why is it important to keep your working set in RAM?

Accessing RAM is quick. Accessing disk is slow. When querying your data store, if all the data and indexes typically accessed are in RAM, then performance is blisteringly quick. If it's not in RAM, then disk access is required and that is when performance suffers. Hence it is important to ensure you have enough to hold your working set. The moment your working set exceeds the about of RAM you have, you will start to notice the performance degradation as it has to pull stuff back off disk, so it's important to monitor the situation and react.

Crude Example

Suppose you have 1 year's worth of data. For simplicity, each month relates to 1GB of data giving 12GB in total, and to cover each month's worth of data you have 1GB worth of indexes again totalling 12GB for the year.

If you are always accessing the last 12 month's worth of data, then your working set is: 12GB (data) + 12GB (indexes) = 24GB.

However, if you actually only access the last 3 month's worth of data, then your working set is: 3GB (data) + 3GB (indexes) = 6GB.

You need to understand your data set, scenario and the usage patterns, in order to work out a ball park estimate of your working set. Don't expect a black and white answer for what your working set is in your environment, from someone who doesn't know these things.

What if my working set sky-rockets?

Add more RAM. This can be a case of adding more into your existing node(s). Or, if you need non-trivial increases, making use of sharding to split the data over a number of nodes and just bring more nodes online as you need. This provides incredible potential to scale out your workload.

The key point is to ask yourself: do I have enough RAM for my working set? If the answer is: "I don't know", then get yourself to the position of knowing.