Choosing SQL and No-SQL database in Microsoft Azure Cloud services

 

Overview

Pre-requisites: Glimpse of cosmos DB and SQL server

You might have come across various database platforms when it comes to storage. This article would help you to understand the scenarios and how you may have the considerations when it comes to choosing the database into the real world scenarios.

Database as storage

On a broader scale we would discuss about No-SQL database and Azure SQL Database which can cover various scenarios. Of-course, Azure provides variety of database other than we are discussing in this article. You need to be considerate when it comes to chose with PaaS, Native support, ability to configure settings and cost.

Below picture indicates a thumb rule to consider database and No-SQL APIs.

Picture 1: Database scenarios

Scenarios

When it comes to choosing the API on Azure Cosmos DB, there needs to be 

number of considerations that should be taken in to account.

Table API: When you want to store just a key value pair such as configurations those can be reused across applications, common look-ups are the candidates of Table API Storage Account Table both Cosmos DB Table are the examples. If you are looking for SLA bound performance, global distribution then cosmos DB Table API can be a consideration. However; if you have no such requirement, Storage Account Table is the cheaper option. Though Table API is not bound to SAL for performance, Microsoft claims to be fast enough.

Cassandra API is another tabular storage available in Cosmos DB. However it stores the data in columns.

SQL API:  It is also known as document database. It stores the data in JSON format.  This can be used when you have uncertainty in the entities. e.g. Person entity may have FirstName, Last Name common but not all entities need to have secondary address. In such instance SQL API may be a best fit.

Azure SQL DB: When you have complex relations between entities and tables or migrating data from existing SQL database, then Azure SQL DB may the a candidate to choose.

Graph API: When you have number of entities and the relationships are unknown, the graph database can be a consideration. It has nodes and vertices though which nodes are connected. Example: Facebook shows you mutual friends. It may be possible that there is no relationship between those two people but still it establishes the relationship to derive the result.

Another example where you might have seen that number of retail shopping sites show that "People also bought this product along with current product you are looking at." So, where you want to establish the relationships, the graph database would allow you to perform such operations.

Hope this article would have helped to understand scenarios on a high level.