Rupesh Tiwari

Rupesh Tiwari

I help students and professionals to become Full Stack Software Developer in less than a year!

Where to Store Application Data in Azure

11 minute read

Data is very essential and now a days data is everything. So you have application in cloud and want to explore which type of data where to store in Azure. Read this article to learn which Azure store is best for you.

Storing Data in Azure options

  1. Azure SQL Database
  2. Azure Databases for MySQL
  3. Azure Databases for PostgresSQL
  4. Azure Databases for MariaDB
  5. Azure Cosmos DB
  6. Azure Storage
    1. File Storage
    2. Blog Storage
    3. Table Storage
    4. Disk Storage
    5. Queue Storage
  7. Azure Synapse Analytics
  8. Azure Data Lake Store

Azure SQL Database

💁 Important

Azure SQL Database can store Relational Data. It is optimized for OLTP (Online Transactional Processing).

  • SQL Server in the cloud
  • Managed and Scalable by Azure
  • Highly available 99.99%
  • Redundant, data is replicated 3 times in the local data-center and 3 times to another data-center. Data is backed up every 5 minutes and every hour a full backup is made.
  • Relational data can be used as table rows and columns.
  • Use it with Microsoft tools
    • SSMS (SQL Server Management Studio), Azure Portal, Visual Studio
  • Advanced capabilities
    • Geo-replication (single writable node): which actively replicate data to other geographical region. Other geo replicable databases are read-only.
    • Automatic tuning: where Azure SQL analyzes which queries are slow and it will apply required indexes to improve speed.
    • Threat detection: it can detect malicious behavior on the database and alert you to take action.
    • Dynamic Data Masking: which can automatically mask sensitive data like password, credit card number etc. So un-authorized or un-wanted user can only see masked data only.

Learn more about Azure SQL Database

Azure Databases for MySQL

💁 Important

Azure Databases for MySQL can store Relational Data. It is optimized for OLTP (Online Transactional Processing).

  • MySQL database over the cloud
  • Managed and Scalable by Azure
  • Highly available 99.99%
  • Redundant, data is replicated 3 times in the local data-center and 3 times to another data-center. Data is backed up every 5 minutes and every hour a full backup is made.
  • Relational data can be used as table rows and columns.
  • Works well in the LAMP stack
    • Linux, Apache server, MySQL and PHP
  • Use it with tools for MySQL because most of the Azure tools are not supported for MySQL database.
    • MySQL Workbench and SQLyog, drivers for PHP, Java, Node.js and .NET.
    • Can integrate with Azure App Service

MySQL is open-source database. Learn more about Azure Databases for MySQL

Azure Databases for PostgreSQL

💁 Important

Azure Databases for PostgreSQL can store Relational Data. It is optimized for OLTP (Online Transactional Processing).

  • PostgresSQL in the cloud
  • Managed and Scalable by Azure
  • Highly available 99.99%
  • Redundant, data is replicated 3 times in the local data-center and 3 times to another data-center. Data is backed up every 5 minutes and every hour a full backup is made.
  • Relational data can be used as table rows and columns.
  • Flexibility
    • Write functions in several languages (Ruby on Rails, Python with Django, Java with Spring Boot, and Node.js)
    • Use PostgresSQL extensions (PLV8, and PostGIS)
  • Use it with tools for PostgreSQL
    • pgAdmin
    • Hyperscale (Citus) extension to scale to hundreds of nodes with no application rewrites.

PostgreSQL is open-source database. Learn more about Azure Databases for PostgreSQL

Azure Databases for MariaDB

💁 Important

Azure Databases for PostgreSQL can store Relational Data. It is optimized for OLTP (Online Transactional Processing).

  • MariaDB in the cloud
  • Managed and Scalable by Azure
  • Highly available 99.99%
  • Redundant, data is replicated 3 times in the local data-center and 3 times to another data-center. Data is backed up every 5 minutes and every hour a full backup is made.
  • Relational data can be used as table rows and columns.
  • Supports different storage engines that allows to store data in other format like
    • Apache Cassandra, CSV, ColumnStore
  • Use it with the tools for MariaDB
    • Database Workbench and SQLyog

Did you know MariaDB is forked out from MySQL and open source? Learn more about Azure Databases for MariaDB

Azure Cosmos DB

💁 Important

Azure Cosmos DB can store Document Data. It is optimized for OLTP (Online Transactional Processing).

  • Distributed, massive scale database
  • Use it with several APIs to work with your data in many different ways and they abstract the way that Azure Cosmos DB stores and handles the data.
    • DocumentDB API
    • MongoDB API
    • Table API
    • Graph API
    • Apache Cassandra API
  • Since it is noSQL you do not need to predefine the schema
  • Automatic indexing: Cosmos DB will automatically create and apply and manages indexes required on data. You can customize the indexing policy if you want.
  • Geo-replication (single writable node): which actively replicate data to other geographical region. Other geo replicable databases are read-only.
  • Unique SLA is offered by CosmosDB
    • SLA 99.99% availability
    • SLA 99.99% availability for read operation
    • Latency on read < 10ms, writes < 15ms This latency counts from the Cosmos DB to other Azure services.

Apache Cassandra is a free and open-source, distributed, wide-column store, NoSQL database management system designed to handle large amounts of data across many commodity servers, providing high availability with no single point of failure. Learn more about Azure Cosmos DB

Azure Storage

💁 Important

Azure Storage can store Unstructured/Document Data. It is optimized for OLTP (Online Transactional Processing).

  • Massive scale, multiple-purpose storage.
  • Multiple models in a storage account
    • File Storage
    • Blob Storage
    • Queue Storage
    • Table Storage
    • Disk Storage

All storage come with overall features and capabilities that Azure Storage provides like

  • Shared Access Signatures: provides temporary access to some operations on storage type like Blob storage. This is useful for implementing the valet key pattern.
  • Firewalls and Virtual Network using this you can increase security to your Azure storage data.
  • Data encryption at rest. Azure storage will encrypt and decrypt the data for you out of the box.
  • Storage replication
    • Locally-redundant : 3 copies of the data stored in the local data-center
    • Geo-redundant: In addition to Locally-redundant, it will store 3 copies of data in the other geographical data-center
    • Read-access-geo-redundant: You can also create readable Geo-redundant also.

Learn more about Azure Storage

Azure File Storage

💁 Important

Azure File Storage can store Unstructured Data. It is optimized for OLTP (Online Transactional Processing).

  • Really useful for Lift and Shift files to the cloud. Useful when you want to keep the file available across the applications.
  • File storage based on SMB, NSF
  • Mount an Azure File share in
    • Windows
    • MacOS
    • Linux
  • You get all Azure Storage features
    • Shared Access Signatures

Learn more about Azure File Storage

Azure Blob Storage

💁 Important

Azure Blob Storage can store Unstructured Data. It is optimized for OLTP (Online Transactional Processing).

  • Store unstructured data in the cloud like video, audio etc.
  • Multiple types of blob can be stored
    • Block: blobs are optimized for efficient upload of large file.
    • Page: blobs are optimized for random read and write access like VHD files for virtual machine image.
    • Append: these blobs can only be added onto existing blob and the blob can’t be changed. Like adding logs on log blog.

  • Multiple storage tiers

    • Premium: access blobs anytime with great SSD performance and very expensive
    • Hot: access blobs anytime with great performance and expensive
    • Cool: cheaper but take more time to fetch blob.
    • Archive: store offline data, cheaper storage and costly retrieval
  • You get all Azure Storage features
    • Firewalls and Virtual Network
    • Shared Access Signatures

Learn more about Azure Blob Storage

Azure Table Storage

💁 Important

Azure Table Storage can store Unstructured Data. It is optimized for OLTP (Online Transactional Processing).

  • A NoSQL key-value store for rapid development using massive semi-structured datasets.
  • Flexible data schema: the table schema is flexible means one row might have 5 the other can have 23 columns.
  • You get all Azure Storage features
    • Firewalls and Virtual Network
    • Shared Access Signatures

Learn more about Azure Blob Storage

Azure Disk Storage

💁 Important

Azure Disk Storage can store Unstructured Data. It is optimized for OLTP (Online Transactional Processing).

  • High-performance, highly durable block storage for Azure Virtual Machines.
  • Part of the premium pricing tier of Azure storage only.
  • Perfect for use in Virtual Machines.
  • You can use this to lift and shift the existing applications like a Dynamic CRM server to a VM in the cloud. And you get availability and robustness of the cloud.
  • You get all Azure Storage features
    • Firewalls and Virtual Network
    • Shared Access Signatures

Learn more about Azure Disk Storage

Azure Synapse Analytics

💁 Important

Azure Synapse Analytics can store Analytics Data. It is optimized for OLAP (Online Analytical Processing).

  • For prepared, structured data for reporting: Azure Synapse Analytics is perfect for when you want to store a prepared and structured data specifically for reporting purposes in so-called data marts.
  • Relational Data: You only stored relational data which is cleaned and ready to be used for the users that use a reporting tool like Power BI. It is similar to Azure SQL database optimized for reporting purpose.
  • Large amounts of data up to 1PB ~ 1 million GB can be stored in it.
  • Data encryption at rest
    • Transparent Data encryption just like Azure SQL database.

Learn more about Azure Synapse Analytics

Azure Data Lake Store

💁 Important

Azure Data Lake Store can store Analytics Data. It is optimized for OLAP (Online Analytical Processing).

  • For structured and unstructured data for reporting: in it’s native format.
  • Data in native format: so you don’t need to clean and prepare before you put the data in Azure Data Lake Store
  • No Schema definition: you don’t have to create a data schema up front, which you must do in Azure Synapse Analytics. You can use Azure Data Lake to just store the big data and further move them in to Azure Synapse by cleaning and preparing it.
  • Optimized for reporting purposes: high performance reporting and analytics workloads are available.
  • No File or Datastore size limits: You can store large amount of unlimited data. The only limit is your 💵 budget :)
  • Data encryption at rest: out of the box you get this.

Learn more about Azure Data Lake Store

Online Transaction Processing (OLTP) DataStores

Online Transactional Processing (PLTP) datastores are optimized for the transactional processing for create, read, update and delete operations. These are typically used as data storage for live applications like website or mobile apps.

List of Data Storage supporting OLTP

  1. Azure SQL Database
  2. Azure Databases for MySQL
  3. Azure Databases for PostgresSQL
  4. Azure Databases for MariaDB
  5. Azure Cosmos DB
  6. Azure Storage
    1. File Storage
    2. Blog Storage
    3. Table Storage
    4. Disk Storage
    5. Queue Storage

Online Analytical Processing (OLAP) DataStores

Online Analytical Processing (OLAP) datastores are optimized for analytical processing like crunching of data for reporting purposes.

Below are the list of OLAP datastores

  1. Azure Synapse Analytics
  2. Azure Data Lake Store

Comparing the DataStores

Let’s compare datastores as per OLAP and OLTP categories.

Compare Data Stores for OLTP

💁 Important

The main difference among Azure SQL, MySQL, PSQL and MariaDB is the ecosystem they are in and the skillset that you need for them. If you are comfortable on LAMP stack and working with MySQL then Azure Databases for MySQL or MariaDB is good for you. If you are comfortable working in pgAdmin or similar tool and you are not using T-SQL to manage database then use Azure PostgreSQL.

Scenarios SQL MySQL PSQL MariaDB Cosmos DB Storage
Relational data ✔️ ✔️ ✔️ ✔️    
Semi- or non-relational data data         ✔️ ✔️
Can use Microsoft tools to manage ✔️       ✔️ ✔️
Advanced querying capabilities ✔️ ✔️ ✔️ ✔️ ✔️  
Use multiple APIs to access data         ✔️  

Compare Azure Storage Type Services

💁 Important

All Azure storage types have the same Azure Storage Features like Shared Access Signatures and data encryption at rest.

Scenarios File Blob Table Disk
Lift and shift files to the cloud ✔️      
Lift and shift applications to the cloud       ✔️
Store (large) unstructured data   ✔️    
Store (small) semi-structured data     ✔️  

Compare Data Stores for OLAP

💁 Important

The Data Lake Store stores the relational structured data in text file or hierarchical folders. You can also define the schema in Azure Data Lake store but this is not compulsory like Azure Synapse Analytics store.

Scenarios Azure Synapse Analytics Azure Data Lake Store
Relational data ✔️ ✔️
Non-relational data like documents   ✔️
Requires you need to define data schema ✔️  
No file- or database size limits   ✔️
Use when you know which questions you want the data to answer ✔️  

Thanks for reading my article till end. I hope you learned something special today. If you enjoyed this article then please share to your friends and if you have suggestions or thoughts to share with me then please write in the comment box.

Become full stack developer 💻

I teach at Fullstack Master. If you want to become Software Developer and grow your carrier as new Software Engineer or Lead Developer/Architect. Consider subscribing to our full stack development training programs. You will learn Angular, RxJS, JavaScript, System Architecture and much more with lots of hands on coding. We have All-Access Monthly membership plans and you will get unlimited access to all of our video courses, slides, download source code & Monthly video calls.

  • Please subscribe to All-Access Membership PRO plan to access current and future angular, node.js and related courses.
  • Please subscribe to All-Access Membership ELITE plan to get everything from PRO plan. Additionally, you will get access to a monthly live Q&A video call with Rupesh and you can ask doubts/questions and get more help, tips and tricks.

Your bright future is awaiting for you so visit today FullstackMaster and allow me to help you to board on your dream software company as a new Software Developer, Architect or Lead Engineer role.

💖 Say 👋 to me!
Rupesh Tiwari
Founder of Fullstack Master
Email: rupesh.tiwari.info@gmail.com
Website: RupeshTiwari.com

You May Also Enjoy

Mandatory Algorithms for Coding Interview

3 minute read

Basic algorithms that you must know if you want to crack your coding interview. In case you are starting to prepare for coding practice then make sure you...

How to improve Reliability in the cloud?

9 minute read

Did you get a phone call from your customer saying they want to improve their application reliability while you move their workload to the cloud? If yes t...