Administering Microsoft Azure SQL Solutions

Outline

• Prepare to maintain SQL databases on Azure
  • Describe Microsoft Intelligent Data Platform roles
  • Understand SQL Server in an Azure virtual machine
  • Design Azure SQL Database for cloud-native applications
  • Explore Azure SQL Database Managed Instance
  • Knowledge check
• Deploy IaaS solutions with Azure SQL
  • Explain IaaS options to deploy SQL Server in Azure
  • Understand hybrid scenarios
  • Explore performance and security
  • Explain high availability and disaster recovery options
  • Exercise: Provision a SQL Server on an Azure Virtual Machine
  • Knowledge check
• Deploy PaaS solutions with Azure SQL
  • Explain PaaS options for deploying SQL Server in Azure
  • Explore single SQL database
  • Deploy SQL database elastic pool
  • Understand SQL database hyperscale
  • Examine SQL managed instance
  • Describe SQL Edge
  • Exercise: Deploy an Azure SQL Database
  • Knowledge check
• Evaluate strategies for migrating to Azure SQL
  • Understand compatibility level
  • Understand Azure preview features
  • Describe Azure database migration options
  • Knowledge check
• Migrate SQL Server workloads to Azure SQL Database
  • Choose the right Azure SQL Database feature
  • Use Azure SQL migration extension to migrate to Azure SQL Database
  • Explore Data Migration Assistant to migrate to Azure SQL Database
  • Migrate to Azure SQL Database using BACPAC
  • Use an online method to migrate to Azure SQL Database
  • Move data to Azure SQL Database
  • Exercise - Migrate a SQL Server database to Azure SQL Database
  • Knowledge check
• Migrate SQL Server workloads to Azure SQL Managed Instance
  • Evaluate migration scenarios
  • Use Log Replay Service (LRS) to migrate
  • Migrate using Managed Instance link
  • Move data to SQL Managed Instance
  • Exercise - Migrate a SQL Server database to Azure SQL Managed Instance
  • Knowledge check
• Configure database authentication and authorization
  • Describe Active Directory and Microsoft Entra ID
  • Describe authentication and identities
  • Describe Security Principals
  • Describe database and object permissions
  • Identify authentication and authorization failures
  • Exercise: Authorize Access to Azure SQL Database with Microsoft Entra ID
  • Knowledge check
• Protect data in-transit and at rest
  • Explore Transparent Data Encryption
  • Configure server and database firewall rules
  • Explain object encryption and secure enclaves
  • Enable encrypted connections
  • Describe SQL injection
  • Understand Azure Key Vault
  • Exercise: Configure a server-based firewall rule using the Azure portal
  • Knowledge check
• Implement compliance controls for sensitive data
  • Explore data classification
  • Explore server and database audit
  • Implement Dynamic Data Masking
  • Implement Row Level security
  • Understand Microsoft Defender for SQL
  • Explore Azure SQL Database Ledger
  • Implement Azure Purview
  • Exercise: Enable Microsoft Defender for SQL and Data Classification
  • Knowledge check
• Describe performance monitoring
  • Describe performance monitoring tools
  • Describe critical performance metrics
  • Establish baseline metrics
  • Explore extended events
  • Describe Azure SQL Insights
  • Explore Query Performance Insight
  • Exercise: Isolate problems with monitoring
  • Knowledge check
• Configure SQL Server resources for optimal performance
  • Explain how to optimize Azure storage for SQL Server virtual machines
  • Describe virtual machine resizing
  • Optimize database storage
  • Control SQL Server resources
  • Knowledge check
• Configure databases for optimal performance
  • Explore database maintenance checks
  • Describe database scoped configuration options
  • Describe automatic tuning
  • Describe intelligent query processing
  • Exercise: Detect and correct fragmentation issues
  • Knowledge check
• Explore query performance optimization
  • Understand query plans
  • Explain estimated and actual query plans
  • Describe dynamic management views and functions
  • Explore Query Store
  • Identify problematic query plans
  • Describe blocking and locking
  • Exercise: Identify and resolve blocking issues
  • Knowledge check
• Explore performance-based design
  • Describe normalization
  • Choose appropriate data types
  • Design indexes
  • Exercise: Identify database design issues
  • Knowledge check
• Evaluate performance improvements
  • Describe wait statistics
  • Tune and maintain indexes
  • Understand query hints
  • Exercise: Isolate problem areas in poorly performing queries
  • Knowledge check
• Automate deployment of database resources
  • Describe deployment models in Azure
  • Automate deployment by using Azure Resource Manager templates and Bicep
  • Automate deployment by using PowerShell
  • Automate deployment by using Azure CLI
  • Exercise: Deploy an Azure SQL Database using an Azure Resource Manager template
  • Knowledge check
• Create and manage SQL Agent jobs
  • Create a SQL Server maintenance plan
  • Describe task status notifications
  • Knowledge check
  • Exercise: Create a CPU status alert for a SQL Server
• Manage Azure PaaS tasks using automation
  • Explore Elastic jobs
  • Understand Azure Automation
  • Build an automation runbook
  • Automate database workflows by using Logic Apps
  • Monitor automated tasks
  • Exercise: Deploy an automation runbook to automatically rebuild indexes
  • Knowledge check
• Describe high availability and disaster recovery strategies
  • Describe recovery time objective and recovery point objective
  • Explore high availability and disaster recovery options
  • Describe Azure high availability and disaster recovery features for Azure Virtual Machines
  • Describe high availability and disaster recovery options for PaaS deployments
  • Explore an IaaS high availability and disaster recovery solution
  • Describe hybrid solutions
  • Knowledge check
• Explore IaaS and PaaS solutions for high availability and disaster recovery
  • Describe failover clusters in Windows Server
  • Configure Always-on availability groups
  • Describe temporal tables in Azure SQL Database
  • Describe active geo-replication for Azure SQL Database
  • Explore auto-failover groups for Azure SQL Database and Azure SQL Managed Instance
  • Exercise: Configure geo replication for Azure SQL Database
  • Knowledge check
• Back up and restore databases
  • Back up and restore SQL Server running on Azure virtual machines
  • Back up a SQL Server virtual machine
  • Back up and restore a database using Azure SQL Database
  • Exercise: Backup to URL
  • Knowledge check

Plan and Implement Data Platform Resources

• Deploy IaaS solutions with Azure SQL
• Deploy PaaS solutions with Azure SQL
• Evaluate strategies for migrating to Azure SQL
• Migrate SQL workloads to Azure SQL Databases
• Migrate SQL workloads to Azure Managed Instances

Implement a Secure Environment for a Database Service

• Configure database authentication and authorization
• Protect data in-transit and at rest
• Implement compliance controls for sensitive data

Monitor and Optimize Operational Resources

• Describe performance monitoring
• Configure SQL Server resources for optimal performance
• Configure databases for optimal performance

Optimize Query Performance

• Explore query performance optimization
• Explore performance-based database design
• Evaluate performance improvements

Automate database tasks

• Automate deployment of database resources
• Create and manage SQL Agent jobs
• Manage Azure PaaS tasks using automation

Plan and Implement a High Availability and Disaster Recovery Solution

• High Availability and Disaster Recovery Strategies
• IaaS Platform and Database Tools for HADR
• PaaS Platform and Database Tools for HADR
• Database Backup and Recovery