This chapter will teach you everything about databases in Azure, starting from absolute basics. We’ll explain what databases are, why you need them, and how to choose and use the right database service for your needs.
Without a database (storing data in text files or JSON files):
Problems:1. Slow searching - Finding one customer in 1 million records = read entire file - Takes seconds or minutes2. No concurrent access - Two people update same file = data corruption - Must lock file (only one person at a time)3. No relationships - How do you connect customers to their orders? - Must manually search and link data4. No data integrity - Nothing prevents duplicate customers - Nothing ensures email format is valid - Easy to corrupt data5. No backup/recovery - File corrupted = everything lost - No automatic backups
With a database:
Solutions:1. Fast searching - Indexes allow instant lookups (milliseconds) - Can search by name, email, ID, anything2. Concurrent access - 1000s of users can read/write simultaneously - Database handles conflicts automatically3. Relationships - Customer has many Orders (foreign keys) - Database enforces relationships4. Data integrity - Constraints prevent bad data - Transactions ensure consistency - No duplicates (unique constraints)5. Backup/recovery - Automatic backups every day - Point-in-time restore - Disaster recovery built-in
Customers:- ID, Name, Email, Phone, Address, Created DateProducts:- ID, Name, Description, Price, Stock Quantity, CategoryOrders:- ID, Customer ID, Order Date, Total Amount, StatusOrder Items (what's in each order):- ID, Order ID, Product ID, Quantity, PriceReviews:- ID, Product ID, Customer ID, Rating, Comment, Date
Without Database (text files):
customers.txt:1,John Doe,john@email.com,555-1234,123 Main St,2024-01-152,Jane Smith,jane@email.com,555-5678,456 Oak Ave,2024-01-16orders.txt:1,1,2024-02-01,99.99,Shipped2,2,2024-02-02,149.99,DeliveredHow do you find all orders for customer "John Doe"?- Read customers.txt, find John's ID (1)- Read ALL of orders.txt, find orders with Customer ID = 1- If 1 million orders = very slow!
With Database (SQL):
-- Find all orders for John Doe (instant, even with millions of orders)SELECT o.*FROM Orders oJOIN Customers c ON o.CustomerID = c.IDWHERE c.Name = 'John Doe';-- Result in milliseconds, even with 10 million orders
What it is: Data stored in tables with rows and columns, connected by relationships.Real-World Analogy: Filing Cabinet with Cross-References
Think of a library:- Customer card catalog (one drawer)- Book catalog (another drawer)- Checkout cards (third drawer)Cross-references:- Customer card references their checkouts- Checkout card references the book- Everything linked by ID numbersSame concept as relational databases!
When to Use Relational:
✅ Data has clear relationships (customers → orders → items)✅ Need ACID guarantees (banking, e-commerce)✅ Need complex queries (joins, aggregations)✅ Data structure is consistent✅ Strong data integrity requiredExamples:- E-commerce orders- Banking transactions- Employee management- Inventory systems- ERP/CRM systems
What it is: Flexible data storage without fixed schemas.Real-World Analogy: Boxes with Labels
Think of storage boxes:- Each box (document) can contain different things- Box 1: Customer info + preferences + purchase history (all in one)- Box 2: Different customer with different fields- No need for everything to match- Just search by label
When to Use NoSQL:
✅ Flexible schema (fields change frequently)✅ Need massive scale (millions of records)✅ Global distribution (users worldwide)✅ Semi-structured data (JSON documents)✅ High read/write throughputExamples:- User profiles- Product catalogs- Social media posts- IoT sensor data- Gaming leaderboards- Real-time analytics
Azure Options:
Cosmos DB (multi-model: document, key-value, graph, column)
Modern E-Commerce Application:Azure SQL Database:- Orders (need ACID guarantees)- Inventory (need transactions)- Payments (critical consistency)Cosmos DB:- Product catalog (flexible schema)- User sessions (high throughput)- Shopping cart (global distribution)Blob Storage:- Product images- User uploadsWhy both?- Use the right tool for each job- SQL for critical data- NoSQL for scale and flexibility- Storage for files
Traditional SQL databases like SQL Server or PostgreSQL are designed to run on a single machine. When you hit the limit of that machine (even if it’s 128 vCPUs), you have to Shard.
The Problem: You split your “Customers” into two databases: A-M in DB1, N-Z in DB2.
The Principal’s Headache: How do you do a JOIN between a customer in DB1 and an order in DB2? You can’t. Your application code now has to handle the routing and the distributed logic.
You can add hundreds of Read Replicas to handle users browsing your site. But every Write (an order) must go to the Single Primary. This is the bottleneck for every relational database in the cloud.
Azure SQL: Choose C (Consistency). If the primary fails, the database is unavailable for a few seconds until a new primary is elected.
Cosmos DB: Choose A (Availability). It can be configured for “Multi-Master” writes, allowing you to write to any region, even if other regions are down.
[!IMPORTANT]
Pro Insight: NewSQL
Azure SQL Hyperscale is Microsoft’s answer to the “Distributed SQL” problem. It separates Compute from Storage, allowing the storage to grow to 100 TB while providing multiple read-compute nodes, effectively blurring the line between traditional SQL and cloud-scale distribution.
DTU = Bundled measure of CPU, memory, I/OTiers:- Basic: 5 DTUs, 2 GB max- Standard: 10-3,000 DTUs, up to 1 TB- Premium: 125-4,000 DTUs, up to 4 TBExample: S3 (100 DTUs) = ~$150/month
When to use: Simple workloads, predictable performance
Virtual Cores (more control)
Choose: CPU cores + Memory + Storage separatelyTiers:- General Purpose: Balanced, SSD, 99.99% SLA- Business Critical: High performance, local SSD, 99.995% SLA- Hyperscale: Up to 100 TB, rapid scaleExample: 4 vCores + 32 GB + 500 GB = ~$700/month
When to use: Complex workloads, need specific resources
[!WARNING]
Gotcha: DTU vs vCore
DTU is cheaper for small databases but scales poorly. vCore is more expensive but allows you to save money with “Reserved Instances” (1-3 year commitment). Moving from DTU to vCore is easy, but going back can be tricky.
[!TIP]
Jargon Alert: ACID vs BASEACID (Azure SQL): Data is always correct immediately (Bank transactions).
BASE (Cosmos DB): Data is eventually correct (Social media feed). Cosmos DB creates copies around the world, so it might take a few milliseconds for a “Like” to show up in Japan if it happened in Brazil.
Architecture:- Compute and storage together- Local SSD with 4 replicas- Synchronous replication- 99.995% SLAFailover time: <10 secondsRPO: 0 (zero data loss)Bonus: Read-only replica included (free)
To a pro, indexes aren’t just “go-faster stripes”. They are physical data structures (B-Trees) that occupy disk space.
The Covered Index: The INCLUDE clause in the example above is critical. By including FirstName and LastName in the index, the query can get all the data it needs directly from the index leaf nodes without having to go back to the main table (the “Key Lookup”). This can turn a 500ms query into a 5ms query.
SARGable Queries: “Search ARGument-able”. If you use a function on a column in your WHERE clause (e.g., WHERE YEAR(JoinDate) = 2024), the database CANNOT use an index on JoinDate. It must scan every row.
Write Penalty: Every index you add makes your INSERT and UPDATE operations slower. In high-traffic write systems, “Over-indexing” is as dangerous as “Under-indexing”.
3. Read Scale-Out
Use read-only replica for reporting:
// Write connection (primary)string connStrWrite = "Server=tcp:myserver.database.windows.net;Database=mydb;ApplicationIntent=ReadWrite;";// Read connection (replica)string connStrRead = "Server=tcp:myserver.database.windows.net;Database=mydb;ApplicationIntent=ReadOnly;";// Routes to read-only replica (no load on primary)using (var conn = new SqlConnection(connStrRead)){ // Run reports without impacting production}
In a distributed system, you don’t just have CAP (Consistency, Availability, Partition Tolerance). You also have PACELC:
If there is a Partition, you choose between Availability or Consistency.
Else, you choose between Latency or Consistency.
Cosmos DB allows you to tune this exactly for your application.
Level
Consistency
Latency
RU Cost
Use Case
Strong
Highest
Highest
2.0x
FinTech / Transfers
Bounded
Guaranteed Lag
Medium
1.0x
Stock Tickers
Session
Read-Your-Own-Write
Low
1.0x
Social Media / Cart
Prefix
Ordering Guaranteed
Lowest
1.0x
Chat / Comments
Eventual
No Guarantees
Lowest
1.0x
View Analytics
[!WARNING]
Performance Gotcha: The Strong Consistency Tax
Strong consistency requires a majority of replicas to ACK a write before success. If you have replicas in US, Europe, and Asia, a “Strong” write will take hundreds of milliseconds (speed of light limit). NEVER use Strong consistency for global applications unless absolutely necessary.
Tiers:- Basic: 1-2 vCores, up to 1 TB- General Purpose: 2-64 vCores, up to 16 TB- Memory Optimized: 2-32 vCores, high memoryFeatures:✅ Automatic backups (7-35 days)✅ Automatic patching✅ Built-in HA❌ No read replicas (except cross-region)❌ No zone redundancyStatus: Being retired (use Flexible Server)
Advanced features, better performance
Features:✅ Zone-redundant HA (99.99% SLA)✅ Read replicas (same region)✅ Burstable VMs (B-series)✅ Stop/Start (save costs)✅ Custom maintenance window✅ Better performance (up to 3x faster)Price: Similar to Single ServerUse: All new deployments
Blocking: Check for long-running transactions that are locking rows. Use sys.dm_tran_locks.
CPU Throttling: If your compute utilization is at 100%, Azure will “throttle” your queries. The solution is usually vertical scaling (more vCores) or fixing a missing index.
Connection pooling: Is your app opening a new connection for every request? This is expensive. Use a connection pool (like HikariCP or Entity Framework pooling).
Cloud databases sometimes reset connections during maintenance.
The Fix: Implement Retry Logic in your application code. Most Azure SDKs have this built-in, but you must enable it. Use “Exponential Backoff” to avoid overwhelming the database after a reset.
[!TIP]
Pro Tool: Query Store
In Azure SQL, enable Query Store. It’s a “black box flight recorder” for your database that tracks every query’s performance history. It can even automatically roll back a bad execution plan!
