Should I use CDS as a backend for my application ?

[IanTaylor]

I have a .NET application where my backend is hosted currently on On-Premise SQL Server. In a year, I expect around 1000 transaction, which should be considered bare minimum.

I plan to migrate this to Powerapp and I understand its possible to do so, considering the transactional load being minimum.

However, I have a dilemma if I should go ahead with Common Data Service (CDS) or Azure DB as a backend in this scenario. Any advice would be much appreciated. 

[Pranav_Shroti · Accepted solution]

CDS is the backbone of Power Platform. When it comes to Power Apps it can act as an interface for CDS and its natively supported. Advantages of using CDS - 

1. Built in schema which can be extended as per business needs

2. Native connectors for Canvas and Model driven Power Apps

3. If you buy Power Apps licenses CDS will accompany it.

4. Not only schema it stores semantics as well - meaning email will be recognized as email in the schema. Your SQL DB will only recognize it as a normal data. It can also identify Personal Identification data and that logic is built into it.

5. No need to configure Azure firewall, or to understand database connection strings

Conclusion - I dont exactly know what your business domain is and other parameters like how much storage you will be using etc, however prima facie, CDS looks to be a better option.

Cheers !!!

Pranav

Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[Lars4]

In my opinion, the main problem of of working with CDS is accessing the raw data. If your table has a lot of lines (>10000) or big data types (nvarchar(max)), it can be impossible to check it. (That is the case for me, because the Excel view is not useful for my table with 200000 lines or does not load at all my table with text(100000) data fields.)

So, ask yourself a few questions.

How often do you look into the raw data and is it important for you?

How complex is your data model and how much data do you save? So is it likely to happen, that you get problems with raw data access?

Do you load the data from your data source and process it in other (non-PowerApps) application or do you expect doing so in future?

However, if you just have an easy data model with less data, I can recommend CDS without any doubts. It is easy to administrate and it is optimized for being connect to and used in PowerApps.

[DavidJennaway]

To add to the previous post, you can configure CDS to push data out to a SQL Azure DB using the Data Export Service, if you need access to the raw data. If you do that, then you'd pay for both CDS and SQL Azure, so it's not the most cost-effective option for the service costs, but it allows you have the advantages of CDS along with SQL access to data.

One other major advantage of CDS over SQL is that it has a built-in security model, and all data access is in the context of the end user.