In today’s article, I will share with you my experience with using SQL Server as a back end for a PowerApps Canvas App.

Even though SQL Server is considered the most powerful RDBS out there, and it has amazing processing capabilities, there are some considerations we need to keep in mind when using it as a back end for Power Apps.

In most instances, I saw DBAs simplifying (or trying to simplify) the task to the PowerPlatform developers by creating views which are ready to consume.

When reading the data, this is quite convenient, but when it comes to updating it might turn into a challenge.

Writing to SQL Server Views

If a view contains complex joins and custom columns, updating it by writing back is difficult or impossible.

In most cases, you can directly write to a view if it is the result of one or two simple tables. However, if the keys are not set properly, SQL will reject writing to the view.

Besides, if mandatory base table columns need to be set, this approach will not work.

Using views to read data and underlying tables to update it can be a complex approach. However, it can also make maintenance a nightmare. Therefore, it’s important to think carefully before adopting this strategy.

Writing to SQL Server Tables

This is probably the preferred way to interact with SQL Server if you have a good understanding of the back end and the relationship/dependencies between tables.

Using Stored Procedures

Another widely adopted approach is to make a call to a stored procedure, with the parameters you want and let the DBA do the job for you.

The problem with this approach is that you cannot call a stored procedure directly from PowerApps. You will have to use a PowerAutomate which calls the stored procedure.

You guessed it, it makes the processing time longer and adds an extra layer (PowerAutomate) to the app, which will also need to be maintained and looked after.