July 25, 2021 - azure iot

Run Logic Apps locally with the SQL Storage Provider

Xavier Geerinck

@XavierGeerinck

Did you enjoy reading? Or do you want to stay up-to-date of new Articles?

Consider sponsoring me or providing feedback so I can continue creating high-quality articles!

For a customer, I have been researching a way to reduce the time it takes us to integrate different sensors on-edge, while still being flexible and configure everything remotely. Next to that, we do not want to spend much time on integrating custom connectors.

Use Case: "Reduce the time it takes us to integrate different sensors on-edge"

Requirements

  • Support on-edge flows with a focus on
    • Protocols (MQTT, AMQP, Websockets, Webhooks are commonly used by sensor vendors)
    • On-Edge computation (AI Inferencing)
  • Offline support (cloud connection should be optional)
  • Possible connectors for multi-cloud support (some of our customers require this)

Meet Logic Apps Standard

One of the possible solutions that caught my eye is Logic Apps. Now you might wonder: "Doesn't Logic App require an Azure Connection?" Well this was true until BUILD 2021, where Logic Apps Standard was introduced, which is a SKU designed to run as a containerized workload that can run anywhere. Causing a lot of excitement in the community! (myself included)

Looking at this announcement, the biggest requirement of all would now be possible through Logic Apps: "Offline Support"! But what is required for this and how far is Microsoft to make this reality?

Making Logic Apps Standard Available Offline

Well, if you have worked with Logic Apps before, you will know that Logic Apps is quite dependent on Azure Storage for its AzureWebJobsStorage setting. This configuration uses the provided connection string for normal operation, connecting to an Azure Storage Account. We could potentially utilize the Azure Storage Emulator, but this is not a production recommended configuration and thus not a good solution to work offline.

Luckily for us, Microsoft also announced that they are working on SQL Support to enable workloads to run locally with no Azure dependency on storage❗ Which is available as a Private Preview, so sign-up to get a sneak peek at it! 😉

For my use case I requested access to it and wanted to show you how this works!

So let's dive into how we are able to create a Logic Apps workflow that can run entirely offline.

Creating a Logic Apps Workflow

Requirements

Before we can get started creating our Logic Apps workflow, we need to install some dependencies:

⚠ I ran this on WSL but I included the Windows installation commands

  1. Install .NET Core SDK (3.1.X)
  • Windows: winget install Microsoft.dotnet --version 3.1.411 find the latest version in the winget-pkgs repository
  • Linux: wget -q https://dot.net/v1/dotnet-install.sh -O - | /bin/bash -s -- -c 3.1 (use -c Current for latest)
  1. Install Node.js (the current supported one is 14)
  • Windows: nvm install v14.17.3
  • Linux: nvm install v14.17.3
  1. Install Azure Functions Core Tools
  • Windows: winget install Microsoft.AzureFunctionsCoreTools
  • Linux: Add the repository and run sudo apt-get install azure-functions-core-tools-3
  1. Install VSCode Extension - Azure Logic Apps (Standard)
  2. Install VSCode Extension - Azure Functions
  3. Create global.json file in the root folder to configure the dotnet version it should use with the content below

global.json

{
"sdk": {
"version": "3.1.411",
"rollForward": "disable"
}
}

Creating a Logic Apps project

We are finally ready to get started on our Logic Apps project! 🤩 To create a Logic Apps workflow, execute the following in VSCode:

  1. Create and open a new Project Folder
  2. Press CTRL + P -> Azure Logic Apps: Create New Project -> Current Folder -> Stateless Workflow -> DemoSQLStorageProvider
  3. Right Click workflow.json -> Open in Designer -> Skip for now (for connectors)

The Logic Apps workflow design time api will now start up and run func host start --port 8000 in the background.

Configure SQL Storage Provider for Logic Apps Offline Support

Now we have all our dependencies installed, we can continue setting up the Storage Provider we will use. In this case that will be the SQL Storage Provider (Private Preview).

Starting SQL Server

Of course we need a SQL Server instance running for our SQL Storage Provider. For development purposes, we can simply run this with:

docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=******' -p 1433:1433 -d mcr.microsoft.com/mssql/server:2019-CU11-ubuntu-20.04

Which will allow us to connect through tools such as Azure Data Studio.

Create a database here and shape it into a connection string

# Create Database Query:
CREATE DATABASE LogicAppDemo
# Example Connection String:
# Server=localhost;Database=LogicAppDemo;User Id=sa;Password=*****

Configuring Logic Apps to utilize SQL Server

Now SQL Server is running, we can go back to our Logic Apps project and adapt the local.settings.json file to include the following:

{
"IsEncrypted": false,
"Values": {
"AzureWebJobsStorage": "", // Not required since we are using SQL Server as Storage Provider
"AzureWebJobsSecretStorageType": "Files", // Store secrets in local file system (https://docs.microsoft.com/en-gb/azure/azure-functions/functions-app-settings#azurewebjobssecretstoragetype)
"Workflows.Sql.ConnectionString": "Server=localhost;Database=LogicAppDemo;User Id=sa;Password=*****", // SQL Server connection information we use User ID and Password instead lf Integrated Security=True
"FUNCTIONS_WORKER_RUNTIME": "node", // The language worker runtime to load (https://docs.microsoft.com/en-us/azure/azure-functions/functions-app-settings#functions_worker_runtime)
"FUNCTIONS_V2_COMPATIBILITY_MODE": "true", // No Change
"FUNCTIONS_EXTENSIONBUNDLE_SOURCE_URI": "https://workflowscdn.azureedge.net/2021-05-unix-preview" // SQL Storage Provider extension bundle
}
}

Logic Apps is now configured to utilize the SQL Storage Provider.

Testing SQL Server Storage Provider

Before we continue, let's create a simple application that takes in a HTTP Request and then sends a HTTP Response back with "Hello World".

⚠ Do not forget to save!

Then start the Logic Apps runtime to ensure that we are able to run our application. Press CTRL + F5 (or Run -> Continue without debugging) to start up the Logic App workflow or utilize func start --verbose in a terminal window.

⚠ When it asks which runtime to use, select Node.js

You should now see your function app running

To invoke our endpoint, Right click on the workflow.json file and select "overview"

Now select the URL and load it in your browser to receive the expected result!

Troubleshooting

When you have an issue (e.g. Unable to load shared library 'sni.dll' or one of its dependencies) try to check the following

  • dotnet --version: is this showing 3.x.x?
  • Remove the core tools: rm -rf ~/.azure-functions-core-tools and try again

Did you enjoy reading? Or do you want to stay up-to-date of new Articles?

Consider sponsoring me or providing feedback so I can continue creating high-quality articles!

Xavier Geerinck © 2020

Twitter - LinkedIn