Azure SQL Database

Azure SQL Database is Microsoft’s flagship relational database service in the cloud, designed to handle various data types including relational, JSON, spatial, and XML data. It is a fully managed service that guarantees high availability and data protection.

Key Features

• Fully Managed Service: Azure takes care of routine database management tasks, including patching, backups, replication, failure detection, and maintenance, ensuring no data loss and high availability.
• Compatibility: Azure SQL Database is designed for maximum compatibility with existing SQL Server databases.

Deployment Options

There are three primary ways to implement Azure SQL Database:

1. Managed Instance:
   • Targeted at customers with existing on-premises SQL Server instances.
   • Enables migration to Azure with minimal changes and maximum compatibility with existing applications.
2. Single Database:
   • A standalone database that uses its own resources managed through a logical server.
3. Elastic Pool:
   • A collection of databases that share a pool of resources (DTUs or vCores) managed through a logical server. This is a cost-effective solution for managing multiple databases.
4. SQL Server on Azure Virtual Machines (IaaS):
   • Allows you to run SQL Server on a virtual machine, where you manage the SQL Server instance.

Purchasing Models

Azure SQL Database offers two purchasing models:

1. vCore Purchasing Model:
   • Allows independent scaling of compute and storage resources.
   • Provides flexibility, control, and transparency.
   • Enables use of Azure Hybrid Benefit for SQL Server to optimize costs.
2. DTU Model:
   • A bundled measure that combines compute, storage, and I/O resources into Database Transaction Units (DTUs) for single databases and elastic Database Transaction Units (eDTUs) for elastic pools.
   • Simplifies resource management with pre-configured options.

Service Tiers

Azure SQL Database has different service tiers to meet various workload requirements:

1. General Purpose / Standard Model:
   • Based on a separation of compute and storage, ensuring high availability and reliability.
   • Utilizes Azure Premium Storage for transparent data protection.
2. Business Critical / Premium Service Tier:
   • Designed for high performance, with low-latency SSD storage.
   • Utilizes a clustered architecture for high availability.
3. Hyperscale Service Tier:
   • The newest tier in the vCore-based model.
   • Supports highly scalable storage and compute resources beyond the limits of other service tiers.

SQL Database Logical Server

• Acts as a central administrative point for managing multiple databases, elastic pools, and configurations like firewall rules and threat detection policies.
• Must exist before creating any Azure SQL Database.
• Provides no instance-level access but serves as a parent resource for other database-related components.

Elastic Pools

• Cost-effective for managing multiple databases that share resources at a fixed price.
• Resource allocation can be configured based on either the DTU or vCore purchasing model.
• Defines maximum resource utilization and storage across all databases within the pool.

Creating an Azure SQL Database via Azure Portal

Step-by-Step Process

1. Create a Resource:
   • Click on “Create a Resource” and search for “SQL Database.” Click “Create.”
2. Fill Required Details:
   • Enter necessary information such as database name and subscription.
3. Select/Create a Server:
   • Choose an existing server or create a new one.
4. Select Pricing Tier:
   • Click on “Compute + Storage” to select the desired pricing tier.
5. Review and Create:
   • Click “Review + Create” to finalize the configuration.
6. Access the Database:
   • After creation, click on “Go to Resources” to configure additional settings.