Plan and deploy the data platform
- Choose the right database offering: Azure SQL Database, SQL Managed Instance, SQL Server on virtual machines
- Automated deployment, hybrid SQL Server solutions, Azure Arc-enabled SQL services
- Plan table partitioning and sharding, data compression
- Configuration for scale and performance across all three platforms
- Migration: assess requirements, online and offline strategy, migration to Azure and between Azure SQL services, troubleshooting
Secure environment
- Authentication and authorisation: Microsoft Entra ID, security principals, database- and object-level permissions, the principle of least privilege
- Encryption of data at rest and in transit: Transparent Data Encryption, Always Encrypted, firewall rules, Private Link and service endpoints
- Compliance: data classification, server and database auditing, change data tracking, dynamic data masking, Ledger, row-level security
Monitor and optimise
- Establish a performance baseline, collect and interpret metrics, Database Watcher and Extended Events
- Query performance: Query Store, detecting blocking, dynamic management views, reading execution plans, deriving index changes, Intelligent Insights
- Index and statistics maintenance, integrity checks, automatic tuning, Resource Governor, Intelligent Query Processing
Automation
- SQL Server Agent: jobs, schedules, alerts and notifications, troubleshooting
- Automate deployment with ARM and Bicep templates, Azure PowerShell and Azure CLI
- Elastic jobs and automated database tasks in Azure
High availability and disaster recovery
- Design a strategy around RPO and RTO, evaluate hybrid and Azure-specific solutions, plan test procedures
- Backup and restore, point-in-time restore, long-term retention, backup to cloud storage
- Active geo-replication, Always On availability groups, failover groups, failover cluster instances, log shipping
