CDS vs Azure SQL Database

[mogulman52]

I'm just getting started looking at Powerapps.  I'm trying to figure the pros/cons of using CDS vs. Azure SQL Server.  My application is small, basically 3 tables (entities) with less than 200 records in each table.  Less than 2Mb DB.  From a cost perspective a low end Azure SQL Server instance is less than $10/month.  For CDS I need a Powerapps Plan 2 and around 5 Powerapps Plan 1 licenses so around $75/month.

The process I want to automate is surprising complex with lots of steps, tasks, emails etc. It has a lot of date calculations with many of them being Business days from a certain date.  I have yet to figure that out in CDS or Powerapps.  I can do that with a trigger or stored procedure in SQL Server.  If I find any other limitations I can always resort to T-Sql or T-Sql can call a C# program.

Can someone better help me understand pros/cons?

[Anonymous · Accepted solution]

I am strongly for Azure SQL Database. Current CDS is still lacking many things to replace SQL Database.

Check out my post in this thread on why I think Azure SQL is better than CDS.

[Anonymous]

I agree, we use Azure SQL.

I hope Powerapps team continue improving PowerApps functionality for MS SQL. 

Most companies use SQL and it would be really hard for them to migrate their data to CDS

[mogulman52]

Appreciate your comment.  Thanks.  I see several benefits to Azure SQL Database:

1. Cost as you mention

2. Deployment

3. Ability to use T-SQL for flow or other automation

4. T-SQL can call C#

I'm trying to figure out the pros for CDS.  I'm recommending we use Azure SQL database and I want to be sure I've considered everything.

[timl · Accepted solution]

Hi @mogulman52

Azure SQL would also be my database of choice. Here's my take on this....

CDS
The main benefit of the CDS is that it's simple to use. For non-developers, these benefits include:

• The ability to design tables via a web based designer - no need to install/learn SQL Management Studio
• Management of permissions through a web interface
• Access to pre-built data structures/entities for common tasks like sales, invoicing, and helpdesk.
• The ability to access data through an Excel addin
• The simplicity of having a database that's attached to an environment - no need to configure Azure firewall, or to understand 'database connection strings'

SQL Azure
In addition to the benefits that you and @Anonymous mention, I would add....

• There are many more ways for you to access your data. Data access isn't limited to Flow/PowerBI/PowerApps.
• You can use the built in/industry standard tools for backing up and restoring your data. Backing up CDS data is more difficult.
• You can bulk import data with tools like SSIS, import key values with 'identity insert', and more easily import sets of related data.
• You can optimise data retrieval and join tables with SQL views.
• For more complex tasks, you can use Flow and stored procedures.

In your post, you mention triggers. Please note that if you add a trigger to table, you won't be able to update that table from PowerApps. This thread here contains more details.

https://powerusers.microsoft.com/t5/General-Discussion/Update-SQL-Server-Table-with-trigger/m-p/102887#M37856

[mogulman52]

Thanks @timl for your feedback.  I'm confused on the trigger issue.  If you read this article that is referenced in the thread you sent me it appears to be related to on premises SQL Servers.  I plan to use an Azure SQL Server.   I wonder it works on that.

[timl]

Hi @mogulman52,

The problem with triggers applies also to Azure SQL Server.

I'd also recommend reading this great post by @Meneghino, which covers all of the issues that you'll likely encounter with Azure SQL.

http://baizini-it.com/blog/index.php/2017/09/28/powerapps-and-azure-sql-database-current-issues/#lis...

[ChinoDoesStuff]

Here's a link to my tutorial on Getting Started with Power Apps and Azure SQL. At least you can get an idea on what is involved with a SQL solution by watching my video. Hopefully this help you and anyone else looking to get started with using Azure SQL in their Power Apps application -