Which Azure data store? Azure SQL, Cosmos DB, Blob Storage, PostgreSQL, or Dataverse and Fabric

"Azure SQL vs Cosmos DB" is how the question usually arrives, and the honest answer is that the two rarely compete. Azure SQL Database is a relational database that most line-of-business apps want; Cosmos DB is a distributed document store for the cases where you need writes in several regions, a schema that changes shape, or millions of operations a second. Before you get to that choice there are three earlier ones that settle most cases: whether Power Platform already owns the data, whether it's files rather than rows, and whether what you're really building is analytics. This guide walks the four questions in order, then puts the stores side by side.

Question 1: does Power Platform own the data?

Microsoft's own data-store guide now asks this before anything else, and it's the right order for anyone reading this site. Dataverse is the SaaS data platform behind Power Apps, Power Automate, Power Pages and Dynamics 365: relational tables, business rules, and security at the role, row and column level, all without an engine to manage. Apps reach it through its APIs rather than a SQL connection, which is the trade: less query flexibility and throughput than an Azure database, in exchange for a security model and a data model the Power Platform understands.

The rule Learn gives is to use Dataverse for the parts of a solution that Power Apps, Power Automate, Power Pages or Dynamics 365 touch, and an Azure-native database beside it for the parts that need high-throughput processing, complex analytical queries or direct control of the engine. Data moves between the two with Azure Synapse Link for Dataverse and Microsoft Fabric, not with a nightly export you wrote yourself.

What this means in practice: a canvas app whose data you were about to put in Azure SQL "to save licences" is a decision to rebuild row-level security, auditing and the connector's identity story yourself. It can be done well, and our Power Apps to Azure SQL with Entra ID guide shows how, but go in knowing that's the work.

Question 2: files or rows?

Files, binary objects, exports, backups, images and anything you'd otherwise put on a share belong in a storage account, not a database.

• Blob Storage is the general case: any size, any type, hot, cool, cold and archive tiers, and a lifecycle policy to move data down the tiers as it ages. Authorise it with Entra ID and data-plane roles, not account keys; our Blob 403 guide covers what goes wrong.
• Data Lake Storage Gen2 is Blob Storage with a hierarchical namespace: real folders, file-level permissions, and the throughput analytics engines want. It's the foundation OneLake in Fabric is built on.
• Azure Files is the SMB or NFS share for things that genuinely need a drive letter.
• Table Storage is a cheap key-value store with one index (partition key and row key) and a scalability ceiling of 20,000 operations a second per table. When you outgrow it, Cosmos DB for Table has the same data model with reserved throughput and no table-level ceiling.

Question 3: relational, or documents?

Rows that an application reads and writes one transaction at a time want a relational database unless you have a specific reason otherwise.

Azure SQL Database is the managed relational default: T-SQL, SQL or Entra sign-in, row-level security, and a serverless tier that pauses when idle (read our error 40613 guide before you turn that on). Choose SQL Managed Instance when you're moving an existing SQL Server estate and need instance-level features, and SQL Server on a VM only when you need the operating system too. Azure Database for PostgreSQL is the open-source choice, with its extensions (vector search among them), and the usual target for Oracle and PostgreSQL migrations; Azure Database for MySQL serves the MySQL world the same way.

Azure Cosmos DB is for a different shape of problem: a document model that can change without migrations, automatic indexing of every property, single-digit-millisecond latency anywhere in the world, and the thing no relational service in the list offers, writes in several regions at once. It bills in request units per second, which is where new users hit 429s, and it has no row-level security, so per-user filtering is your application's job. Reach for it when you need those properties, not because "NoSQL scales". For a MongoDB-compatible service, Learn now points at Azure DocumentDB; for an in-memory cache beside any of these, Azure Managed Redis.

Two things not to build on: Azure Cosmos DB for PostgreSQL is on a retirement path and no longer recommended for new projects (Learn's replacement is the Elastic Clusters feature of Azure Database for PostgreSQL), and SQL database in Fabric, which looks like Azure SQL, accepts Entra sign-in only, so an app that needs a SQL login can't use it.

Question 4: analytics is a different question

If the workload is reporting, dashboards, a warehouse, a lake, streaming events or machine learning, the store is in Microsoft Fabric, and the operational databases above become sources. Fabric's own decision guide maps it out: an eventhouse for streaming and high-granularity event data, SQL database in Fabric for transactional workloads that want to live in Fabric, Cosmos DB in Fabric for NoSQL there, a Data Warehouse for SQL-based BI with full transactions, and a lakehouse for big data, unstructured data and data engineering. Every one of them lands in OneLake in an open table format, and OneLake shortcuts read data that already sits in a storage account or another workspace without copying it.

Where that leaves the older services: Microsoft publishes a migration path from Azure Synapse Analytics Spark workloads to Fabric (notebooks, pools, pipelines, metadata, with a migration assistant), and describes Data Factory in Fabric as the next generation of Azure Data Factory. Neither is switched off, but new analytics work is being pointed at Fabric, and Azure Databricks remains the choice when the team is code-first and wants Spark on its own terms.

Side by side

The traps

• Cosmos DB for scale you don't have. Request units are a budget you pay for every second, and the first symptom of getting it wrong is a 429, not a slow query. Most apps are relational and never need multi-region writes.
• Azure SQL instead of Dataverse to avoid licences. The saving is real; so is the rebuild of security, and the connector's shared-login trap (question 1).
• A database for files. Blobs in a table bloat backups and cost more than a storage account; keep the file in Blob Storage and the path in the row.
• Serverless by default. The pause saves money only if the database is actually idle; see question 3.
• Analytics on the operational database. Reports that scan the live tables slow the app and the report; land the data in Fabric through Synapse Link, shortcuts or Fabric's own connectors.
• A new project on a retiring service. Check the "Important" note at the top of a service's Learn overview; Cosmos DB for PostgreSQL carries one today.

Sources

• Prepare to choose a data store in Azure, Microsoft Learn (the key questions, the scenario table, the Dataverse guidance and the feature comparison)
• Choose a big data storage technology in Azure, Microsoft Learn (OneLake, Blob and Data Lake Storage Gen2, Cosmos DB)
• Use Azure Cosmos DB for Table and Azure Table Storage, Microsoft Learn (the Table Storage limits)
• Microsoft Fabric decision guide: choose a data store, Microsoft Learn
• Choose an analytical data store in Azure, Microsoft Learn (the security matrix, including row-level security and sign-in per store)
• Overview of migrating Azure Synapse Spark to Fabric, Microsoft Learn
• Troubleshoot Azure Data Factory and Synapse pipelines, Microsoft Learn (the note that Data Factory in Fabric is the next generation of Azure Data Factory)
• What is Azure Cosmos DB for PostgreSQL?, Microsoft Learn (the retirement-path note)
• SQL Server and Windows authentication, Microsoft Learn (SQL database in Fabric supports Entra identities only)