Azure SQL Database is a fully managed relational database service built on SQL Server. Microsoft handles patching, backups, high availability, and scaling — you focus on schema design, queries, and application integration.

Service Tiers

Tier Description Use Case
Serverless Auto-pause, pay per second Dev/test, intermittent workloads
DTU-based Bundled compute + storage Simple, predictable pricing
vCore-based Independent compute + storage Production, fine-grained control
Hyperscale Up to 100 TB, fast scale Large databases, rapid growth

Create Azure SQL Database 

  # Create logical server
az sql server create \
  --name sql-webapp-prod \
  --resource-group rg-webapp-prod \
  --location eastus \
  --admin-user sqladmin \
  --admin-password 'ComplexP@ssw0rd!'

# Configure firewall (allow Azure services)
az sql server firewall-rule create \
  --resource-group rg-webapp-prod \
  --server sql-webapp-prod \
  --name AllowAzure \
  --start-ip-address 0.0.0.0 \
  --end-ip-address 0.0.0.0

# Create database
az sql db create \
  --resource-group rg-webapp-prod \
  --server sql-webapp-prod \
  --name db-webapp \
  --service-objective S1 \
  --backup-storage-redundancy Zone \
  --zone-redundant true

Connect to Azure SQL 

  # Connection string format
Server=tcp:sql-webapp-prod.database.windows.net,1433;
Initial Catalog=db-webapp;
User ID=sqladmin;
Password=<password>;
Encrypt=True;TrustServerCertificate=False;

# Connect via sqlcmd
sqlcmd -S sql-webapp-prod.database.windows.net -d db-webapp -U sqladmin -P '<password>' -G

# From App Service (connection string in app settings)
az webapp config connection-string set \
  --name my-webapp-prod \
  --resource-group rg-webapp-prod \
  --settings DefaultConnection="Server=tcp:sql-webapp-prod.database.windows.net..." \
  --connection-string-type SQLAzure

High Availability Options

Feature RTO Description
Zone redundancy Seconds Standby in different AZ, automatic failover
Geo-replication Seconds–minutes Readable secondary in another region
Failover group ~30 seconds Automatic DNS failover between regions
Auto-failover groups Managed Combines geo-replication + failover group 
  # Create geo-replica
az sql db replica create \
  --resource-group rg-webapp-prod \
  --server sql-webapp-prod \
  --name db-webapp \
  --partner-server sql-webapp-dr \
  --partner-resource-group rg-webapp-dr

# Create failover group
az sql failover-group create \
  --name fg-webapp \
  --resource-group rg-webapp-prod \
  --server sql-webapp-prod \
  --partner-server sql-webapp-dr \
  --partner-resource-group rg-webapp-dr \
  --failover-policy Automatic \
  --grace-period 3600 \
  --add-db db-webapp

Application connects to failover group listener: fg-webapp.database.windows.net — DNS automatically routes to primary.

Backups and Point-in-Time Restore

Azure SQL automatically backs up databases:

Backup Type Frequency Retention
Full Weekly Up to 35 days (configurable)
Differential Every 12-24 hours Same as full
Transaction log Every 5-10 minutes Same as full 
  # Restore to point in time
az sql db restore \
  --resource-group rg-webapp-prod \
  --server sql-webapp-prod \
  --name db-webapp-restored \
  --source-database db-webapp \
  --time "2024-06-13T10:30:00Z"

# Long-term retention (weekly/monthly/yearly)
az sql db ltr-policy set \
  --resource-group rg-webapp-prod \
  --server sql-webapp-prod \
  --database db-webapp \
  --weekly-retention P4W \
  --monthly-retention P12M \
  --yearly-retention P5Y \
  --week-of-year 1

Security 

  # Enable Azure AD authentication
az sql server ad-admin create \
  --resource-group rg-webapp-prod \
  --server sql-webapp-prod \
  --display-name "SQL Admins" \
  --object-id $(az ad group show --group "SQL Admins" --query id -o tsv)

# Enable Transparent Data Encryption (enabled by default)
az sql db tde set \
  --resource-group rg-webapp-prod \
  --server sql-webapp-prod \
  --database db-webapp \
  --status Enabled

# Private endpoint (no public access)
az network private-endpoint create \
  --name pe-sql-webapp \
  --resource-group rg-webapp-prod \
  --vnet-name vnet-webapp \
  --subnet subnet-private \
  --private-connection-resource-id $(az sql server show --name sql-webapp-prod -g rg-webapp-prod --query id -o tsv) \
  --group-id sqlServer \
  --connection-name sql-connection

Disable public network access after configuring private endpoint:

  az sql server update \
  --name sql-webapp-prod \
  --resource-group rg-webapp-prod \
  --public-network-access Disabled

Performance Monitoring 

  # Enable Query Store (on by default in Azure SQL)
az sql db update \
  --resource-group rg-webapp-prod \
  --server sql-webapp-prod \
  --name db-webapp \
  --query-store-enabled true

# View DTU/vCore usage
az monitor metrics list \
  --resource $(az sql db show --name db-webapp --server sql-webapp-prod -g rg-webapp-prod --query id -o tsv) \
  --metric "dtu_consumption_percent" \
  --interval PT1H

Key metrics in Azure Monitor:

  • dtu_consumption_percent or cpu_percent — alert > 80%
  • storage_percent — alert > 85%
  • connection_failed — alert on spikes
  • deadlock — investigate query patterns

Elastic Pools

Share resources across multiple databases for cost efficiency:

  az sql elastic-pool create \
  --resource-group rg-webapp-prod \
  --server sql-webapp-prod \
  --name pool-dev-databases \
  --edition Standard \
  --capacity 100

az sql db create \
  --resource-group rg-webapp-prod \
  --server sql-webapp-prod \
  --name db-app1 \
  --elastic-pool pool-dev-databases

Ideal for SaaS with many small tenant databases.

Real-World Scenario: E-Commerce Database

Component Configuration
Tier vCore, General Purpose, 4 vCores
HA Zone redundant, auto-failover group to West US
Backups 35-day PITR + 5-year LTR
Security Private endpoint, Azure AD auth, TDE
Performance Query Store enabled, auto-tuning on
Connection App Service via private endpoint + Managed Identity

Azure SQL vs SQL on VM vs Cosmos DB

Criteria Azure SQL SQL on VM Cosmos DB
Management Fully managed Self-managed Fully managed
SQL compatibility Full T-SQL Full SQL Server SQL API (limited)
Scaling Vertical + read replicas Manual Automatic, global
Cost DTU/vCore pricing VM + license RU-based
Best for Most relational workloads Full SQL Server features Global NoSQL

Common Mistakes

  1. Public endpoint in production — use private endpoints
  2. SQL authentication only — enable Azure AD authentication
  3. No geo-replication for production — configure failover groups
  4. Under-provisioned tier — monitor DTU/CPU and scale up
  5. Firewall rule 0.0.0.0-255.255.255.255 — scope to specific IPs or VNet
  6. No connection pooling — use App Service connection pooling or Elastic Pool

Troubleshooting

Issue Diagnosis Fix
Connection timeout Firewall, private endpoint Add client IP to firewall or use VNet integration
DTU/consumption > 90% Missing indexes, slow queries Query Store → top queries; add indexes
Failover not working Failover group misconfiguration Verify partner server, grace period, DNS
Login failed for user Wrong auth method Use Azure AD token or verify SQL credentials
Storage full Database growth Enable auto-grow; archive old data

Best Practices

  • Use private endpoints and disable public access for production
  • Enable Azure AD authentication alongside SQL auth
  • Configure auto-failover groups for cross-region DR
  • Monitor Query Store and enable automatic tuning
  • Set backup retention based on compliance requirements (35 days minimum for production)
  • Use Elastic Pools for multi-tenant SaaS with small databases
  • Apply Azure Hybrid Benefit for existing SQL Server license savings
  • Test restore procedures quarterly

Next: Azure Storage.