Azure SQL vs NoSQL

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 InstanceAzure SQL Elastic DatabaseAzure Database
Almost 100% compact with on-premisesDatabase share resources (CPU, IO, Memory)Built-in high availability, Disaster Recovery, upgrade
Fully Managed PaaSGood choice for heavy usage or performance peaksAn on-premises application can access data
Full Isolation & Security3 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 DocumentColumn StoreGraph 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 machinesGraph Databases are built with nodes, relationships between nodes and the properties of nodes
Mappings are usually accompanied by cache mechanisms to maximize performanceSemi-structured documents can be XML or JSON formattedKeys point to multiple columns
API is typically simpleDocuments 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.

SQLNoSQL
TypeRelationalNon-Relational
DataStructured Data stored in TablesUn-structured stored in JSON files.** Graph Database support relationship
SchemaStaticDynamic
LanguageStructure Query LanguageUn-Structured Query Language
ScalabilityVertical (Scale-up || Scale-down)Horizontal (Scale-in || Scale-out)
JoinsAllow designing complex queriesDo not allow complex queries
OLTPComfortable
SupportGreat SupportCommunity dependent, they are expanding the support model
Integrated CachingSupport In-line memorySupports Integrated caching
Flexible
TransactionACID (Atomic, Consistent, Isolated, Durable)BASE Compatibility
Auto ElasticityRequires downtime in most casesAutomatic, No outage required

Benefits

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

SQL

SQL
ManagementMicrosoft offers a Performance Dashboard, which allows the visualisation of the database performance
Elastic GroupsA database can be scaled up for an intensive process and scaled back after the process has completed.
SecurityMicrosoft integrates security features first in the cloud, e.g. Always Encrypted, Row-level security, Dynamic data masking, authentication, authorization, threat detection, auditing etc.
BackupAutomatic Backup, Restore database to point-in-time when TDE is enabled, backups are also encrypted.
PerformancePerformance is measured in database DTUs (Data Transaction Units = a blended measure of CPU, Memory, I/O)
Enterprise FeaturesNew 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
ScalabilityHorizontal scale-out, easy way to reduce capacity quickly
PerformanceBy simply adding resources, enterprises can increase performance NoSQL database.
High AvailabilityNoSQL is by design high available databases, without the complexity
Global AvailabilityBy automatically replicating data across multiple servers, this minimizes latency and ensure consistent application experience.
Flexible Data ModellingDevelopers can leverage the data types and query options that are most fit to their application. It offers a more agile development.
PricingNoSQL 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

About the Author:

George Chrysovalantis Grammatikos is an Azure Cloud Architect. He has more than 10 years experience in different technologies like BI & SQL Server Professional level solutions, Azure technologies, networking, security etc. He writes technical blogs for his blog “cloudopszone.com“, Wiki Technet articles and also participates in discussions on Technet and other technical blogs.

Reference:

Chrysovaladis Grammatikos, G. (2019). Azure SQL vs NoSQL. Available at: https://cloudopszone.com/azure-sql-vs-nosql/ [Accessed: 17th February 2020].

Share this on...

Rate this Post:

Share: