Azure SQL Database
Azure SQL Database is a fully managed relational database built on SQL Server — automated backups, patching, high availability, and scaling with no DBA required.
Simple Explanation
Azure SQL is SQL Server in the cloud where Azure handles all the maintenance — backups, updates, replication, and failover. You just connect and write SQL queries.
When to Use Azure SQL Database
- New web applications needing a relational database.
- Migrating from SQL Server when you don't need absolute compatibility (SQL MI is better for that).
- Teams without DBA resources who want automated maintenance.
- Workloads with unpredictable scale that benefit from serverless or elastic scaling.
Deployment Options
| Option | Description | Best For |
|---|---|---|
| Single Database | One database with dedicated resources | Standard workloads, single-tenant apps |
| Elastic Pool | Shared resources across multiple databases | SaaS multi-tenant (many small DBs) |
| Serverless | Auto-scales and pauses when idle | Dev/test, intermittent workloads |
Purchasing Models
| Model | Unit | Control | Best For |
|---|---|---|---|
| DTU (Database Transaction Unit) | Blended CPU/memory/IO bundle | Simple, less control | Smaller apps, predictable load |
| vCore | Individual CPU cores + RAM separately | Fine-grained, Azure Hybrid Benefit eligible | Production, migration from on-prem licensing |
If you're migrating from an on-prem SQL Server with existing licenses, use vCore + Azure Hybrid Benefit to save up to 55%. For new workloads without existing licenses, DTU is simpler to reason about.
Visual Representation
Commands
# Create a logical SQL server az sql server create \ --name myapp-sql-server \ --resource-group rg-database \ --location eastus \ --admin-user sqladmin \ --admin-password "P@ssw0rd2024!" # Allow Azure services to access the server az sql server firewall-rule create \ --resource-group rg-database \ --server myapp-sql-server \ --name AllowAzureServices \ --start-ip-address 0.0.0.0 \ --end-ip-address 0.0.0.0 # Add your client IP az sql server firewall-rule create \ --resource-group rg-database \ --server myapp-sql-server \ --name MyClientIP \ --start-ip-address 203.0.113.10 \ --end-ip-address 203.0.113.10 # Create a database (Standard S2 DTU) az sql db create \ --resource-group rg-database \ --server myapp-sql-server \ --name app-db \ --service-objective S2 # Create vCore database (General Purpose, 2 vCores) az sql db create \ --resource-group rg-database \ --server myapp-sql-server \ --name app-db-vcore \ --edition GeneralPurpose \ --family Gen5 \ --capacity 2 # Connection string format (SQL auth) # Server=myapp-sql-server.database.windows.net; # Database=app-db;User Id=sqladmin;Password=...;Encrypt=True; # Create Elastic Pool az sql elastic-pool create \ --resource-group rg-database \ --server myapp-sql-server \ --name ep-saas-tenants \ --edition Standard \ --dtu 100 \ --db-max-dtu 50
Hands-on
- Create a SQL Server (logical) and a database on S1 tier.
- Add a firewall rule for your IP and connect using Azure Data Studio or SSMS.
- Create a table and insert sample rows.
- Demonstrate point-in-time restore in Portal (try restoring to 5 minutes ago).
- Enable Microsoft Defender for SQL and review any alerts.
Debugging Scenario
Issue: Application cannot connect to Azure SQL Database.
- Check firewall rules: the client IP must be whitelisted on the logical server.
- Verify TLS: Azure SQL requires TLS 1.2+. Older connection strings or drivers may fail.
- Check the connection string format — use
Encrypt=True;TrustServerCertificate=False. - If connecting from an Azure resource, use "Allow Azure services" firewall rule or private endpoint.
- Check credentials: admin password policies enforce complexity requirements.
Interview Questions
Beginner
A container for one or more Azure SQL databases. It defines the server hostname, admin credentials, and firewall rules. It's not a physical server — it's a management namespace.
Azure automatically performs full backups weekly, differential backups every 12-24 hours, and transaction log backups every 5-10 minutes. Retention: 7-35 days configurable.
Scenario-based
Use an Elastic Pool. Pool resources are shared across all databases. Each tenant gets a database, they collectively share the DTU pool, and Azure distributes resources based on actual usage. Much cheaper than 500 individual S1 databases.
Use point-in-time restore: create a new database restored to 1:59 PM, extract the deleted rows, and insert them into production.
Summary
Azure SQL Database offers fully managed relational SQL with automatic backups, built-in HA, and flexible scaling. Use Elastic Pools for SaaS multi-tenancy, vCore for migration with existing licenses, and serverless for cost-optimised dev environments.