Azure SQL Database Administration & Performance Optimization

Overview

End-to-end Azure SQL Database administration toolkit covering deployment, security, performance optimization, backup/restore, and cost management using a hybrid approach of Azure Portal and automation scripts.

Implementation Approach

• Provisioning: Azure Portal for initial setup + ARM templates for repeatable deployments
• Security Configuration: Portal-based firewall rules + T-SQL for authentication
• Performance Tuning: Portal Query Performance Insight + T-SQL index optimization scripts
• Backup & Recovery: Portal automated backups + PowerShell for restore testing
• Monitoring: Azure Monitor dashboards + custom alert rules

---

Real-World Implementation

Client: E-Commerce Platform (150GB Database, 5000 daily transactions)

Implementation: Portal + Scripts Hybrid Approach

Phase 1: Analysis (Portal + T-SQL Scripts)

Portal Analysis:

1. Azure Portal → SQL Database → Query Performance Insight

   • Identified top 10 resource-consuming queries
   • Found 3 queries consuming 65% of DTUs
   • Detected missing indexes on OrderDetails table

2. Portal → Database Advisor

   • Reviewed automatic tuning recommendations
   • Found 15 index recommendations
   • Identified unused indexes consuming 40GB

Script-Based Analysis:

-- Run: scripts/t-sql/performance/missing-indexes.sql

Phase 2: Performance Optimization

Portal Tasks:

1. Enable Automatic Tuning (Portal → Database → Automatic tuning)

   • CREATE INDEX: Enabled
   • DROP INDEX: Enabled (for unused indexes)
   • FORCE PLAN: Enabled

2. Configure Read Scale-Out (Portal → Database → Compute + storage)

   • Enabled read-only replica for reporting queries
   • Redirected analytics workload to secondary replica
   • Reduced primary database load

T-SQL Script Tasks:

1. Index Optimization

-- Created missing indexes (from analysis)
CREATE NONCLUSTERED INDEX IX_OrderDetails_ProductID 
ON OrderDetails(ProductID) 
INCLUDE (Quantity, UnitPrice);

-- Rebuilt fragmented indexes
ALTER INDEX ALL ON Orders REBUILD;

-- Dropped unused indexes (40GB freed)
DROP INDEX IX_Unused_CustomerEmail ON Customers;

2. Query Optimization

-- Rewrote top expensive query
-- Before: Full table scan (850ms)
-- After: Index seek (45ms) = 95% improvement

Result: Average query time reduced from 850ms to 470ms (45% improvement)

Phase 3: Cost Optimization

Portal Tasks:

1. Service Tier Right-Sizing (Portal → Compute + storage)

   • Analyzed DTU consumption over 30 days
   • Peak usage: 180 DTUs (45% of 400 DTU capacity)
   • Action: Downgraded from S6 (400 DTU) to S3 (100 DTU)
   • Cost: $850/month → $450/month

2. Storage Optimization (Portal → Database)

   • Deleted old indexes (freed 40GB)
   • Configured data compression
   • Storage: 150GB → 110GB
   • Cost: Saved $12/month on storage

3. Backup Retention Optimization (Portal → Automated backups)

   • Changed from 35-day retention to 14-day (compliance minimum)
   • Long-term retention: Monthly backups only (not weekly)
   • Cost: Saved $58/month

PowerShell Automation:

# Scheduled script: Monitor DTU and auto-scale
.\scripts\powershell\Monitor-And-Scale.ps1
# If DTU > 80% for 30 minutes → Scale up
# If DTU < 40% for 7 days → Recommend scale down

Total Cost Reduction: $850/month → $520/month (39% savings)

Phase 4: Security Hardening

Portal Tasks:

1. Firewall Configuration (Portal → Networking)

   • Removed "Allow Azure services" (too broad)
   • Added specific IP ranges for application servers
   • Enabled "Deny public network access" for production

2. Azure AD Authentication (Portal → Azure Active Directory admin)

   • Configured Azure AD admin
   • Removed SQL authentication for production (Azure AD only)
   • Enabled MFA for admin access

3. Advanced Threat Protection (Portal → Security → Microsoft Defender)

   • Enabled SQL injection detection
   • Configured email alerts for suspicious activity
   • Set up vulnerability assessment scans

T-SQL Tasks:

1. Row-Level Security (for multi-tenant SaaS)

-- Implemented RLS to isolate customer data
CREATE FUNCTION dbo.fn_securitypredicate(@CustomerID INT)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @CustomerID = CAST(SESSION_CONTEXT(N'CustomerID') AS INT);

CREATE SECURITY POLICY CustomerFilter
ADD FILTER PREDICATE dbo.fn_securitypredicate(CustomerID) ON dbo.Orders;

2. Dynamic Data Masking

-- Mask credit card numbers for non-admin users
ALTER TABLE Customers 
ALTER COLUMN CreditCard ADD MASKED WITH (FUNCTION = 'partial(0,"XXXX-XXXX-XXXX-",4)');