Azure SQL Database Concepts

Introduction to Azure SQL Database

Azure SQL Database is a fully managed Platform as a Service (PaaS) database engine that handles most database management functions such as upgrading, patching, backups, and monitoring without user involvement.

It's built on the SQL Server engine and provides a robust, scalable, and highly available solution for modern application development.

Key Components and Concepts

1. Logical Servers

A logical server is a management construct that acts as a central administrative point for a collection of databases. It contains logins, firewall rules, auditing policies, and threat detection policies. A logical server is not tied to a specific Azure region, and its contained databases are distributed across the available regions for high availability.

2. Databases

You can create different types of databases within your logical server:

  • Single Database: A self-contained database with its own set of resources, managed independently.
  • Elastic Pool: A collection of single databases with shared resources, designed to manage and scale multiple databases with varying and unpredictable usage demands.
  • Hyperscale: A database service tier that provides very large database sizes and rapid scaling capabilities.

3. Service Tiers and Compute Models

Azure SQL Database offers several service tiers to meet different performance, scalability, and cost requirements. The primary compute models are:

  • DTU (Database Transaction Unit): A bundled measure of compute, memory, and I/O resources. This is a simpler model for smaller workloads.
  • vCore (Virtual Core): Provides more granular control over resources. You choose the number of vCores, memory, and I/O. This model is recommended for most production workloads.

Within the vCore model, you have compute tiers such as:

  • General Purpose: Balanced compute and storage for most common workloads.
  • Business Critical: High performance with low-latency storage and high availability.
  • Hyperscale: For very large databases and rapid scaling needs.

4. Elasticity and Scalability

Azure SQL Database offers extensive elasticity. You can scale compute and storage resources up or down with minimal downtime. Elastic pools further enhance this by allowing shared resource allocation across multiple databases, optimizing costs for variable workloads.

5. High Availability and Disaster Recovery

Azure SQL Database is designed for high availability. It includes:

  • Automatic backups: Regular backups are taken automatically and stored in Azure storage.
  • Point-in-time restore: You can restore your database to any point in time within your defined retention period.
  • Geo-restore: Restore a database from a geo-replicated backup to any region.
  • Active Geo-Replication: Maintain readable secondary databases in different regions for disaster recovery or read-scale scenarios.
  • Auto-failover groups: Manage replication and failover across different Azure regions.

6. Security Features

Security is a core tenet of Azure SQL Database:

  • Azure Active Directory Authentication: Centralized identity management.
  • Always Encrypted: Protects sensitive data at rest and in transit.
  • Transparent Data Encryption (TDE): Encrypts data at rest automatically.
  • Firewall rules: Control network access to your database.
  • Threat Detection: Identifies and alerts on anomalous activities.

7. Connectivity

Azure SQL Database can be accessed using various tools and applications, including:

  • SQL Server Management Studio (SSMS)
  • Azure Data Studio
  • Programmatic access via ADO.NET, ODBC, JDBC, PHP, and other database drivers.

Azure SQL Database vs. SQL Server on Azure VMs

Azure SQL Database is a managed PaaS offering, abstracting away infrastructure management. SQL Server on Azure VMs provides full control over the operating system and SQL Server instance, acting as an Infrastructure as a Service (IaaS) solution.

Choose Azure SQL Database for:

  • Modern cloud applications
  • Managed PaaS benefits
  • Automatic updates and patching
  • High availability and scalability with less administrative overhead

Choose SQL Server on Azure VMs for:

  • Migrating existing on-premises applications with minimal changes
  • Specific OS or SQL Server version requirements
  • Full control over the environment

Next Steps

To learn more about Azure SQL Database, explore the following resources: