CQRS with Microsoft Azure SQL Server & Redis Cache


Command Query Responsibility Segregation (CQRS) is an advanced application/integration pattern that is very rarely done correctly. CQRS can add unnecessary complexity and risk to systems, however, if designed correctly, CQRS can be a tremendous benefit.

In this post, I’ll demonstrate a simple CQRS service-level architecture.

Command Query Responsibility Segregation

CQRS is an application design pattern that separates a CREATE, UPDATE & DELETE Operation from a READ Operation. The aim of CQRS is to provide separations of concern and enforce the single responsibility principle.

In a PaaS setting, CQRS is extremely beneficial for managing costs, security and operational maintenance.

CQRS can exist at two levels; Class and Service levels. At the Class level, both command and query are separated. At the Service level, the physical datastores are separated.

Advantages of CQRS

  1. Separation of concerns. Both the READ and QUERY models can be managed independently providing a greater degree of flexibility.
  2. Scalability. READ operations tend to be higher than CREATE operations, for this reason, a READ Datastore might be a high-performing Azure SQL instance or Cosmos DB.
  3. Cost-effectiveness. The WRITE operation might write to a datastore that is not as compute-intensive as a READ model. For this reason, two datastores in some scenarios provide a better cost-benefit ratio than having a single datastore that is managing both the READ & WRITE operations.
  4. Security. READ models can be constructed to provide only relevant columns only. This supports any geographic data laws.
  5. Redundancy. Most CQRS solutions are designed to be redundant, that is, the datastores will be designed to have some form of high-availability.

CQRS Architecture with Redis Cache

In this CQRS Architecture, two physical datastores will be separated This is to provide a greater degree of isolation data isolation. Cosmos DB will be used in order to avoid building an O/RM.

  1. Front-end application built on Node.JS (Express).
  2. API Gateway built on Azure API Management Gateway
  3. Command Service built with Azure Functions for CREATE, UPDATE & DELETE operations
  4. Write Data Store built on Azure Single Instance SQL/Server & DB.
  5. Query Service built with Azure Functions for READ Operations Only.
  6. Read Data Store built on Azure Cache for Redis: https://docs.microsoft.com/en-us/azure/api-management/api-management-howto-cache-external

Command Service

HTTP Requests received from the Front-end application will be routed through APIM. The Command Service will operate through an Azure Serverless Function. The Datastore the Command service will communicate with is Azure SQL Server.

Azure Resource Provision

# Variable block
let "randomIdentifier=$RANDOM"

# Create a resource group
echo "Creating $resourceGroup in "$location"..."
az group create --name $resourceGroup --location "$location" --tags $tag

# Create an Azure storage account in the resource group.
echo "Creating $storage"
az storage account create --name $storage --location "$location" --resource-group $resourceGroup --sku $skuStorage

# Create a serverless function app in the resource group.
echo "Creating $functionApp"
az functionapp create --name $functionApp --storage-account $storage --consumption-plan-location "$location" --resource-group $resourceGroup --functions-version $functionsVersion

# Create a single SQL Server & database, also configure a firewall rule
location="East US"

echo "Using resource group $resourceGroup with login: $login, password: $password..."
echo "Creating $resourceGroup in $location..."
echo "Creating $server in $location..."

# Create SQL Server
az sql server create --name $server --resource-group $resourceGroup --location "$location" --admin-user $login --admin-password $password
echo "Configuring firewall..."
az sql server firewall-rule create --resource-group $resourceGroup --server $server -n AllowYourIp --start-ip-address $startIp --end-ip-address $endIp
echo "Creating $database on $server..."

# Create SQL Database
az sql db create --resource-group $resourceGroup --server $server --name $database --sample-name AdventureWorksLT --edition GeneralPurpose --family Gen5 --capacity 2 --zone-redundant true # zone redundancy is only supported on premium and business critical service tiers

# Create and manage a Standard Redis Cache
# Variable block

# Create a Standard C0 (256 MB) Redis Cache
echo "Creating $cache"
az redis create --name $cache --resource-group $resourceGroup --location "$location" --sku $sku --vm-size $size

# Get details of an Azure Cache for Redis
echo "Showing details of $cache"
az redis show --name $cache --resource-group $resourceGroup 

# Retrieve the hostname and ports for an Azure Redis Cache instance
redis=($(az redis show --name $resourceGroup --resource-group $resourceGroup --query [hostName,enableNonSslPort,port,sslPort] --output tsv))

# Retrieve the keys for an Azure Redis Cache instance
keys=($(az redis list-keys --name contosoCache --resource-group contosoGroup --query [primaryKey,secondaryKey] --output tsv))

# Display the retrieved hostname, keys, and ports
echo "Hostname:" ${redis[0]}
echo "Non SSL Port:" ${redis[2]}
echo "Non SSL Port Enabled:" ${redis[1]}
echo "SSL Port:" ${redis[3]}
echo "Primary Key:" ${keys[0]}
echo "Secondary Key:" ${keys[1]}

# Create an APIM Developer Tier Instance
az apim create --name myapim --resource-group $resourceGroup --location "$location" \
  --publisher-name Eax360 --publisher-email syed.hussain@eax360.com \

Login with your Private Access to read the full post.

Leave a comment