Using NPoco With SQL Azure

NPoco has been my goto choice for database access in recent years. Now that more and more apps are being move up into the cloud, we’re naturally running into some new challenges with our old tools.

Transient Faults

If you’ve never deployed an application that interracts with SQL Azure, one of the first issues you’re going to run into at run time are Transient Faults. What the heck is a Transient Fault?

Transient faults are errors that occur because of some temporary condition such as network connectivity issues or service unavailability. Typically, if you retry the operation that resulted in a transient error a short time later, you find that the error has disappeared.

Or more simply - in normal operating conditions, your application will experience occassional exceptions when interracting with resources suchs as

  • SQL Azure
  • Service Bus
  • Storage Services
  • Cache Service

In my own experience, the heavier the use of the app, the more prone you are to experiencing (and your users experiencing) the exceptions.

Handling Transient Faults

Luckily, way back in the early days of Azure, Microsoft identified this issue and started a project specifically to make handling these issues easier. As time has gone on they have slowly integrated the handling block into clients. This means for some clients such as the Table or Blob Storage, you no longer have to worry about this yourself.

For SQL Azure, we’re still left in a situation where this is a concern. In these cases we can take advantage of the Transient Fault Data package. Once loaded, it’s pretty easy to handle this in your code

var retryPolicy = new RetryPolicy(new SqlDatabaseTransientErrorDetectionStrategy(), 3);

using(var db = new Database())
{
	var results = retryPolicy.ExecuteAction(() => db.Fetch<Users>());
}

RetryPolicy vs DetectionStrategy

With the code above, there are couple important things that make this work.

  1. SqlDatabaseTransientErrorDetectionStrategy - this is our DetectionStrategy. It’s purposes is to determine which exceptions are Transient
  2. RetryPolicy - this is our wrapper that controls how Transient exceptions are retried.

Thankfully, Microsoft has taken the time to define for us what is and is not a Transient exception with regards to SQL Azure - saving us quite a bit of work. Of course, if you wanted to roll your own, the framework is there for you.

Applying It To NPoco

NPoco was written as a generic data access helper, not specific to a single database. Which means it really doesn’t care (nor should it) about the behavior of SQL Azure. We also don’t want to be spreading around lots of coupling to the RetryPolicy in our application’s logic. This would result in a lot of extra code to maintain, which we don’t want.

Another consideration is that NPoco has a DatabaseFactory, but no hooks on it exist to help us.

Lastly, introduced in Version 3 of NPoco, Interceptors provide great functionality for hooking into the Before / After execution of commands. Our primary issue here is that our RetryPolicy expects us to wrap the entire task/action within one of it’s execution routines. Unlike transactions, we really don’t have a clean way to mark Begin/End without having to roll our own version of the RetryPolicy.

That means this logic really bellongs behind the abstraction of NPoco’s IDatabase but outside of the default Database implementation that ships with it.

Enter NPoco.Azure

I created a package that takes the existing Database implementation that ships with NPoco and wraps it behind a RetryPolicy with the SQLAzure detections strategy.

This makes it a lot easier to port existing code:

using(var db = new SqlAzureDatabase(...))
{
	var results = db.Fetch<Users>();
}