Azure SQL

Azure SQL Skill

Manage and analyze Azure SQL databases using az sql commands.

Discovery-First Rule

ALWAYS discover before acting. Never assume server names, database names, or elastic pool names.

# Discover SQL servers
az sql server list --output json \
  --query "[].{name:name, rg:resourceGroup, fqdn:fullyQualifiedDomainName, state:state, adminLogin:administratorLogin}"

# Discover databases per server
az sql db list --server "$SERVER" --resource-group "$RG" --output json \
  --query "[].{name:name, status:status, sku:currentSku, maxSize:maxSizeBytes, zoneRedundant:zoneRedundant}"

Parallel Execution Requirement

ALL independent operations MUST run in parallel using background jobs (&) and wait.

for server_info in $(echo "$servers" | jq -c '.[]'); do
  {
    name=$(echo "$server_info" | jq -r '.name')
    rg=$(echo "$server_info" | jq -r '.rg')
    az sql db list --server "$name" --resource-group "$rg" --output json
  } &
done
wait

Helper Functions

# Get database service tier and performance details
get_db_tier() {
  local server="$1" rg="$2" db="$3"
  az sql db show --server "$server" --resource-group "$rg" --name "$db" --output json \
    --query "{sku:currentSku, maxSize:maxSizeBytes, status:status, earliestRestoreDate:earliestRestoreDate, zoneRedundant:zoneRedundant, readScale:readScale}"
}

# Get DTU/vCore usage metrics
get_db_metrics() {
  local resource_id="$1"
  az monitor metrics list --resource "$resource_id" \
    --metric "dtu_consumption_percent" "cpu_percent" "storage_percent" "deadlock" "connection_failed" \
    --interval PT1H --aggregation Average Max --output json
}

# List elastic pools
list_elastic_pools() {
  local server="$1" rg="$2"
  az sql elastic-pool list --server "$server" --resource-group "$rg" --output json \
    --query "[].{name:name, sku:sku, maxSizeBytes:maxSizeBytes, perDbMinDtu:perDatabaseSettings.minCapacity, perDbMaxDtu:perDatabaseSettings.maxCapacity, state:state, zoneRedundant:zoneRedundant}"
}

# Get geo-replication links
get_geo_replication() {
  local server="$1" rg="$2" db="$3"
  az sql db replica list-links --server "$server" --resource-group "$rg" --name "$db" --output json
}

Common Operations

1. Database Health Overview

servers=$(az sql server list --output json --query "[].{name:name, rg:resourceGroup}")
for s in $(echo "$servers" | jq -c '.[]'); do
  {
    server=$(echo "$s" | jq -r '.name')
    rg=$(echo "$s" | jq -r '.rg')
    az sql db list --server "$server" --resource-group "$rg" --output json \
      --query "[?name!='master'].{name:name, sku:currentSku.name, tier:currentSku.tier, capacity:currentSku.capacity, status:status, maxSizeGB:maxSizeBytes}"
  } &
done
wait

2. DTU/vCore Performance Analysis

resource_id=$(az sql db show --server "$SERVER" --resource-group "$RG" --name "$DB" --query "id" -o tsv)
az monitor metrics list --resource "$resource_id" \
  --metric "dtu_consumption_percent" "cpu_percent" "physical_data_read_percent" "log_write_percent" \
  --interval PT1H --aggregation Average Max --output json

# Check if database needs scaling
az monitor metrics list --resource "$resource_id" \
  --metric "dtu_consumption_percent" --interval PT5M --aggregation Max \
  --start-time "$(date -u -v-24H +%Y-%m-%dT%H:%M:%SZ 2>/dev/null || date -u -d '24 hours ago' +%Y-%m-%dT%H:%M:%SZ)" --output json

3. Elastic Pool Management

# List pools and their databases
pools=$(list_elastic_pools "$SERVER" "$RG")
for pool in $(echo "$pools" | jq -c '.[]'); do
  {
    pool_name=$(echo "$pool" | jq -r '.name')
    echo "Pool: $pool_name"
    az sql elastic-pool show --server "$SERVER" --resource-group "$RG" --name "$pool_name" --output json
    az sql db list --server "$SERVER" --resource-group "$RG" --elastic-pool "$pool_name" --output json \
      --query "[].{name:name, status:status}"
  } &
done
wait

4. Query Performance Insights

# Top resource-consuming queries (requires Query Store enabled)
az sql db show --server "$SERVER" --resource-group "$RG" --name "$DB" --output json \
  --query "{queryStoreState:currentSku, readScale:readScale}"

# Check long-running queries via metrics
resource_id=$(az sql db show --server "$SERVER" --resource-group "$RG" --name "$DB" --query "id" -o tsv)
az monitor metrics list --resource "$resource_id" \
  --metric "blocked_by_firewall" "deadlock" "connection_failed" "connection_successful" \
  --interval PT1H --aggregation Total --output json

5. Geo-Replication Status

# Check replication links and lag
az sql db replica list-links --server "$SERVER" --resource-group "$RG" --name "$DB" --output json \
  --query "[].{partnerServer:partnerServer, partnerDatabase:partnerDatabase, role:role, replicationState:replicationState, percentComplete:percentComplete}"

# List failover groups
az sql failover-group list --server "$SERVER" --resource-group "$RG" --output json \
  --query "[].{name:name, readWriteEndpoint:readWriteEndpoint, readOnlyEndpoint:readOnlyEndpoint, replicationRole:replicationRole, databases:databases}"

Output Format

Present results as a structured report:

Azure Sql Report
════════════════
Resources discovered: [count]

Resource       Status    Key Metric    Issues
──────────────────────────────────────────────
[name]         [ok/warn] [value]       [findings]

Summary: [total] resources | [ok] healthy | [warn] warnings | [crit] critical
Action Items: [list of prioritized findings]

Target ≤50 lines of output. Use tables for multi-resource comparisons.

Anti-Hallucination Rules

1. NEVER assume resource names — always discover via CLI/API in Phase 1 before referencing in Phase 2.
2. NEVER fabricate metric names or dimensions — verify against the service documentation or --help output.
3. NEVER mix CLI commands between service versions — confirm which version/API you are targeting.
4. ALWAYS use the discovery → verify → analyze chain — every resource referenced must have been discovered first.
5. ALWAYS handle empty results gracefully — an empty response is valid data, not an error to retry.

Counter-Rationalizations

Common Pitfalls

1. DTU vs vCore confusion: DTU-based tiers use dtu_consumption_percent metric; vCore-based use cpu_percent. Check the SKU tier first.
2. Elastic pool oversubscription: Per-database max DTU can exceed pool total. Check aggregate usage, not just per-db settings.
3. master database: The master database always appears in listings. Filter it out with [?name!='master'] in queries.
4. Serverless auto-pause: Serverless tier databases may show zero metrics when paused. Check autoPauseDelay setting.
5. Geo-replication lag: replicationState shows link health but not actual data lag. Use replication_lag metric for RPO assessment.