How to Be an Azure SQL DBA: Skills, Practices, and a Hands-On Workshop

If you are already a SQL Server DBA, Azure SQL feels familiar and completely different at the same time. You still care about concurrency, indexing, locking, and query plans, but now you also own things like service tiers, vCores, firewall rules, Azure Monitor alerts, and cost optimization for a platform you do not patch or reboot yourself.

This guide walks through what it actually means to be an Azure SQL DBA today, how the role differs from a traditional on-premises DBA, and includes a detailed step-by-step workshop you can follow to build a real-world environment from scratch.

Throughout this guide, “Azure SQL DBA” means the person responsible for Azure SQL Database (single and elastic pool), Azure SQL Managed Instance, and often SQL Server on Azure VMs as part of the same portfolio.

The Azure SQL Family

Before doing anything in the portal, you need a clear mental model of the three Azure SQL offerings:

What an Azure SQL DBA Actually Does

Core Skills Matrix

Creating Your First Azure SQL Database

Every Azure SQL DBA should be able to provision and connect to a new database with best-practice defaults from memory. The pattern you establish with your first database is the one that tends to repeat across your environment.

Portal Provisioning Flow

1. Go to Azure SQL in the portal → SQL Database → Create
2. Select subscription, resource group, database name, and logical server
3. For compute: choose vCore-based General Purpose for most workloads. Start at 2–4 vCores and scale based on metrics — do not guess high
4. For networking: use a Private endpoint for production. Avoid “Allow Azure services and resources to access this server” for sensitive environments
5. For security: set Microsoft Entra admin at server creation. Use contained database users for applications. Enable Defender for SQL and auditing to Log Analytics

Security: Make Secure by Default Your Habit

Identity and Logins

Use Microsoft Entra ID for admin and human access — not plain SQL logins. Use role-based access control at the server and database level. Map applications to contained database users via managed identities or Entra groups.

Network Isolation

• Use private endpoints for production — traffic stays in your virtual network
• Use NSGs and controlled peering to restrict where traffic originates
• Avoid wide-open firewall rules — especially 0.0.0.0 – 255.255.255.255
• Public endpoint only is acceptable for dev/test, not for sensitive production workloads

Data Protection

• Transparent Data Encryption (TDE) is on by default — keep it that way
• Consider customer-managed keys in Key Vault for strict compliance scenarios
• Use Always Encrypted for highly sensitive columns where the application model supports it
• Enable auditing (to Log Analytics or storage) and Defender for SQL per your organization’s requirements

Performance and Monitoring in Azure SQL

You cannot RDP into the box and stare at PerfMon anymore. You use Azure Monitor plus in-engine telemetry — Query Store, DMVs, and Query Performance Insight.

Key Azure Monitor Metrics to Watch

• cpu_percent — compute utilization (vCore model)
• data_io_percent and log_write_percent — I/O pressure
• storage_percent — storage headroom
• connection_failed — connection errors
• dtu_consumption_percent — if still on DTU model (migrate to vCore)

Query Store Workflow

Confirm Query Store is enabled in Read-Write mode for all user databases. Use Query Performance Insight in the portal for quick analysis, then drop to T-SQL for deeper investigation:

-- Top 20 queries by total CPU consumption from Query Store
SELECT TOP 20
    qsq.query_id,
    SUM(rs.avg_duration        * rs.count_executions) AS total_duration_ms,
    SUM(rs.avg_cpu_time        * rs.count_executions) AS total_cpu_ms,
    SUM(rs.avg_logical_io_reads * rs.count_executions) AS total_reads,
    OBJECT_NAME(qt.object_id)                          AS object_name,
    qt.query_sql_text
FROM sys.query_store_query       qsq
JOIN sys.query_store_plan        qsp ON qsq.query_id     = qsp.query_id
JOIN sys.query_store_runtime_stats rs  ON qsp.plan_id      = rs.plan_id
JOIN sys.query_store_query_text  qt  ON qsq.query_text_id = qt.query_text_id
GROUP BY qsq.query_id, qt.query_sql_text, qt.object_id
ORDER BY total_cpu_ms DESC;

Tune the worst offenders first with indexing, parameterization, or code fixes. Consider automatic tuning features — automatic index management and plan correction — where they fit your environment. Do not accept all automated recommendations blindly; review each one.

Database Watcher

Database Watcher is Microsoft’s centralized monitoring solution for Azure SQL, now generally available. It collects a rich dataset of performance, wait stats, active sessions, and storage metrics and stores it in a Fabric or Log Analytics workspace for long-term analysis and alerting. For larger Azure SQL portfolios it provides a single pane of glass across all databases and managed instances.

Backup, HA, and DR: What You Get and What You Still Own

Azure SQL PaaS gives you built-in backups, high availability, and recovery options — but you still set policies, configure geo-redundancy, and most importantly, test your DR procedures.

Cost Management: You Are the Performance and Cost Owner

Azure SQL lets you scale with a slider — powerful and dangerous. Your job is to balance performance with cost.

• Right-size compute — start reasonable, use Azure Monitor and Query Store to justify scaling, do not guess high
• Serverless — for bursty, low duty-cycle workloads, serverless databases with auto-pause can cut costs significantly
• Reserved capacity — pre-buy 1 or 3 years for predictable workloads to lower cost substantially
• Elastic pools — consolidate small noisy databases when the workload pattern fits
• Tagging — consistent tags on all resources are essential for cost visibility by team, environment, or workload

Best Practices Checklist

• Choose the right Azure SQL offer for each workload (Database vs MI vs VM)
• Consistent naming conventions for servers, databases, resource groups, and tags
• Dev, test, and prod separated into different resource groups or subscriptions
• vCore model used for new databases — plan migration away from DTU

• Microsoft Entra ID for all admin and human access — minimal SQL logins
• Private endpoints for production workloads
• TDE on — consider customer-managed keys for strict compliance
• Auditing and Defender for SQL configured according to organization standards
• No wide-open firewall rules (0.0.0.0–255.255.255.255)

• Query Store on and in Read-Write mode for all user databases
• Baseline metrics captured with Azure Monitor and Log Analytics
• Top queries and index recommendations reviewed regularly — not blindly accepted
• Azure Monitor alerts set for CPU, IO, and storage thresholds

• Backup retention set according to RPO and compliance requirements
• LTR configured where compliance requires long-term backup storage
• Geo-replication or failover groups for critical workloads
• Failover tested and runbook documented before production deployment

• Provisioning and configuration are scripted or templated — not hand-built
• Schema changes deployed via pipelines (DACPAC, Flyway, or similar)
• Common DBA tasks documented and automated
• Consistent tagging on all resources for cost management

Common Mistakes and Why Nots

Treating Azure SQL Database Like a Full SQL Server Instance

Azure SQL Database is a database-scoped PaaS service. You cannot rely on cross-database queries the same way, SQL Agent jobs on the “same server,” or instance-level settings. If you need those capabilities, Azure SQL Managed Instance is the right choice — not workarounds in Azure SQL Database.

Public Endpoint with Wide-Open Firewall Rules

Massive attack surface, difficult compliance story, and you will eventually face pressure to redesign from scratch. Private endpoints and secure network design early save considerable pain later.

Scaling Up Instead of Tuning

Scaling up hides bad queries and schema design until the cost becomes unacceptable. You want to use Query Store and DMVs to tune the workload, then use scale as a complement — not a replacement for analysis.

No Tagging or Cost Visibility

Without consistent tags you cannot answer “what are we paying for environment X or department Y.” Azure Cost Management depends heavily on good tagging. Apply tags at provisioning time — retrofitting them later is painful.

Assuming Backups Mean DR Is Solved

Backups cover many scenarios but do not give you low-RTO regional failover. You still need geo-replication, failover groups, tested failover procedures, and documented runbooks.

Hands-On Workshop: Build a Production-Ready Azure SQL Environment

Work through this workshop in a dev subscription. The goal is to behave like an Azure SQL DBA from requirements through to a working system with monitoring, security, and DR.

Resource Groups:      rg-contoso-dev-sql      rg-contoso-prod-sql
Logical Servers:      sql-contoso-dev-01      sql-contoso-prod-01
Databases:            contosoorders-dev       contosoorders-prod

-- Dev: General Purpose, 2 vCore, 32 GB storage, 7-day backup retention
-- Prod: General Purpose, 4 vCore, 128 GB storage, 14-35 day retention

# Azure CLI — create prod database
az sql db create \
  --resource-group rg-contoso-prod-sql \
  --server sql-contoso-prod-01 \
  --name contosoorders-prod \
  --service-objective GP_Gen5_4 \
  --storage-size 131072 \
  --backup-storage-redundancy Geo

-- Inside contosoorders-prod
-- Create a contained database user for the app's managed identity
CREATE USER [ContosoAppProd] FROM EXTERNAL PROVIDER;

-- Grant least-privilege permissions through roles
ALTER ROLE db_datareader ADD MEMBER [ContosoAppProd];
ALTER ROLE db_datawriter ADD MEMBER [ContosoAppProd];

-- For stored procedure execute access only (preferred over direct table access):
-- GRANT EXECUTE ON SCHEMA::Sales TO [ContosoAppProd];

CREATE SCHEMA Sales;
GO

CREATE TABLE Sales.Orders
(
    OrderID      INT            IDENTITY(1,1) PRIMARY KEY,
    CustomerID   INT            NOT NULL,
    OrderDate    DATETIME2(2)   NOT NULL DEFAULT SYSUTCDATETIME(),
    OrderTotal   DECIMAL(18,2)  NOT NULL,
    OrderStatus  NVARCHAR(50)   NOT NULL,
    CreatedBy    NVARCHAR(50)   NOT NULL,
    CreatedAtUtc DATETIME2(2)   NOT NULL DEFAULT SYSUTCDATETIME()
);

CREATE INDEX IX_Orders_OrderDate   ON Sales.Orders (OrderDate);
CREATE INDEX IX_Orders_CustomerID  ON Sales.Orders (CustomerID);

-- Confirm Query Store is on and in Read-Write mode
ALTER DATABASE [contosoorders-prod]
SET QUERY_STORE = ON;

ALTER DATABASE [contosoorders-prod]
SET QUERY_STORE (
    OPERATION_MODE          = READ_WRITE,
    CLEANUP_POLICY          = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    MAX_STORAGE_SIZE_MB     = 1024,
    QUERY_CAPTURE_MODE      = AUTO
);

-- In Log Analytics workspace — query recent Azure SQL audit events
AzureDiagnostics
| where Category == "SQLSecurityAuditEvents"
| where TimeGenerated > ago(24h)
| project TimeGenerated, LogicalServerName_s, DatabaseName_s,
          action_name_s, client_ip_s, server_principal_name_s
| order by TimeGenerated desc
| take 100

# Create an auto-failover group between prod and a paired region
az sql failover-group create \
  --name "contoso-fog-prod" \
  --partner-server sql-contoso-prod-secondary \
  --resource-group rg-contoso-prod-sql \
  --server sql-contoso-prod-01 \
  --failover-policy Automatic \
  --grace-period 60

# Application connection string should use the listener, not the server name:
# Server=tcp:contoso-fog-prod.database.windows.net,1433;
# -- Always routes to current primary after failover

# Trigger a planned failover (non-production test)
az sql failover-group set-primary \
  --name "contoso-fog-prod" \
  --resource-group rg-contoso-prod-sql \
  --server sql-contoso-prod-secondary

Final Thoughts

Being an Azure SQL DBA is not about giving up classic DBA skills — it is about applying them in a cloud context where the platform handles backups, patching, and core high availability, but you still own design, security, performance, and cost.

References

• Microsoft Learn – What is Azure SQL Database
• Microsoft Learn – Azure SQL Managed Instance Overview
• Microsoft Learn – Monitor and Performance Tuning Overview
• Microsoft Learn – Security Best Practices for Azure SQL
• Microsoft Learn – Query Performance Insight
• Microsoft Learn – Database Watcher for Azure SQL
• Microsoft Learn – Automated Backups in Azure SQL Database
• Microsoft Learn – Auto-Failover Groups for Azure SQL Database
• Microsoft Learn – vCore Purchasing Model
• Microsoft Learn – Migrate from DTU to vCore