This course provides students with the knowledge and skills to create a SQL Server database infrastructure for cloud, on-premises and hybrid relational databases and who work with the Microsoft PaaS relational database offerings. Additionally, it will be of use to individuals who develop applications that deliver content from SQL-based relational databases.

The audience for this course is data professionals managing data and databases who want to learn about administering the data platform technologies that are available on Microsoft Azure. This course is also valuable for data architects and application developers who need to understand what technologies are available for the data platform with Azure and how to work with those technologies through applications.

• Familiarity with on-premises virtualization technologies, including VMs, virtual networking, and virtual hard disks.
• Familiarity with network configuration, including TCP/IP, Domain Name System (DNS), virtual private networks (VPNs), firewalls, and encryption technologies.
• Understanding of why security is a crucial part of database system planning.
• Experience creating and configuring resources using the Azure portal.
• Ability to create an on-premises SQL Server database, including the configuration of data files and log files.
• Ability to write code in the SQL language, particularly the Microsoft T-SQL dialect, at a basic level.
• Ability to use tools for running queries against a Microsoft SQL database, either on-premises or cloud-based.
• Basic understanding of relational database concepts.
• Basic understanding of SQL Server file storage.
• Basic understanding of the structure and usage of SQL Server indexes.
• Knowledge of the purpose of SQL Server system databases: master, tempdb, and msdb.
• Experience using Windows Performance Monitor to monitor system components such as CPU, I/O, and network traffic.

Prepare to maintain SQL Server-based databases on Azure
• Introduction
• 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 Managed Instance
• Module assessment
• Summary

Deploy IaaS solutions with Azure SQL

• Introduction
• 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
• Module assessment
• Summary

Deploy PaaS solutions with Azure SQL

• Introduction
• 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
• Exercise: Deploy an Azure SQL Database
• Module assessment
• Summary

Migrate SQL Server workloads to Azure SQL Database

• Introduction
• Choose the right Azure SQL Database feature
• Use Azure Database Migration Service 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
• Module assessment
• Summary

Migrate SQL Server workloads to Azure SQL Managed Instance

• Introduction
• Evaluate migration scenarios
• Migrate using Azure Arc
• 8 min
• 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
• Module assessment
• Summary

Configure database authentication and authorization

• Introduction
• 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
• Module assessment
• Summary

Protect data in-transit and at rest

• Introduction
• 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
• Module assessment
• Summary

Implement compliance controls for sensitive data

• Introduction
• Explore data classification
• Explore server and database audit
• Implement Dynamic Data Masking
• Implement Row Level security
• Understand Microsoft Defender for SQL
• Explore Ledger
• Implement Microsoft Purview
• Exercise: Enable Microsoft Defender for SQL and Data Classification
• Module assessment
• Summary

Describe performance monitoring

• Introduction
• Describe performance monitoring tools
• Describe critical performance metrics
• Establish baseline metrics
• Explore extended events
• Describe database watcher (preview)
• Explore Query Performance Insight
• Exercise: Isolate problems with monitoring
• Module assessment
• Summary

Configure SQL Server resources for optimal performance

• Introduction
• Explain how to optimize Azure storage for SQL Server virtual machines
• Describe virtual machine resizing
• Optimize database storage
• Control SQL Server resources
• Module assessment
• Summary

Configure databases for optimal performance

• Introduction
• Explore database maintenance checks
• Describe database scoped configuration options
• Describe automatic tuning
• Describe intelligent query processing
• Exercise: Detect and correct fragmentation issues
• Module assessment
• Summary

Explore query performance optimization

• Introduction
• 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
• Module assessment
• Summary

Explore performance-based database design

• Introduction
• Describe normalization
• Choose appropriate data types
• Design indexes
• Exercise: Identify database design issues
• Module assessment
• Summary

Evaluate performance improvements

• Introduction
• Describe wait statistics
• Tune and maintain indexes
• Understand query hints
• Explore performance scenarios
• Exercise: Isolate problem areas in poorly performing queries
• Module assessment
• Summary

Automate database deployment

• Introduction
• 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
• Module assessment
• Summary

Create and manage SQL Agent jobs

• Introduction
• Create a SQL Server maintenance plan
• Describe task status notifications
• Knowledge check
• Exercise: Create a CPU status alert for a SQL Server
• Summary

Manage Azure PaaS tasks using automation

• Introduction
• 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
• Module assessment
• Summary