Skip to main content
Microsoft Azure
CHAPTER 12

Azure SQL Database and Cosmos DB

Updated: May 15, 2026
20 min read

# CHAPTER 12

Azure SQL Database and Cosmos DB

1. Introduction

You *could* install a SQL database directly onto an Azure Virtual Machine. However, this means you are responsible for updating the Windows Server OS, patching SQL security flaws, configuring Always-On Availability Groups, and writing scripts to perform nightly backups. If you make a mistake, you lose your company's data. To eliminate this massive operational burden, Azure offers Azure SQL Database (Relational) and Azure Cosmos DB (NoSQL). In this chapter, we will deploy production-grade databases that maintain themselves.

2. Learning Objectives

By the end of this chapter, you will be able to:
  • Differentiate between IaaS (Self-Managed) and PaaS (Managed Database Services).
  • Define Azure SQL Database and its relational capabilities.
  • Define Azure Cosmos DB and its NoSQL, multi-model capabilities.
  • Deploy an Azure SQL Database.
  • Secure the database connection using Azure Firewall rules.

3. Beginner-Friendly Explanation

Imagine maintaining a classic car.
  • Self-Managed (Virtual Machine): You own the car, but you also have to change the oil, rebuild the engine when it breaks, and lock the garage yourself. It requires intense mechanical knowledge.
  • Managed Service (Azure SQL Database): You lease a car that comes with an invisible mechanic. The mechanic automatically changes the oil while you are driving, instantly swaps the engine if it fails, and guarantees the car will never be stolen. You just get in and drive.

4. Azure SQL Database (Relational)

Azure SQL Database is a fully managed Platform as a Service (PaaS) Database Engine. It runs the exact same Microsoft SQL Server engine used by millions of enterprises.
  • Automated Backups: Azure automatically takes full, differential, and transaction log backups (Point-in-Time Restore allows you to rewind the database to any specific second in the past 35 days!).
  • High Availability: Built-in fault tolerance. If the underlying hardware fails, Azure instantly moves your database to a healthy node.

5. Azure Cosmos DB (NoSQL)

Relational databases are rigid. If you have a massive, unpredictable mobile app (like a global multiplayer game), you need flexibility and infinite horizontal scalability. Azure Cosmos DB is Microsoft's globally distributed, multi-model NoSQL database.
  • Schema-less: You don't create rigid tables with columns. You just throw flexible JSON documents into it.
  • Turnkey Global Distribution: With one click, your database replicates perfectly across 5 different continents, ensuring users in Tokyo and users in New York both experience single-digit millisecond latency.

6. Security and Connectivity

Databases are the crown jewels of your architecture. They should never be open to the public internet. By default, an Azure SQL Database blocks ALL traffic. You must configure its Server Firewall to allow your specific IP address (so you can manage it from your laptop) and check a box to "Allow Azure services and resources to access this server" (so your Azure App Service web app can talk to it). *For true enterprise security, you disable the Public Endpoint entirely and use Azure Private Link to connect the database directly to your VNet.*

7. Mini Project: Create a Managed SQL Database

Let's provision a database that we never have to maintain.

Step-by-Step Tutorial:

  1. 1. In the Azure Portal, search for SQL databases.
  1. 2. Click + Create.
  1. 3. Resource group: rg-database-demo.
  1. 4. Database name: my-production-db.
  1. 5. Server: Click Create new.
  • Server name: my-unique-sql-server-1234
  • Location: East US
  • Authentication method: Use SQL authentication
  • Server admin login: sqladmin
  • Password: Create a highly secure password. Click OK.
  1. 6. Want to use SQL elastic pool? No.
  1. 7. Compute + storage: Click Configure database. Select Basic (For less demanding workloads) to save money. Apply.
  1. 8. Click Next: Networking.
  1. 9. Connectivity method: Public endpoint.
  1. 10. Firewall rules:
  • Allow Azure services: Yes.
  • Add current client IP address: Yes (This lets you connect from your current laptop).
  1. 11. Click Review + create, then Create.
*(Note: Provisioning takes a few minutes).*
  1. 12. Once created, you can download a free tool like *SQL Server Management Studio (SSMS)* or *Azure Data Studio* to connect to your shiny new managed database!

8. Real-World Scenarios

An e-commerce company relies on Azure SQL Database for its shopping cart system. A junior developer accidentally runs a SQL command that deletes the entire "Users" table at 3:00 PM. Panic ensues. However, because the Cloud Engineer utilizes Point-in-Time Restore (PITR), they simply click a button in the portal to restore a new copy of the database to its exact state at 2:59 PM. The crisis is averted in minutes.

9. Best Practices

  • Serverless SQL: Azure offers a "Serverless" compute tier for Azure SQL. If your database is only used by accountants from 9 AM to 5 PM, Serverless SQL will automatically PAUSE the database at night. You stop paying for compute completely, and Azure instantly wakes it up the next morning when someone connects.

10. Cost Optimization Tips

  • Cosmos DB Request Units (RUs): Cosmos DB bills you based on "RUs"—the amount of CPU/Memory required to perform a read or write. If you write bad queries that scan the entire database instead of using an Index, you will consume massive RUs and receive a massive bill. Always optimize NoSQL queries!

11. CLI Examples

To create a SQL Server and Database via the terminal:
bash
123 
az sql server create --name myserver123 --resource-group rg-db --admin-user sqladmin --admin-password "MySecretPassword123!"

az sql db create --resource-group rg-db --server myserver123 --name mydb --edition Basic

12. Exercises

  1. 1. What is the fundamental difference between Azure SQL Database (Relational) and Azure Cosmos DB (NoSQL)?
  1. 2. Why does Azure SQL Database automatically block your laptop's IP address by default?

13. FAQs

Q: Can I use MySQL or PostgreSQL instead of Microsoft SQL Server? A: Yes! Azure provides fully managed PaaS versions of open-source databases: Azure Database for MySQL and Azure Database for PostgreSQL. They offer the exact same automated backups and HA features.

14. Interview Questions

  • Q: Contrast the scaling paradigms of a relational database (Azure SQL) and a globally distributed NoSQL database (Cosmos DB). Why is Cosmos DB inherently more suited for massive, globally dispersed read-heavy workloads?
  • Q: A developer complains they cannot connect to a newly provisioned Azure SQL Database from their local development environment via SQL Server Management Studio. Detail the security configurations (Firewall rules and Public/Private endpoints) you must review to grant them secure access.

15. Summary

In Chapter 12, we liberated ourselves from Database Administration. We contrasted the operational agony of self-managed IaaS databases with the elegant automation of Azure SQL Database. We explored the schema-less, global capabilities of Cosmos DB, established resilience via Automated Backups, and solidified our security posture by strictly configuring Server Firewalls, ensuring our critical data remains impenetrable to unauthorized access.

16. Next Chapter Recommendation

Databases handle the storage, and App Service handles the frontend. But what if we want to run small, background processing scripts without paying for an entire server 24/7? Proceed to Chapter 13: Azure Functions Serverless Computing.

Finish this Chapter

Save your progress on your learning path and prepare for coding interview challenges.

Previous Chapter Docker and Azure Container Services
Next Chapter Azure Functions Serverless Computing

Discussion

Join the discussion

Log in or create a free account to participate.

Sort: ·

Explore More

📖 Related Tutorials 5

❓ Related Quizzes 6