Azure SQL Database and Cosmos DB

Azure SQL Database: The Relational Foundation

Azure SQL Database is a platform-as-a-service offering built on the industry-standard relational engine, designed to handle transactional workloads where data integrity and consistency are non-negotiable. By abstracting away the underlying infrastructure, it automates backups, high availability, and performance tuning, allowing developers to focus on query optimization and schema design. The core strength of this service lies in its support for ACID properties, ensuring that complex financial or administrative transactions are processed reliably, even in the event of system failures. You should reach for this service when your application requires complex joins, referential integrity across multiple tables, or existing procedural code migration. Because it utilizes a structured schema, it provides a highly predictable performance profile for well-defined queries, making it the bedrock for enterprise business applications that rely on structured reports and stable, consistent data models that do not change frequently.

-- Create a standard relational table for user orders
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT NOT NULL,
    OrderDate DATETIME DEFAULT GETDATE(),
    TotalAmount DECIMAL(18, 2)
);
-- Insert a sample transaction record
INSERT INTO Orders (OrderID, CustomerID, TotalAmount) 
VALUES (101, 5001, 299.99);

Cosmos DB: Global Distribution and NoSQL Flexibility

Cosmos DB is a globally distributed, multi-model database service designed for scenarios requiring extreme horizontal scale and low-latency access across disparate geographic regions. Unlike traditional databases, it abandons the requirement for a strict, predefined schema, allowing you to store JSON documents that evolve alongside your application. The fundamental innovation here is its ability to provide tunably consistent data replication; you choose the level of consistency—from strong to eventual—that aligns with your application's tolerance for lag versus performance speed. This service is ideal for high-velocity telemetry, global retail product catalogs, or applications requiring near-instant responsiveness for users regardless of their physical location. Because data is partitioned across shards, you must carefully choose a partition key that ensures even distribution of traffic to avoid hotspots. By leveraging this architecture, you gain the capability to handle millions of requests per second while maintaining millisecond response times globally.

// Conceptual JSON document structure for Cosmos DB
{
  "id": "user_profile_001",
  "username": "jdoe",
  "preferences": {
    "theme": "dark",
    "notifications": true
  },
  "lastLogin": "2023-10-27T10:00:00Z"
}

Choosing Between Structured and Unstructured Data

The decision to use Azure SQL Database versus Cosmos DB should primarily be driven by the nature of your data relationships and the expected evolution of your data model. If your data is highly relational, requires deep analytical joins across complex entities, and maintains a fairly static schema, Azure SQL Database provides a robust ecosystem that enforces data quality at the engine level. Conversely, if your application data is semi-structured, highly dynamic, or requires massive global scale where traditional vertical scaling is insufficient, Cosmos DB is the superior choice. The cost of migration from a rigid relational schema to a NoSQL document-based approach is high, so selecting the correct paradigm early in the development lifecycle is critical. Consider the query patterns: if you need to perform ad-hoc reporting and complex set operations, the relational engine is optimized for these tasks, whereas if you need point-lookups and simple range queries at extreme concurrency, the document model is more efficient.

-- T-SQL snippet to retrieve joined relational data
SELECT O.OrderID, C.CustomerName
FROM Orders O
JOIN Customers C ON O.CustomerID = C.CustomerID
WHERE O.TotalAmount > 100;

Performance and Scalability Patterns

Performance in Azure SQL Database is managed via Database Transaction Units or vCores, which provide a predictable resource footprint for your queries. As your load increases, you can scale these resources vertically with minimal downtime, ensuring that large, compute-intensive operations do not starve critical transactional processes. In contrast, Cosmos DB scales by adding Request Units, which represent the consumption of CPU, memory, and IOPS needed to serve a request. Because Cosmos DB is distributed by design, performance is tied to how effectively you partition your data; a well-chosen partition key allows the service to route requests directly to the relevant physical shard without a global broadcast search. You should reason about your data access patterns before deployment: if your queries frequently touch a specific subset of data, align your partitioning strategy to keep that data localized, thereby reducing network latency and improving throughput across all globally distributed replicas.

// Example of selecting a partition key in Cosmos DB container
// Choosing 'userId' as the partition key ensures queries for specific
// users are routed efficiently to a single physical partition.
const containerDefinition = {
  id: "UserSessions",
  partitionKey: { paths: ["/userId"] }
};

Security, Governance, and Disaster Recovery

Both services provide comprehensive security features, including identity management through Entra ID, transparent data encryption at rest, and firewall controls to restrict network access to authorized subnets only. For Azure SQL Database, features like dynamic data masking and row-level security allow fine-grained access control, ensuring that sensitive information is visible only to authorized personnel. Cosmos DB provides similar granular control using role-based access, but because it is a document store, security often relies on application-level logic to sanitize and validate input before persistence. Regarding disaster recovery, Azure SQL Database offers active geo-replication to secondary regions with automated failover capabilities to maintain business continuity. Cosmos DB takes a slightly different approach by allowing multi-region writes, where every region is essentially an active participant in the database, offering near-zero recovery time objectives by design. Designing for high availability requires an understanding of these recovery models so your architecture can survive regional outages seamlessly.

-- Enable Transparent Data Encryption (TDE) for Azure SQL
ALTER DATABASE [ProductionDB] SET ENCRYPTION ON;

Interview questions

All Microsoft Azure interview questions →

Check yourself

Take the full Microsoft Azure quiz →