cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mogulman52
Level: Powered On

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

Re: CDS vs Azure SQL Database

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

Super User
Super User

Re: CDS vs Azure SQL Database

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

6 REPLIES 6
Anonymous
Not applicable

Re: CDS vs Azure SQL Database

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

Highlighted
Anonymous
Not applicable

Re: CDS vs Azure SQL Database

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
Level: Powered On

Re: CDS vs Azure SQL Database

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.

Super User
Super User

Re: CDS vs Azure SQL Database

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

mogulman52
Level: Powered On

Re: CDS vs Azure SQL Database

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.

Super User
Super User

Re: CDS vs Azure SQL Database

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...

 

Helpful resources

Announcements
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

New Badges

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors
Users online (5,991)