Azure SQL vs Cosmos db

Global Databases: Azure SQL vs Cosmos db

Cosmos DB is a fully managed, multi-model and globally distributed database  that offers the following capabilities:

• Capable to store relational data, semi-structured data(supports document, key-value, graph, and columnar data models)
• Fast, single-digit-millisecond data access using popular API including SQL, MongoDB, Cassandra, Tables, or Gremlin(graph).
• Data is completely transparently encrypted and secure by default
• Azure Cosmos DB natively partitions your data for high availability and scalability. Azure Cosmos DB offers 99.99% guarantees for availability, throughput, low latency, and consistency on all single-region accounts and all multi-region accounts with relaxed consistency, and 99.999% read availability on all multi-region database accounts for both reads and writes.
• Azure Cosmos DB is ISO, FedRAMP, EU, HIPAA, and PCI compliant

Geo-replication

While creating Cosmos DB account multi-region, we can choose from the following modes:

Multi-region accounts with multiple write regions.

In this mode, all copies of the database are writable at all times. If a region fails, no failover is necessary.

Multi-region accounts with a single write region.

In this mode, only the primary region contains writable databases. The data replicated to the secondary regions are read-only. You can select enable automatic failover (by default disabled) so that Cosmos DB automatically fails over the primary, writable copy of the database to another region.

In Cosmos DB, data replication is synchronous. When a change is applied, the transaction is not considered complete until replicated across the replicas. Then an acknowledgment is sent to the client. When a failure occurs, no recent changes are lost because replication has already occurred.

Common Azure Cosmos DB Use Cases

• IoT and telematics
• Retail and marketing
• Gaming
• Social Applications
• Personalization

Cosmos Db Pricing

Azure Cosmos DB bills for provisioned throughput and consumed storage by the hour. Visit here for the latest pricing here

Azure SQL Database

Azure SQL Database is a relational database-as-a-service (DBaaS)  and PaaS based on the latest stable version of Microsoft SQL Server with following deployment models like Single database, Elastic pools, Managed instance, Instance pools. We can use either:

• Active geo-replication
• Auto failover groups

Active Geo-replication

• Azure SQL Database can automatically replicate a database and all its changes from one database to replicas with the active geo-replication feature. 
• Only the primary server hosts a writable copy of the database, up to four other logical servers can be created that host read-only copies of the database.
• Managed instances of Azure SQL Database do not support Active geo-replication.
• Developers can write code that calls the failover method in the Azure SQL Database REST API.

Auto Failover Groups

An auto-failover group is a group of databases where data replicates automatically from a primary to underlying secondary servers. The data replication method is the same as active geo-replication. However, You can automate the response to a failure by defining a policy.

The replication uses the asynchronous method for the data replication for both active geo-replication and auto-failover groups. An acknowledgment is sent to the client when a change is applied to the primary replica. At this point, the transaction is considered complete, and replication occurs. If a failure occurs, the latest changes made in the primary database may not have replicated to the secondary. Keep in mind that, after a disaster, the most recent database changes may have been lost.

Click to see the latest Azure SQL Database pricing

SQL Service Tier and SLA

Please visit MSDN for the latest SLA and pricing data

Bonus Tips:

If a failure occurs,

• Azure Cosmos Db: No recent changes/data are lost because replication has already occurred
• Azure SQL database: keep in mind that, after a disaster, the most recent database changes may have been lost

Azure Data Load (ETL) Process using Azure Functions Step by Step Example

Data Load (ETL) Process using Azure Functions

Azure Functions 

are serverless and are a great solution for processing data, integrating systems, working with the internet-of-things (IoT), and building simple APIs and microservices. Consider Functions for tasks like image or order processing, file maintenance, or for any tasks that you want to run on a schedule.

Here we are talking about ETL process implementation using Azure Functions, even though Azure Data Factory is out there but if you are a c# developer you will love it. You can leverage all the benefits of the App Service Plan and/or Consumption Plan(Pay As you Go) along with Event-Driven Process and Programming Model

Azure Durable Function

Durable Function is an extension of Azure Function that lets you write stateful functions in a serverless compute environment. It allows you to define stateful workflows by writing orchestrator functions and stateful entities by writing entity/Activity functions using the Azure Functions programming model. All other things like state management, checkpoints, and restarts for you, will be taken care of by azure durable function engine and allowing you to focus on your business logic.

The primary requirement is the reader should be familiar  with Azure function and durable functions

Business Requirement

 We have CSV file dropped into the azure blob storage/container and that file should be process and data saved into the Azure SQL Server.

Design and Architecture

• Azure Function: It's a blob trigger function and starter for the data load process.
• Azure Durable Functions - Orchestrator: It's an orchestrator function that will manage the workflow/data flow activities function and all the executions 
• Azure Durable Functions - Activity: An azure function that will actually process the CSV data and will insert into the azure SQL database
• Azure SQL Server: will be in used to keep processed data
• SendGrid: will be used to send emails and acknowledgment on process completion
• Application Insights: can be used to logging the exception event etc..

Development Environment Setup:

1.  Visit MSDN for the step by step example  here is a link
2.  Required NuGet package Microsoft.Azure.WebJobs.Extensions.DurableTask

Code and Example : 

Here is a list of code screenshots

Start Function: its a blob trigger azure function that will execute automatically once any CSV file will be dropped into the container "samples-workitems"

Orchestrator Function : 

it will manage the life cycle of data workflow 

Activity Functions

perform actual data manipulation and communication with the database.

Solutions and NuGet pkg:

Bonus Points: 

1. Use Cunsputions Plans only if you are sure that you function execution time will no exceed 10minutes limit
2. Use App Service Plan if need to configure vnet andother securtity and if your function will need more than 10 minutes to complete the task just you need to configure function time out in host.json
3. Visit for more Application Patterns