Azure SQL DBA - Interview Preparation (Part 2)
1. Azure SQL Deployment & Configuration
• Provisioning Azure SQL Database (Single Database, Elastic Pools).
• Provisioning Azure SQL Managed Instance (network requirements, VNet integration).
• Service Tiers: DTU vs vCore models, scaling up/down.
• Script Example (Azure CLI): az sql db create --resource-group MyRG --server MyServer
--name MyDB --service-objective S3
2. Backup, HA/DR & Failover
• Automatic Backups (7-35 day retention), Geo-restore.
• Active Geo-Replication & Auto-Failover Groups.
• Point-in-Time Restore using Azure Portal & PowerShell.
• PowerShell Example: Restore-AzSqlDatabase -ResourceGroupName 'MyRG'
-ServerName 'MyServer' -DatabaseName 'MyDB' -PointInTime '2025-08-26T[Link]Z'
3. Performance Monitoring & Query Optimization
• Query Store usage for capturing performance history.
• Intelligent Query Processing features (memory grant feedback, adaptive joins).
• Azure Monitor & Log Analytics integration.
• Troubleshooting slow queries using sys.dm_exec_query_stats, sys.dm_exec_requests.
4. Security & Compliance
• Transparent Data Encryption (TDE), Always Encrypted.
• Role-Based Access Control (RBAC) & Managed Identities.
• Auditing & Threat Detection, Azure Defender for SQL.
• Integration with Azure Key Vault for secrets management.
5. Automation
• PowerShell for automated tasks (backups, scaling, auditing).
• Azure CLI for script-based management.
• Terraform/ARM templates for Infrastructure-as-Code deployments.
6. DevOps (CI/CD for Databases)
• Using Azure DevOps Pipelines for schema deployment.
• SQL Server Data Tools (SSDT) for database projects.
• Continuous Integration (unit tests, linting), Continuous Delivery (release pipelines).
7. Real-World Troubleshooting Scenarios
• Performance Degradation in Production: - Check Query Store for regressed queries. -
Use Intelligent Performance Recommendations.
• Failover Group Not Syncing: - Validate endpoint health, resolve DNS or network issues.
• High DTU Utilization: - Scale database tier, optimize queries, use Elastic Pools.
• Hybrid Migration Issues (On-Prem to Azure): - Validate connectivity, firewall,
authentication. - Use Data Migration Assistant (DMA) to detect compatibility issues.
8. Interview Questions (Beginner to Advanced)
• Explain DTU vs vCore pricing models in Azure SQL.
• How do you implement Active Geo-Replication?
• Scenario: Your Azure SQL DB shows high CPU usage – how do you troubleshoot?
• How would you secure sensitive data in Azure SQL Database?
• Scenario: Failover group failed to failover during an outage – what steps would you take?
• Describe your approach to migrating a 2TB on-prem SQL database to Azure with minimal
downtime.
