Logic App Tips: SQL db can't be shared, but...

Highlight

Speaking of logic app state storage, what about SQL DB? Can SQL database be used to share state for many logic apps?

Some time ago Microsoft released a public preview of SQL database support for storing Logic App State. This might not be a big deal for many, but it might be interesting for some organizations that could leverage it to replace old school Storage Accounts for state management.

Intro

As we know Azure Logic Apps use Storage Accounts to save their state. This state contains information about every workflow execution, logs, data, etc. In one of my previous posts I’ve talked about how this can be a problem when building large platforms with many logic apps.

So is Azure SQL a good alternative? Let’s try to figure it out.

Azure SQL Database can’t be shared, but…

First of all, one thing is certain. Azure SQL Database, similarly to Azure Storage Account can’t be shared across many logic apps.

Based on documentation, the way this works is that logic app needs a high-level permissions on three schemas in the database. Namely

• dc - for data containers - this replicates storage blob containers
• dq - for data queues - this replicates storage queues
• dt - for data tables - this replaces storage tables

Below diagram illustrates this

Similarly to Azure Storage Account. If we were to share SQL schemas there is an inevitable chance of data collisions which will mess up your logic apps state.

But… there is a way.

But not all is lost, there is one interesting fact here. Compared to storage accounts there is a difference here.

In storage account you have a structure like this

• Logic App - 1 Private Endpoint
• Storage Account
  • Blob Storage - 1 Private Endpoint
  • Queue Storage - 1 Private Endpoint
  • Table Storage - 1 Private Endpoint

4 private endpoints in total, where as on Azure SQL it’s as follows

• Logic App - 1 Private Endpoint
• Azure SQL Server - 1 Private Endpoint
  • Azure SQL Database

So 2 private endpoints, instead of 4. But let’s look at the effect of scale here.

Effect of scale - Scenario with 10 logic apps

If we have 10 logic apps in the first scenario you will get 40 private endpoints. But if you share Azure SQL server, which is actually something fairly easily doable, then you have have 10 logic apps on the single server. In which case you get 10 private endpoints for logic apps, and 1 shared private endpoint for all databases, since private endpoint is tied to the server, not the database. So you will end up in with 11 PEs.

Summary

It’s hard to say whenever SQL DB will be a good replacement for Storage Accounts for Logic Apps state, since cost is just one of many factors to take into account. There are also things like

1. Subscription limits
2. SQL limits
3. SQL pricing at higher throughputs
4. SQL management

Time will tell, but it’s definitely an interesting avenue to pursue

Hear you next time

Next Actions

Here are some recommendations from me on what you can check out next if you want to learn more