Azure SQL Database

Azure SQL Database: Complete Guide for DP-900

Azure SQL Database is one of the most critical topics on the DP-900 (Microsoft Azure Data Fundamentals) exam. Understanding what it is, how it works, and why it matters is essential for both the certification and real-world cloud data management.

Why Is Azure SQL Database Important?
Azure SQL Database is Microsoft's flagship fully managed relational database service in the cloud. It is important because:

- It enables organizations to move their relational workloads to the cloud without managing underlying infrastructure.
- It provides built-in high availability, automated backups, and intelligent performance tuning.
- It supports mission-critical applications with enterprise-grade security, compliance, and scalability.
- It reduces operational overhead by eliminating the need for patching, updating, and maintaining database servers manually.
- It is one of the most widely adopted Platform-as-a-Service (PaaS) database offerings globally.

What Is Azure SQL Database?
Azure SQL Database is a fully managed Platform-as-a-Service (PaaS) relational database engine hosted in Azure. It is based on the latest stable version of the Microsoft SQL Server database engine. Key characteristics include:

- PaaS Offering: Microsoft manages the underlying infrastructure, including hardware, OS patching, backups, and high availability. You focus on the database, not the server.
- SQL Server Compatibility: It uses the same T-SQL (Transact-SQL) language as SQL Server, making it familiar to SQL Server developers and administrators.
- Deployment Options: Azure SQL Database can be deployed as a Single Database (an isolated database with its own resources) or within an Elastic Pool (a collection of databases that share a common pool of resources for cost optimization).
- Serverless Compute Tier: Azure SQL Database offers a serverless tier that automatically scales compute based on workload demand and pauses during inactive periods to save costs.
- Hyperscale Service Tier: For extremely large databases (up to 100 TB), the Hyperscale tier provides rapid scale-out capabilities and near-instant backups.

How Does Azure SQL Database Work?

1. Provisioning and Configuration
You create an Azure SQL Database through the Azure portal, Azure CLI, PowerShell, or ARM/Bicep templates. During creation, you specify:
- A logical SQL server (a logical construct that acts as a central administrative point for databases — it is NOT a physical server you manage).
- The database name, pricing tier, and compute/storage configuration.
- Firewall rules and networking settings to control access.

2. Purchasing Models
Azure SQL Database offers two purchasing models:
- DTU-based (Database Transaction Unit): A bundled measure of compute, storage, and I/O resources. Simpler to configure. Comes in Basic, Standard, and Premium tiers.
- vCore-based: Allows you to independently choose compute and storage resources. Offers General Purpose, Business Critical, and Hyperscale service tiers. Supports Azure Hybrid Benefit (use existing SQL Server licenses).

3. High Availability
Azure SQL Database provides built-in high availability with a guaranteed SLA of up to 99.995% (Business Critical tier). It automatically replicates data and handles failovers without requiring user configuration.

4. Security Features
- Transparent Data Encryption (TDE): Encrypts data at rest by default.
- Advanced Threat Protection: Detects anomalous database activities indicating potential threats.
- Azure Active Directory (Microsoft Entra ID) Authentication: Supports identity-based access in addition to SQL authentication.
- Dynamic Data Masking: Limits sensitive data exposure to non-privileged users.
- Auditing: Tracks database events and writes them to an audit log.
- Firewall Rules and Virtual Network Service Endpoints: Control network-level access to the database.

5. Backup and Recovery
- Automated backups are performed continuously (full backups weekly, differential daily, transaction log every 5-10 minutes).
- Point-in-time restore (PITR) allows you to restore a database to any point within the retention period (7-35 days).
- Long-term retention (LTR) policies allow backups to be stored for up to 10 years.
- Geo-replication enables readable secondary databases in different Azure regions for disaster recovery.
- Auto-failover groups provide automatic failover of a group of databases to a secondary region.

6. Monitoring and Performance Tuning
- Azure Monitor and SQL Analytics provide insights into database performance.
- Intelligent Insights uses built-in AI to detect performance issues and recommend solutions.
- Automatic Tuning can automatically create indexes, drop unused indexes, and force good query plans.
- Query Performance Insight helps identify resource-consuming queries.

7. Elastic Pools
Elastic pools are designed for SaaS applications where multiple databases have unpredictable usage patterns. Databases in a pool share a set amount of resources (eDTUs or vCores), which is more cost-effective than provisioning resources for each database individually to handle peak loads.

Azure SQL Database vs. Other Azure SQL Options

It is important for the exam to distinguish Azure SQL Database from related services:

- Azure SQL Database: Fully managed PaaS, single database or elastic pool. Best for modern cloud applications. No OS-level access.
- Azure SQL Managed Instance: Also PaaS, but provides near 100% compatibility with on-premises SQL Server. Supports features like SQL Server Agent, cross-database queries, CLR, and linked servers. Best for lift-and-shift migrations.
- SQL Server on Azure Virtual Machines: Infrastructure-as-a-Service (IaaS). Full control over the SQL Server instance and the OS. You manage patching, backups, and HA yourself. Best when you need full OS-level or SQL Server-level control.

Key Concepts to Remember
- Azure SQL Database is PaaS, not IaaS.
- The logical server is an administrative boundary, not a VM you manage.
- Elastic pools help manage costs for multiple databases with variable usage.
- Geo-replication and auto-failover groups are disaster recovery features.
- Serverless compute tier auto-scales and auto-pauses to reduce costs.
- Hyperscale supports very large databases up to 100 TB.
- Built-in automatic backups and point-in-time restore are included.
- TDE is enabled by default for encryption at rest.

Exam Tips: Answering Questions on Azure SQL Database

1. Know the Service Model: If a question asks about a fully managed relational database with no infrastructure management, the answer is Azure SQL Database (PaaS). If the question mentions needing OS-level access or installing custom software, the answer is SQL Server on Azure VMs (IaaS). If it mentions near-complete SQL Server compatibility with features like SQL Agent or cross-database queries, think Azure SQL Managed Instance.

2. Understand Elastic Pools: Questions about cost optimization for multiple databases with unpredictable or varying workloads typically point to Elastic Pools.

3. Differentiate Purchasing Models: DTU-based is simpler and bundled; vCore-based gives more granular control and allows Azure Hybrid Benefit. Know which model offers which benefit.

4. Security Questions: If asked about encryption at rest, the answer is TDE (enabled by default). For identity-based authentication, think Microsoft Entra ID (Azure AD). For limiting data visibility, think Dynamic Data Masking.

5. Backup and DR Questions: Point-in-time restore for accidental data loss within the retention period. Geo-replication or auto-failover groups for regional disaster recovery. Long-term retention for compliance requirements spanning years.

6. Read the Question Carefully: Many DP-900 questions test whether you understand the difference between PaaS and IaaS. Azure SQL Database = PaaS. SQL Server on Azure VM = IaaS. Azure SQL Managed Instance = PaaS (but with broader SQL Server feature compatibility).

7. Serverless vs. Provisioned: If a question mentions a database that is used intermittently or has unpredictable usage and the goal is cost savings, the serverless compute tier is likely the answer.

8. Watch for Distractors: Options mentioning Azure Cosmos DB, Azure Database for MySQL, or Azure Database for PostgreSQL are relational but non-SQL-Server services. Azure SQL Database is specifically the SQL Server-based PaaS offering.

9. Logical Server Concept: Remember that a logical server in Azure SQL Database is NOT the same as a physical server. Multiple databases can share the same logical server, which provides a unified endpoint for firewall rules, auditing policies, and login management.

10. Practice Scenario-Based Thinking: The DP-900 often presents real-world scenarios. Ask yourself: Does the scenario need full SQL Server compatibility? (Managed Instance) Does it need just a cloud database with minimal management? (SQL Database) Does it need full control? (SQL on VM) This mental framework will help you answer quickly and accurately.

By thoroughly understanding these concepts, you will be well-prepared to answer any DP-900 exam question related to Azure SQL Database with confidence.