Architecture
SharePoint lists vs Dataverse vs SQL: choosing a data backend
Every Power App and internal tool sits on this quietly critical decision. Get it right and nobody ever mentions it; get it wrong and you rebuild in eighteen months. Here's the decision made simple.
The three candidates, honestly characterised
SharePoint lists — the licence-free workhorse. Included in Microsoft 365, familiar, quick to stand up, and perfectly good for straightforward data: registers, trackers, request logs. Its limits are real: it's not relational (lookups exist, but chaining them is pain), complex filtering degrades as lists grow into tens of thousands of rows, and enforcing data integrity is largely on the honour system. Brilliant starter home; poor permanent address for anything complex.
Dataverse — the platform-native database. Microsoft's business data service: properly relational, strong security down to row and column level, business rules enforced at the data layer, audit built in, and first-class citizenship in Power Apps and Power Automate. The catch is licensing — Dataverse means premium Power Platform licences, a real per-user monthly cost that changes the arithmetic for large user counts. When the data is genuinely business-critical and relational, it's usually worth it; that's what "the app must not lie" costs.
SQL (Azure SQL / PostgreSQL) — the engineer's choice. Maximum power, performance and flexibility; the natural backend when data volumes are serious, when systems beyond the Microsoft stack need the same data, or when a bespoke application sits alongside the low-code one. Costs are modest and capacity-based rather than per-user — but you need engineering capability in the loop, because databases don't administer themselves.
The decision table
| Your situation | Sensible backend |
|---|---|
| Simple tracker/register, one team, modest rows | SharePoint list |
| Relational business data, integrity matters, users in the dozens | Dataverse |
| Large volumes, multiple systems reading/writing, bespoke apps in play | SQL |
| Customer-facing at scale | SQL (with proper engineering) |
The two classic failure patterns
Outgrowing SharePoint in silence. The list that started with 500 rows hits 40,000; views crawl, "delegation" warnings appear, and duplicate records breed because nothing enforces uniqueness. The app gets blamed; the backend was the culprit. If a system is becoming business-critical, migrate the data layer before the pain — it's a far smaller job than after.
Buying Dataverse for a shopping list. The reverse error: premium licensing for data a SharePoint list would hold happily. Licence cost should follow data criticality, not enthusiasm.
Where we fit in
Power Analytix builds exactly this kind of solution — scoped in writing, priced fixed, delivered by senior engineers. If you'd rather have it done than read about it, book a free scoping call.
Deciding in four questions
- Is the data relational — multiple entities that must stay consistent?
- What happens if a record is wrong or duplicated — shrug, or incident?
- How many users, and what does per-user licensing do at that count?
- Will anything outside the Microsoft stack need this data?
Mostly "simple/shrug/few/no" — start with SharePoint and revisit annually. Relational and critical — Dataverse. Scale, integration or bespoke ambitions — SQL. And if you're staring at a struggling list right now, migrating it is one of the most routine rescues we perform.