Category: Azure

Azure SQL Database deployment with Farmer, DbUp and GitHub Actions

Farmer is a DSL for generating and executing ARM templates and one of the great things about it is that its based on .NET Core. That means that you can use it in combination with other components from the .NET ecosystem to create end to end typesafe deployment solutions.

As an aside – I recently posted a critique of Microsofts new DSL Bicep. One of the things I didn’t mention in that but did in a series of tweets was the shortcomings of inventing a new language that lives in its own ecosystem.

Ultimately Bicep will need to support “extension points” or you’ll have to wrap them in script and communicate information across boundaries (of course their can be benefits to that approach too). Not to mention they need to write all the tooling from scratch and developers / administrators need to learn another language.

By taking the approach Farmer has handling boundaries is a lot cleaner – as we’ll see – and we can take advantage of some neat language features.

In this example I’m going to provision an Azure SQL Database into Azure and then upgrade its schema using DbUp and we’ll run all this through GitHub Actions giving us an automated end to end deployment / upgrade system for our SQL database. You could do this with less F# code (almost none) but I also want to try and illustrate how this approach can form a nice framework for more complicated deployment scenarios so we’re also going to look at error handling across a deployment pipeline.

As all the components themselves are well documented I’m not going to go end to end on all the detail of each component here – instead I’m going to focus on the big picture and the glue. You can find the code for the finished demonstration on GitHub here.

Starting with a F# console app, adding the Farmer NuGet package, and the boilerplate Program.fs file first we need to declare our Azure resources – in this case a SQL Server and a database and then bring them together in an ARM template:

let demoDatabase = sqlServer {
    name serverName
    admin_username "demoAdmin"
    enable_azure_firewall
    
    add_databases [
        sqlDb { name databaseName ; sku DbSku.Basic }
    ]
}

let template = arm {
    location Location.UKWest
    add_resource demoDatabase
    output "connection-string" (demoDatabase.ConnectionString databaseName)
}

Pretty straightforward but a couple of things worth noting:

  1. Both serverName and databaseName are simple constants (e.g. let databaseName = “myDatabaseName”) that I’ve created as I’m going to use them a couple of times.
  2. Opening up the database to azure services (enable_azure_firewall) will allow the GitHub Actions Runner to access the database.
  3. On the final line of our arm block we output the connection string for the database so we can use it later.

That’s our Azure resources but how do we apply our SQL scripts to generate our schema? First we’ll need to add the dbup-sqlserver NuGet package and with that in place we’ll first add a Scripts folder to our solution and in my example four scripts:

DbUp keeps track of the last script it ran and applies subsequent scripts – essentially its a forward only ladder of migrations. If you’re adding scripts of your own make sure you mark them as Embedded Resource otherwise DbUp won’t find them. To apply the scripts we simply need some fairly standard DbUp code like that shown below, I’ve placed this in a F# module called DbUpgrade so, as we’ll see in a minute, we can pipe to it quite elegantly:

let tryExecute =
  Result.bind (fun (outputs:Map<string,string>) ->
    try
      let connectionString = outputs.["connection-string"]
      let result =
        DeployChanges
          .To
          .SqlDatabase(connectionString)
          .WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly())
          .LogToConsole()
          .Build()
          .PerformUpgrade()
      match result.Successful with
      | true -> Ok outputs
      | false -> Error (sprintf "%s: %s" (result.Error.GetType().Name.ToUpper()) result.Error.Message)
    with _ -> Error "Unexpected error occurred upgrading database"
  )

If you’re not familiar with F# you might wonder what this Result.bind function is. F# has a wrapper type for handling success and error states called options and a bunch of helper functions for their use. One of the neat things about it is it lets you chain lots of functions together with an elegant pattern for handling failure – this is often referred to as Railway Oriented Programming.

We’ve now declared our Azure resources and we’ve got a process for deploying our upgrade scripts and we need to bring it all together and actually execute all this. First lets create our deployment pipeline that first provisions the resources and then upgrades the database:

let deploymentPipeline =
  Deploy.tryExecute "demoResourceGroup" [ adminPasswordParameter ]
  >> DbUpgrade.tryExecute 

If we had additional tasks to run in our pipeline we’d join them together with the >> operator as I’ve done here.

To run the deployment we need to provide an admin passford for SQL server which you can see in this code snippet as sqlServerPasswordParameter and we need to do this securely – so it can’t sit in the source code. Instead as I’m going to be running this from GitHub Actions an obvious place is the Secrets area of GitHub and an easy way to make that available to our deployment console app is through an environment variable in the appropriate action (which we’ll look at later). We can then access this and format it for use with Farmber by adding this line:

let adminPasswordParameter =
  Environment.GetEnvironmentVariable("ADMIN_PASSWORD") |> createSqlServerPasswordParameter serverName

Farmer uses a convention approach to a parameter name – I’ve built a little helper function createSqlServerPassword to form that up.

(We could take a number of different approaches to this – ARM parameters for example – I’ve just picked a simple mechanism for this demo)

Finally to invoke all this we add this line at the bottom of our file:

template |> deploymentPipeline |> asGitHubAction

asGitHubAction is another little helper I’ve created that simply returns a 0 on success or prints a message to the console and returns a 1 in the event of an error. This will cause the GitHub Action to fail as we want.

That’s the code side of things done. Our finished Program.cs looks like this:

open System
open Farmer
open Farmer.Builders
open Sql
open Constants
open Helpers

[<EntryPoint>]
let main _ =
  let adminPasswordParameter =
    Environment.GetEnvironmentVariable("ADMIN_PASSWORD") |> createSqlServerPasswordParameter serverName

  let demoDatabase = sqlServer {
    name serverName
    admin_username "demoAdmin"
    enable_azure_firewall
      
    add_databases [
      sqlDb { name databaseName ; sku DbSku.Basic }
    ]
  }

  let template = arm {
    location Location.UKWest
    add_resource demoDatabase
    output "connection-string" (demoDatabase.ConnectionString databaseName)
  }

  let deploymentPipeline =
    Deploy.tryExecute "demoResourceGroup" [ adminPasswordParameter ]
    >> DbUpgrade.tryExecute 
  
  template |> deploymentPipeline |> asGitHubAction

All we need to do now is wrap it up in a GitHub Action. I’ve based this action on the stock .NET Core build one – lets take a look at it:

name: Deploy SQL database

on:
  push:
    branches: [ master ]
  pull_request:
    branches: [ master ]

jobs:
  build:

    runs-on: ubuntu-latest

    steps:
    - uses: actions/checkout@v2
    - name: Setup .NET Core
      uses: actions/setup-dotnet@v1
      with:
        dotnet-version: 3.1.301
    - name: Install dependencies
      run: dotnet restore
    - name: Build
      run: dotnet build --configuration Release --no-restore
    - name: Login via Az module
      uses: azure/login@v1.1
      with:
        creds: ${{secrets.AZURE_CREDENTIALS}}
        enable-AzPSSession: true
    - name: Run
      env:
        ADMIN_PASSWORD: ${{ secrets.ADMIN_PASSWORD}}
      run: dotnet DeployDb/bin/Release/netcoreapp3.1/DeployDb.dll

If you’re familiar with GitHub Actions most of this should be fairly self explanatory – there’s nothing special about our deployment code, its a standard .NET Core console app so we begin by building it as we would any other (again this is one of the things I like about Farmer – its just .NET, and if you’re using .NET there’s nothing else required). However after building it we do a couple of things:

  1. To do the deployment Farmer will use the Azure CLI and so we need to login to Azure via that. We do that in the Login via Az module step which is pretty stock and documented on GitHub here. I’ve stored the secret for the service principal in the secrets area of GitHub.
  2. In the final step we run our deployment – again its just a standard console app. You an see in this step the use of the env section – we take a secret we’ve called ADMIN_PASSWORD and set it as an environment variable making it available to our console app.

And that’s it! At this point you’ve got an automated solution that will make sure your Azure SQL database infrastructure and its schema are managed get up to date. Change the configuration of your SQL database and/or add a SQL script and this will kick off and apply the changes for you. If / when you run it for the first time you should see output like this from the build section of the Action:

I think its a simple, but powerful, example of infrastructure as code and the benefits of using an existing language and ecosystem for creating DSLs – you get so much for free by doing so. And if the rest of your codebase is in .NET then with Farmer you can share code, whether that be simple constants and names or implementation, easily across your deployment and runtime environments. Thats a big win. I’m slowly adding it into my Performance for Cyclists project and this approach here is largely lifted from their.

Finally I think its worth emphasising – you don’t need to really know F# to use Farmer and you certainly don’t need to be using it elsewhere in your solution. Its a pretty simple DSL build on top of F# and a fantastic example of how good F# is as a basis for DSLs. I’ve dug a little deeper into the language here to integrate another .NET tool but if all you want to do is generate ARM templates then, as you can see from the Farmer examples on its website, you really don’t need to get into the F# side (though I do encourage you to!).

Bicep – an utterly uninspiring start

I’m not sure when it went public but Microsoft now have an alpha of Bicep available on GitHub. Bicep is their attempt to deal with the developer unfriendly horror and rats nest that is ARM templates.

I took a look at it today and came away thoroughly disappointed with what I saw. Before I even started to look at the DSL itself the goals raised a whole bunch of red flags… so lets start there.

And strap in… because this is going to be brutal.

Bicep Goals

  1. Build the best possible language for describing, validating, and deploying infrastructure to Azure.
    Laudable.
  2. The language should provide a transparent abstraction for the underlying platform. There must be no “onboarding step” to enable it to a new resource type and/or apiVersion in Bicep.
    Seems important. Azure moves fast and new things are added all the time. On the one hand it would be nice to see each push of a new resource / API to Azure be coupled to some nice Bicep wrapping – but given the breadth and pace of what goes on its probably not realistic.

    On the other hand…. this seems dangerous. There’s a lot of intricacy in that stuff and if we just boil down to expressing the ARM inside strings with a bit of sugar round it what have we gained?
  3. Code should be easy to understand at a glance and straightforward to learn, regardless of your experience with other programming languages.
    Ok. No great quibble here.
  4. Users should be given a lot of freedom to modularize and reuse their code. Reusing code should not require any ‘copy/paste’.
    Seems weak. I’d like to see this strengthened such that writing new Bicep code shouldn’t require copy/paste – see my point (2) above as these things seem somewhat coupled. Cough. Magic strings. Cough.
  5. Tooling should provide a high level of resource discoverability and validation, and should be developed alongside the compiler rather than added at the end.
    On the one hand I don’t disagree. On the other… this seems like a bit of a cop out back to magic strings.
  6. Users should have a high level of confidence that their code is ‘syntactically valid’ before deploying.
    Wait. What? A “high level of confidence”. I don’t want a high level of confidence. I want to know. Ok – if I’m on the bleeding edge and the Azure resource hasn’t been packed into some nice DSL support yet then ok. But if I’m deploying, say, a vanilla App Service I don’t want a high level of confidence – I want to know.

I don’t about you but to me this sounds like it has the hallmarks of another half baked solution (“no its awesome” – random Twitter devrel) that still requires you to remember a bunch of low level details. And probably still relies on strings.

The Tutorial

Next I cautiously cracked open the tutorial… oh god.

Strings galore. Strings for well known identities. The joy. I’ve not installed the tooling but I assume it helps you pick the right string. But really? REALLY? Strings.

I was pretty horrified / disappointed so I continued through hoping this was just the start but as far as I can tell – nope. Strings are a good idea apparently. This is the final example for storage:

Same dogs dinner.

I can absolutely see why you want some form of string support in their. As I noted in the goals if a new API version is released you want to be able to specify it. But there are ways to achieve this that don’t involve this kind of untyped nonsense. In the normal course of events for common things like Storage accounts the only strings in use should be for names.

No wonder they can only give “confidence” things are syntactically correct.

The tutorial finishes with “convert any ARM template to Bicep” – its not hard is it. This is still a thin low value wrapper on top of ARM. We’ve just got rid of the JSON and replaced it with something else. If you don’t add much value then converting between two things is generally straight forward.

I’m struggling not to be unkind… but is their any kind of peer review for this stuff? Do people who understand languages or have a degree of breadth get involved? Do people actually *making* things using this stuff get involved? Because as someone involved in making lots of stuff and running teams making stuff – this misses by miles.

It really doesn’t have to be this way – the community are coming up with better solutions frankly. Bit of a “back to Build” but why the heck didn’t they put some weight behind Farmer – or at least lift some of its ideas (I’m glad they at least acknowledge it). Because here’s a storage account modelled in that:

Here’s a more complex Farmer block for storage and a web app:

Neater right? And typesafe. Its not hard to imagine something inbetween Farmer and Bicep that doesn’t rely on all these strings and bespoke tooling (Farmer is based on F#… so the tooling already exists) but still allows you to dive into things “not in the box”.

Conclusion

Super disappointing. Hopelessly basic. Doesn’t look to solve many problems. Another “requires lots of custom tooling” project. A tiny incremental move on from ARM. Doesn’t seem worth the effort. Better hope the Bicep tooling is good and frequently updated if you plan on using this.

If we’re now “treating ARM as the IL” (which is what it is – despite years of MS pushing back on feedback that ARM is awesome) then this really is a poor effort to build on that. Which is sad because as it comes from Microsoft its likely to become the most commonly used solution. Merit won’t have much to do with it.

If anyone from the Bicep team wants to talk about this – happy to.

An Azure Reference Architecture

There are an awful lot of services available on Azure but I’ve noticed a pattern emerging in a lot of my work around web apps. At their core they often have a similar architecture, deployment in Azure, and process for build and release.

For context a lot my hands on work over the last 3 years has been as a freelancer developing custom systems for people or on my own side projects (most recently https://www.forcyclistsbycyclists.com). In these situations I’ve found productivity to be super important in a few key ways:

  1. There’s a lot to get done, one or two people, and not much time – so being able to crank out a lot of work quickly and to a good level of quality is key.
  2. Adaptability – if its an externally focused green field system there’s a reasonable chance that there’s a degree of uncertainty over what the right feature set is. I generally expect to have to iterate a few times.
  3. I can’t be wasting time repeating myself or undertaking lengthy manual tasks.

Due to this I generally avoid over complicating my early stage deployment with too much separation – but I *do* make sure I understand where my boundaries and apply principles that support the later distribution of a system in the code.

With that out the way… here’s an architecture I’ve used as a good starting point several times now. And while it continues to evolve and I will vary specific decisions based on need its served me well and so I thought I’d share it here.

I realise there are some elements on here that are not “the latest and greatest” however its rarely productive to be on the bleeding edge. It seems likely, for example, that I’ll adopt the Azure SPA support at some point – but there’s not much in it for me doing that now. Similarly I can imagine giving GitHub Actions ago at some point – but what do I really gain by throwing what I know away today. From the experiments I’ve run I gain no productivity. Judging this stuff is something of a fine line but at the risk of banging this drum too hard: far too many people adopt technology because they see it being pushed and talked about on Twitter or dev.to (etc.) by the vendor, by their DevRel folk and by their community (e.g. MVPs) and by those who have jumped early and are quite possibly (likely!) suffering from a bizarre mix of Stockholm Syndrome and sunk cost fallacy “honestly the grass is so much greener over here… I’m so happy I crawled through the barbed wire”.

Rant over. If you’ve got any questions, want to tell me I’m crazy or question my parentage: catch me over on Twitter.

Architecture

Build & Release

I’ve long been a fan of automating at least all the high value parts of build & release. If you’re able to get it up and running quickly it rapidly pays for itself over the lifetime of a project. And one of the benefits of not CV chasing the latest tech is that most of this stuff is movable from project to project. Once you’ve set up a pipeline for a given set of assets and components its pretty easy to use on your next project. Introduce lots of new components… yeah you’ll have lots of figuring out to do. Consistency is underrated in our industry.

So what do I use and why?

  1. Git repository – I was actually an early adopter of Git. Mostly because I was taking my personal laptop into a disconnected environment on a regular basis when it first started to emege and I’m a frequent committer.

    In this architecture it holds all the assets required to build & deploy my system other than secrets.
  2. Azure DevOps – I use the pipelines to co-ordinate build & release activities both directly using built in tasks, third party tasks and scripts. Why? At the time I started it was free and “good enough”. I’ve slowly moved over to the YAML pipelines. Slowly.
  3. My builds will output four main assets: an ARM template, Docker container, a built single page application, and SQL migration scripts. These get deployed into a an Azure resource group, Azure container registry, blob storage, and a SQL database respectively.

    My migration scripts are applied against a SQL database using DbUp and my ARM templates are generated using Farmer and then used to provision a resource group. I’m fairly new to Farmer but so far its been fantastic – previously I was using Terraform but Farmer just fit a little nicer with my workflow and I like to support the F# community.

Runtime Environment

So what do I actually use to run and host my code?

  1. App Service – I’ve nearly always got an API to host and though I will sometimes use Azure Functions for this I more often use the Web App for Containers support.

    Originally I deployed directly into a “plain” App Service but grew really tired with the ongoing “now this is really how you deploy without locked files” fiasco and the final straw was the bungled .NET Core release.

    Its just easier and more reliable to deploy a container.
  2. Azure DNS – what it says on the tin! Unless there is a good reason to run it elsewhere I prefer to keep things together, keeps things simple.
  3. Azure CDN – gets you a free SSL cert for your single page app, is fairly inexpensive, and helps with load times.
  4. SQL Database – still, I think, the most flexible general purpose and productive data solution. Sure at scale others might be better. Sure sometimes less structured data is better suited to less structured data sources. But when you’re trying to get stuff done there’s a lot to be said for having an atomic, transactional data store. And if I had a tenner for every distributed / none transactional design I’ve seen that dealt only with the happy path I would be a very very wealthy man.

    Oh and “schema-less”. In most cases the question is is the schema explicit or implicit. If its implicit… again a lot of what I’ve seen doesn’t account for much beyodn the happy path.

    SQL might not be cool, and maybe I’m boring (but I’ll take boring and gets shit done), but it goes a long way in a simple to reason about manner.
  5. Storage accounts – in many systems you come across small bits of data that are handy to dump into, say, a blob store (poor mans NoSQL right there!) or table store. I generally find myself using it at some point.
  6. Service Bus – the unsung hero of Azure in my opinion. Its reliable. Does what it says on the tin and is easy to work with. Most applications have some background activity, chatter or async events to deal with and service bus is a great way of handling this. I sometimes pair this (and Azure Functions below) with SignalR.
  7. Azure Functions – great for processing the Service Bus, running code on a schedule and generally providing glue for your system. Again I often find myself with at least a handful of these. I often also use Service Bus queues with Functions to provide a “poor mans admin console”. Basically allow me to kick off administrative events by dropping a message on a queue.
  8. Application Insights – easy way of gathering together logs, metrics, telemetry etc. If something does go wrong or your system is doing something strange the query console is a good way of exploring what the root cause might be.

Code

I’m not going to spend too long talking about how I write the system itself (plenty of that on this blog already). In generally I try and keep things loosely coupled and normally start with a modular monolith – easy to reason about, well supported by tooling, minimal complexity but can grow into something more complex when and if that’s needed.

My current tools of choice is end to end F# with Fable and Saturn / Giraffe on top of ASP.Net Core and Fable Remoting on top of all that. I hopped onto functional programming as:

  1. It seemed a better fit for building web applications and APIs.
  2. I’d grown tired with all the C# ceremony.
  3. Collectively we seem to have decided that traditional OO is not that useful – yet we’re working in languages built for that way of working. And I felt I was holding myself back / being held back.

But if you’re looking to be productive – use what you know.

App Service Easy Auth with Auth0 (or any Open ID Connect provider)

So I’m going to prefix this with a warning – I doubt this is officially supported but at a basic level it does seem to work. I would use at your peril and I’m writing this in the hope that it makes for a useful starting point discussion with the App Service team.

I was looking at Easy Auth this week and found myself curious as to if it would work with a generic Open ID Connect identity provider. My first choice provider is Auth0 but that’s not one of the listed providers on the Easy Auth configuration page which, on the face of it, is quite limited:

Azure AD is (as well as many other things) an Open ID Connect Provider so I had a look at its settings in the advanced tab and its asking for two pretty common pieces of information in the identity world: a client ID and an issuer URL. I had an app in Auth0 that I use for general testing so I pasted in its well known configuration endpoint and the ID for my client:

I hit save and it seemed to accept everything. My web app is sat on the URL https://jdreasyauth0.azurewebsites.net/ so on the Auth0 side I added a callback URL to the Easy Auth callback endpoint:

Easy Auth forwards on the contents of common claims in headers such as X-MS-CLIENT-PRINCIPAL-ID (the subject) and X-MS-CLIENT-PRINCIPAL-NAME (the name) so to see if this was working I uploaded a simple ASP.Net Core app that would output the contents of the request headers to a web page. Then I paid it a visit in my browser:

Oh. So that’s hurdle one passed. It does redirect successfully to a none-Azure AD identity provider. What about logging in?

Great. Yes. This works too. And the headers are correct based on the identity I used to login with.

How does this compare to the headers from an Azure AD backed Easy Auth:

Basically the Auth0 login is missing the refresh token (I did later set a client secret and tweak configuration in Auth0) – so there might be some work needed there. But I don’t think that’s essential.

It would be incredibly useful to be able to use Easy Auth in a supported manner with other identity providers – particularly for Azure Functions where dealing with token level authorization is a bit more “low level” than in a fully fledged framework like ASP .Net Core (though my Function Monkey library can help with this) and is only dealt with after a function invocation.

Using Function Monkey with MediatR

There are a lot of improvements coming in v4 of Function Monkey and the beta is currently available on NuGet. As the full release approaches I thought it would make sense to introduce some of these new capabilities here.

In order to simplyify Azure Functions development Function Monkey makes heavy use of commanding via a mediator and ships with my own mediation library. However there’s a lot of existing code out their that makes use of the popular MediatR library which, if Function Monkey supported, could fairly easily be moved into a serverless execution environment.

Happily Function Monkey now supports just this! You can use my existing bundled mediator, bring your own mediator, or add the shiny new FunctionMonkey.MediatR NuGet package. Here we’re going to take a look at using the latter.

First begin by creating a new, empty, Azure Functions project and add three NuGet packages:

FunctionMonkey
FunctionMonkey.Compiler
FunctionMonkey.MediatR

At the time of writing be sure to use the prerelease packages version 4.0.39-beta.4 or later.

Next create a folder called Models. Add a class called ToDoItem:

public class ToDoItem
{
    public Guid Id { get; set; }
    
    public string Title { get; set; }
    
    public bool IsComplete { get; set; }
}

Now add a folder called Services and add an interface called IRepository:

internal interface IRepository
{
    Task<ToDoItem> Create(string title);
}

And a memory based implementation of this called Repository:

internal class Repository : IRepository
{
    private readonly List<ToDoItem> _items = new List<ToDoItem>();
    
    public Task<ToDoItem> Create(string title)
    {
        ToDoItem newItem = new ToDoItem()
        {
            Title = title,
            Id = Guid.NewGuid(),
            IsComplete = false
        };
        _items.Add(newItem);
        return Task.FromResult(newItem);
    }
}

Now create a folder called Commands and in here create a class called CreateToDoItemCommand:

public class CreateToDoItemCommand : IRequest<ToDoItem>
{
    public string Title { get; set; }
}

If you’re familiar with Function Monkey you’ll notice the difference here – we’d normally implement the ICommand<> interface but here we’re implementing MediatR’s IRequest<> interface instead.

Next create a folder called Handlers and in here create a class called CreateToDoItemCommandHandler as shown below:

internal class CreateToDoItemCommandHandler : IRequestHandler<CreateToDoItemCommand, ToDoItem>
{
    private readonly IRepository _repository;

    public CreateToDoItemCommandHandler(IRepository repository)
    {
        _repository = repository;
    }
    
    public Task<ToDoItem> Handle(CreateToDoItemCommand request, CancellationToken cancellationToken)
    {
        return _repository.Create(request.Title);
    }
}

Again the only real difference here is that rather than implement the ICommandHandler interface we implement the IRequestHandler interface from MediatR.

Finally we need to add our FunctionAppConfiguration class to the root of the project to wire everything up:

public class FunctionAppConfiguration : IFunctionAppConfiguration
{
    public void Build(IFunctionHostBuilder builder)
    {
        builder
            .Setup(sc => sc
                .AddMediatR(typeof(FunctionAppConfiguration).Assembly)
                .AddSingleton<IRepository, Repository>()
            )
            .UseMediatR()
            .Functions(functions => functions
                .HttpRoute("todo", route => route
                    .HttpFunction<CreateToDoItemCommand>(HttpMethod.Post)
                )
            );
    }
}

Again this should look familiar however their are two key differences. Firstly in the Setup block we use MediatR’s IServiceCollection extension method AddMediatR – this will wire up the request handlers in the dependency injector. Secondly the .UseMediatR() option instructs Function Monkey to use MediatR for its command mediation.

And really that’s all their is to it! You can use both requests and notifications and you can find a more fleshed out example of this on GitHub.

As always feedback is welcome on Twitter or over on the GitHub issues page for Function Monkey.

Azure Data Factory – Mapping Bug

This is something to be aware of as its effects can be very very subtle.

As part of a project for a client I have an Azure Data Factory that picks up data from various sources and moves it into a reporting database. Their are various types of copy operation going on but a common one is to take a data source and execute it via a stored procedure passing it in as a table parameter. You set up column mappings from the source to the target. I had this table type defined for a parameter to my ingestion stored procedure:

create type SessionType as table(
    [Id]                uniqueidentifier not null primary key nonclustered,
    [ProgrammeId]       UNIQUEIDENTIFIER not null,
    [StartDateTimeUtc]  datetime2        not null,
    [VolunteerId]       uniqueidentifier not null,
    [SessionCancelled]  bit              not null,
    [LastModified]      binary(8)        not null,
    [VolunteerRating]   int              null,
    [VolunteerComments] nvarchar(1025)   null
)

And a mapping set up from the source to this table as follows:

My ingestion procedure ran ok (it does a merge) but I was getting weird downstream results: data didn’t seem to be correlating as we expected. On back and forthing between the data sources I realised that the VolunteerId and the ProgrammeId were switched – the target VolunteerId was getting the source ReadingProgrammeId and the target ProgrammeId was getting the source VolunteerId.

I’d edited this so wandered if their was some weird caching going on or if the publish hadn’t really published so I made a change to try and force things – plus I’d run out of ideas. I couldn’t see a thing wrong with any of the SQL. I removed the two mappings and added them at the end:

On rerunning my data factory I found I now got an error. An issue trying to insert a datetime2 type into a uniqueidentifier column. The penny dropped. Despite the GUI, despite the tooling, despite the ARM definition the data factory is not using the column names – its merrily ignoring them and using order of the columns in the schema table type definition for targets.

I verified this by setting up a mapping based on the order of columns in the table type:

That fixed things and my downstream systems can now make sense of the data.

Hopefully they’ll get this fixed as unless you get a type clash its pretty dangerous.

Writing and Testing Azure Functions with Function Monkey – Part 3

Part 3 of my series on writing Azure Functions with Function Monkey focuses on writing tests using the newly released testing package – while this is by no means required it does make writing high value acceptance tests that use your applications full runtime easy and quick.

Lessons Learned

It really is amazing how quickly time passes when you’re talking and coding – I really hadn’t realised I’d recorded over an hours footage until I came to edit the video. I thought about splitting it in two but the contents really belonged together so I’ve left it as is.

Bike Reminders – A breakdown of a real Azure application (Part 1)

I’ve been meaning to write about a real cloud based project for some time but the criteria a good candidate project needs to fit are challenging:

  • Significant enough to illustrate numerous design and implementation decisions
  • Not so large that the time investment for a reader to get into it is prohibitive
  • I need to own, or have free access to, the intellectual property
  • It needs to be something I want, or am contracted, to build for reasons beyond writing about it

To expand upon that last point a little – I don’t have the time to build something just for a series of blog posts and if I did I suspect it would be too artificial and essentially would end up a strawman.

The real world and real development is constrained messy, you come across things that you can’t economically solve in an ivory towered fashion. You can’t always predict everything in advance, you get things wrong and don’t always have the time available to start again and so have to do the best that you can with what you have.

In the case of this project I hadn’t really thought about it as a candidate for writing about until I neared the end of building the MVP and so it comes, rather handily, with warts and all. For sure I’ve refactored things but no more than you’d expect to on any time and budget constrained project.

My intention is, over the course of a series of posts, to explore this application in an end to end fashion: the requirements, the architecture, the code, testing, deployment – pretty much its end to end lifecycle. Hopefully this will contain useful nuggets of information that can be applied on other projects and help those new to Azure get up and running.

About the project

So what does the project do?

If you’re a keen cyclist you’ll know that you need to check various components on your bike at regular intervals. You’ll also know that some of the components last just long enough that you’ll forget about them – my personal nemesis is chain wear, more than once I’ve taken that to the point where it is likely to start damaging the rear cassette having completely forgotten about it.

I’m fortunate enough to have a rather nice bike and so there is nothing cheap about replacing anything so really not a mistake you want to be making. Many bikes also now contain components that need charging – Di2 and eTap are increasingly common and though I’ve yet to get caught out on a ride I’ve definitely run it closer than I realised.

After the last time I made this mistake I decided to do something about it and thus was born Bike Reminders: a website that links up with Strava to send you reminders as you accrue mileage on each of your bikes. While not a substitute for regularly checking your bike I’m hopeful it will at least give me a prod over chain wear! I contemplated going direct to Garmin but they seem to want circa $5000 for API access and thats a lot of component damage before I break even – ouch.

In terms of an MVP that distilled out into a handful of high level requirements:

  • Authenticate with Strava
  • Access a users bikes in Strava
  • Allow a mileage based maintenance schedule to be set up against a bike
  • Allow email reminders to be dismissed / reset
  • Allow email reminders to be snoozed
  • Update the progress towards each reminder based on rider activity in Strava

There were also some requirements I wanted to keep in mind for the future:

  • Time based reminders
  • “First ride of the week” type reminders
  • Allow reminders to be sent via push notifications
  • Predictive information – based on a riders history when is a reminder likely to be triggered, this is useful if you’re going away on a training camp for example and want to get maintenance done before you go

Setting off on the project I set a number of overarching goals / none functional requirements for it:

  • Keep it small enough that it could be built alongside a two (expanded to three!) week cycling training block in Mallorca
  • To have a very low cost to run both in terms of minimum footprint (cost to run 1 user) and per user cost as the system scales up
  • To require little to no maintenance and a fully automated delivery mechanism
  • To support multiple client types (initially web but to be followed up with a Flutter app)
  • Keep personal data out of it as far as possible
  • As far as possible spin out any work that isn’t specific to the problem domain as open source (I’m fairly likely to reuse it myself if nothing else)

And although I try not to jump ahead of myself that mapped nicely onto using Azure as a cloud provider with Azure Functions for compute and Azure DevOps and Application Insights for the operational side of things.

Architecture

The next step was to figure out what I’d need to build – initially I worked this through on a “mental beermat” while out cycling but I like to use the C4 Model to describe software systems. It gives a basic structure and just enough tools to think about and describe systems at different levels of architecture without disappearing up its own backside in complexity and becoming an end in and of itself.

System Context

For this fairly simple and greenfield system establishing the big picture was fairly straight forward. It’s initially going to comprise of a website accessed by cyclists with their Strava logins, connecting to Strava for tracking mileage, and sending emails for which I chose SendGrid due to existing familiarity with it.

Containers

Breaking this down into more detail forced me to start making some additional decisions. If I was going to build an interactive website / app I’d need some kind of API for which I decided to use Azure Functions. I’ve done a lot of work with them, have a pretty good library for building REST APIs with them (Function Monkey) and they come with a generous free usage allowance which would help me meet my low cost to operate criterion. The event based programming model would also lend itself to handling things like processing queues which is how I envisaged sending emails (hence a message broker – the Azure Service Bus).

For storage I wanted something simple – although at an early stage it seemed to me that I’d be able to store all the key details about cyclists, their bikes and reminders in a JSON document keyed off the cyclists ID. And if something more complex emerged I reasoned it would be easy to convert this kind of format into another. Again cost was a factor and as I couldn’t see, based on my simple requirements, any need for complex queries I decided to at least start with plain old Azure Storage Blob Containers and a filename based on the ID. This would have the advantage of being really simple and really cheap!

The user interface was a simple decision: I’ve done a lot of work with React and I saw no reason it wouldn’t work for this project. Over the last few months I’ve been experimenting with TypeScript and I’ve found it of help with the maintainability of JavaScript projects and so decided to use that from the start on this project.

Finally I needed to figure out how I’d most likely interact with the Strava API to track changes in mileage. They do have a push API that is available by email request but I wanted to start quickly (and this was Christmas and I had no idea how soon I’d hear back from them) and I’d probably have to do some buffering around the ingestion – when you upload a route its not necessarily associated with the right bike (for example my Zwift rides always end up on my main road bike, not my turbo trainer mounted bike) to prevent confusing short term adjustments.

So to begin with I decided to poll Strava once a day for updates which would require some form of scheduling. While I wasn’t expecting huge amounts of overnight for the website Strava do rate limit APIs and so I couldn’t use a timer function with Azure as that would run the risk of overloading the API quite easily. Instead I figured I could use enqueue visibility on the Service Bus and spread out athletes so that the API would never be overloaded. I’ve faced a similar issue before and so I figured this might also make for a useful piece of open source (it did).

All this is summarised in the diagram below:

Azure Topology

Mapped (largely) onto Azure I expected the system to look something like the below:

The notable exception is the introduction of Netlify for my static site hosting. While you can host static sites on Azure it is inelegant at best (and the Azure Storage SPA support is useless as you can’t use SSL and a custom domain) and so a few months back I went searching for an alternative and came across Netlify. It makes building, deploying and hosting sites ridiculously easy and so I’ve been gradually switching my work over to here.

I also, currently, don’t have API Management in front of the Azure Functions that present the REST API – the provisioned approach is simply too expensive for this system at the moment and the consumption model, at least at the time of writing, has a horrific cold start time. I do plan to revisit this.

Next Steps

In the next part we’ll break out the code and begin by taking a look at how I structured the Azure Function app.

Contact

  • If you're looking for help with C#, .NET, Azure, Architecture, or would simply value an independent opinion then please get in touch here or over on Twitter.

Recent Posts

Recent Tweets

Recent Comments

Archives

Categories

Meta

GiottoPress by Enrique Chavez