cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mogulman52
Helper I
Helper I

CDS vs Azure SQL Database

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?

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Anonymous
Not applicable

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.

 

 

View solution in original post

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

 

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

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.

 

 

View solution in original post

Anonymous
Not applicable

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

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.

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

 

View solution in original post

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.

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
Advocate II
Advocate II

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 - 

 

thumbsml.jpg

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,476)