Not too long ago, Microsoft changed the way they licensed PowerApps. Before, you were able to use may of the connectors (especially SQL Server) with the licensing that came with PowerApps for Office 365. Microsoft turned that connector, and may others into “Premium Connectors”. In order to use these, you now have to license your users at either $10 or $40 per user, per month. This can bring a large cost to PowerApps when you’d like to connect to data like SQL Server.

For this scenario, we’re going to be looking at an organization that is creating a PowerApp for 500 of it’s users. All of these user currently have an Office 365 license, and are therefore licensed to use PowerApps for Office 365. However, one of the requirements for the App is that it needs to be able to read and write ton an on-prem SQL Database. In order to accomplish this natively in PowerApps, each user would need a Per App License ($10 per month). This translates to $5,000 per month, and $60,000 per year to be able to accomplish this.

Sync your SQL Data to SharePoint

As part of the license that come with PowerApps for Office365, you have the ability to natively connect to SharePoint with no additional license cost. In addition, there is a company called Layer2 (https://www.layer2solutions.com/products/layer2-cloud-connector) that makes a product that can synchronize you SQL tables to SharePoint lists. The sync can be 1-way or 2-way, so you can set it up to only have read only data in SharePoint, or you could write to the SharePoint list, and then have it sync back to SQL! There are a few different versions of this product (depending on the amount of data sources you will need to synchronize), however, the enterprise edition of the software is only $3200 per year! Using this solution may not be right in every circumstance, but as you can see, it can be quite attractive from a cost perspective depending on how many users will be using your PowerApp.

What are the pitfalls of a solution like this?

There are a few drawbacks to leveraging a solution like this. By syncing your tables to SharePoint lists, you lose the ability for the relational components that you would have natively in SQL. You wouldn’t be able to leverage SQL Views for instance. Reading or writing realational SQL information would need to be handled by PowerApps, and therefore could add a lot of additonal overhead to your design and support of the App.

There may also be delegation limitations when reading large SharePoint lists from PowerApps. A solution like this would be best suited for narrow targeted reading and writing of specific data from SQL, not necessarily large databases with many tables. In addition, there is an infrastructure cost, as layer 2 will need to reside on a server that can access your data source, and SharePoint. There are redundancy considerations at play too, depending on the resiliency of the synchronization required by the organization.

How about the benefits?

Beyond just SQL Server, the Layer2 cloud connector can synchronize data from a whole host of different cloud data sources, whereas Microsoft will still charge a premium for accessing data to these systems. I’ve found Layer2 to be very easy to setup and maintain, and it can provide a lot of value for what it can do. As your user base for Powerapps continues to grow, the cost savings could be enough to consider a solution like this.

I hope this little tidbit might be useful for all the PowerApps designers out there who are looking for ways to maximize the investment you may have already put into the Power Platform before Microsoft changed up all their licensing. As always, please feel free to reach out to me at jo.karnes@centricconsulting.com if you have any questions.