By reading this post, you will be able to compare and choose the right one service for our database deployments (SQL or NoSQL).

Definition

SQL: A relational database management system (RDBMS) is a program that lets you create, update, and administer a relational database.

NoSQL: NoSQL is a class of database management systems (DBMS) that do not follow all of the rules of a relational DBMS and cannot use traditional SQL to query data.

Types Of Databases

At the tables below we can see the types of SQL and NoSQL databases that are available.

SQL

There is three SQL type of databases SQL Managed Instance, Azure Elastic databases and Azure databases.

SQL  Database Managed Instance Azure SQL Elastic Database Azure Database
Almost 100% compact with on-premises Database share resources (CPU, IO, Memory) Built-in high availability, Disaster Recovery, upgrade
Fully Managed PaaS Good choice for heavy usage or performance peaks An on-premises application can access data
Full Isolation & Security 3 elastic pools service tiers (Basic, Standard, Premium) Eliminates hardware and administrative costs

 

 

NoSQL

Most common NoSQL types of databases are four, Key-Value Store, Document, Column Store, and Graph Database.

 

Key-Value Store

 

Document

Column Store

Graph Database

Every record is stored as a {key} with a value. Records stored as
documents”. Key is always strings and values can be string, numeric, Boolean, arrays etc.
Store and process very large amounts of data distributed over many machines Graph Databases are built with nodes, relationships between nodes and the properties of nodes
Mappings are usually accompanied by cache mechanisms to maximize performance Semi-structured documents can be XML or JSON formatted Keys point to multiple columns
API is typically simple Documents can be retrieved with queries

 

NoSQL Database Types Diagrams

SQL vs NoSQL

At the table below we can find the difference between SQL and NoSQL databases.

SQL NoSQL
Type Relational Non-Relational
Data Structured Data stored in Tables Un-structured stored in JSON files.

** Graph Database support relationship

Schema Static Dynamic
Language Structure Query Language Un-Structured Query Language
Scalability Vertical (Scale-up || Scale-down) Horizontal (Scale-in || Scale-out)
Joins Allow designing complex queries Do not allow complex queries
OLTP Comfortable
Support Great Support Community dependent, they are expanding the support model
Integrated Caching Support In-line memory Supports Integrated caching
Flexible
Transaction ACID (Atomic, Consistent, Isolated, Durable) BASE Compatibility
Auto Elasticity Requires downtime in most cases Automatic, No outage required

 

Benefits

At the tables below we can read about the benefits that SQL and NoSQL databases are offers.

SQL

SQL
Management Microsoft offers a Performance Dashboard, which allows the visualisation of the database performance
Elastic Groups A database can be scaled up for an intensive process and scaled back after the process has completed.
Security Microsoft integrates security features first in the cloud, e.g. Always Encrypted, Row-level security, Dynamic data masking, authentication, authorization, threat detection, auditing etc.
Backup Automatic Backup, Restore database to point-in-time when TDE is enabled, backups are also encrypted.
Performance Performance is measured in database DTUs (Data Transaction Units = a blended measure of CPU, Memory, I/O)
Enterprise Features New features are added much faster on cloud than on-premise. These features can be, Online indexing, partitioning, compression, column store indexes, in-memory capabilities etc.

 

NoSQL

NoSQL
Scalability Horizontal scale-out, easy way to reduce capacity quickly
Performance By simply adding resources, enterprises can increase performance NoSQL database.
High Availability NoSQL is by design high available databases, without the complexity
Global Availability By automatically replicating data across multiple servers, this minimizes latency and ensure consistent application experience.
Flexible Data Modelling Developers can leverage the data types and query options that are most fit to their application. It offers a more agile development.
Pricing NoSQL databases are a low cost solution in comparison  SQL databases.

 

What Azure Offers

At the image below we can see what Database services Azure offers.

NoSQL Azure Databases

  • Storage Tables: The Azure Storage service offers highly available storage. Within the Storage service, there is a Tables feature that allows users to create NoSQL entities within a key-value store.
  • Azure Cosmos DB: Azure Cosmos DB is an Azure-native database service that focuses on providing a high-performance database regardless of your selected API or data model. Azure Cosmos DB offers multiple APIs and models that can be used interchangeably
    for various application scenarios.
  • Redis Cache: Redis Cache is an implementation of the Redis Database engine that is offered as a service on Azure. Using the Redis Cache service, one could create a cache instance quickly using the key-value based store.

 

SQL Azure Databases

 

  • Azure SQL Database: Azure SQL Database is a DaaS (Database As A Service) cloud offering by Microsoft. In this service, Azure is responsible for database common administration tasks, hypervisors, networking, bare-metal hardware etc. offers
    automating scale up and scale down resources on-demand and finally, the user pays according to their usage of the service.
  • Azure SQL Managed Instance: This is a fully managed SQL Server instance hosted in Azure Cloud, communicates with on-premise LAN or Azure VNet and provides most SQL Server features to applications.<
  • Azure Elastic Pool: Elastic Pool is a set of databases which they shared the resources. This is a quite cost-effective service for deployments with a large number of databases.

How to understand what service to choose

There are three basic questions that we can ask ourselves to get an answer for the type of database that it is better to use on our deployment.

  • Does the application work with relatively simple data?
  • Did we want a low-cost managed service?
  • Does the app need to work with a large amount of data?

 

See Also


Share This