Implement scalable database solutions using Azure SQL (DP-300)

This Implement scalable database solutions using Azure SQL (DP-300) certification course provides students with the knowledge and skills to administer 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 helpful to individuals who develop applications that deliver content from SQL-based relational databases.

Implement scalable database solutions using Azure SQL (DP-300) Benefits

• In this Administering Microsoft Azure SQL Solutions (DP-300) course, you will learn how to:

  • Plan, deploy and configure Azure SQL offerings.
  • Monitor database performance and tune a database and queries for optimum performance.
  • Plan and configure a High Availability Solution.

• Administering Microsoft Azure SQL Solutions (DP-300) Prerequisites

  Successful Azure Database Administrators start this role with professional experience in database management and technical knowledge of cloud technologies. Specifically: 

  • Working with, maintaining, and developing with SQL Server 
  • Experience with Azure, such as deploying and managing resources 

  At a minimum, you should know the information in the following courses before attending the course:

  • Introduction to Microsoft Azure (AZ-900)
  • Microsoft Azure Data Fundamentals Training (DP-900)

• Administering Microsoft Azure SQL Solutions (DP-300) Exam Information

  This course can help you prepare for the following Microsoft role-based certification exam — Exam DP-300: Microsoft Certified: Azure Database Administrator Associate.

DP-300 Certification Course Outline

Learning Objectives

Plan and implement data platform resources

• Prepare to maintain SQL Server-based databases on Azure

• • Understand the role of Azure Database Administrator as it fits in with other data platform roles.
  • Describe the key differences between the SQL Server-based database options in Azure.
  • Describe other features for Azure SQL platforms available.

• Deploy IaaS solutions with Azure SQL

• • Explore the basics of SQL Server in an Infrastructure as a Service (IaaS) offering
  • Learn the available options for provisioning and deployment
  • Deploy SQL Server into an Azure Virtual Machine

• Deploy PaaS solutions with Azure SQL

• • Gain an understanding SQL Server in a Platform as a Service (PaaS) offering
  • Understand PaaS provisioning and deployment options
  • Understand elastic pools
  • Examine Azure SQL Managed Instances
  • Configure a template for PaaS deployment

• Migrate SQL Server workloads to Azure SQL Database

• • Explore the advantages, capabilities, and migration possibilities offered by Azure SQL Database.
  • Migrate databases using Azure SQL Migration extension for Azure Data Studio and tracking database migration activities.
  • Use transactional replication as an online method to migrate to Azure SQL Database.
  • Explore several other methods for migrating SQL Server databases to Azure SQL Database.

• Migrate SQL Server workloads to Azure SQL Managed Instance

• • Explore the advantages, capabilities, and migration possibilities offered by Azure SQL Managed Instance.
  • Learn how Log Replay Service works to migrate to Azure SQL Managed Instance.
  • Understand how Managed Instance link feature works in a migration scenario.
  • Load and move data to and from Azure SQL Managed Instance.
  • Explore several other methods for migrating SQL Server databases to Azure SQL Database.

Implement a secure environment for a database service

• Configure database authentication and authorization

• • Learn about authentication options for Azure SQL
  • Create various security principals
  • Configure permissions within a SQL database
  • Identify authentication and authorization failures

• Protect data in-transit and at rest

• • Understand the data encryption options available in the various platforms
  • Implement object level encryption
  • Understand the difference between database and server firewall rules for Azure SQL Database
  • Explore Always Encrypted with secure enclaves

• Implement compliance controls for sensitive data

• • Plan and implement data classification in Azure SQL Database
  • Understand and configure row-level security and dynamic data masking
  • Understand the usage of Microsoft Defender for SQL
  • Explore how Ledger works

Monitor and optimize operational resources in Azure SQL

• Describe performance monitoring

• • Review potential performance issues.
  • Identify critical Azure metrics.
  • Learn how to collect metrics for an established baseline.
  • Use extended events for performance analysis.
  • Understand database watcher for Azure SQL Database and Azure SQL Managed Instance.

• Configure SQL Server resources for optimal performance

• • Understand your options for configuration of Azure storage
  • Learn how to configure TempDB data files in SQL Server
  • Learn how to choose the right type of VM for SQL Server workloads
  • Understand the use cases and configuration of Resource Governor in SQL Server

• Configure databases for optimal performance

• • Understand database scoped configuration options
  • Understand maintenance tasks related to indexing and statistics
  • Understand the features of Intelligent Query Processing (IQP)
  • Explore the automatic tuning feature in Azure

Optimize query performance in Azure SQL

• Explore query performance optimization

• • Generate and save execution plans
  • Compare the different types of execution plans
  • Understand how and why query plans are generated
  • Explain the purpose and benefits of the Query Store
  • Investigate the available reports and data in the Query Store

• Explore performance-based database design

• • Explore normal forms and how they affect database design
  • Choose appropriate datatypes for your data
  • Evaluate appropriate index types

• Evaluate performance improvements

• • Determine when changing indexes or defining new ones can affect performance
  • Evaluate wait statistics as an aid in finding areas for performance improvement
  • Understand how query hints work, and when to use them

Automate database tasks for Azure SQL

• Automate database deployment

• • Describe the deployment models available on Azure
  • Deploy database resources using PowerShell and Azure CLI
  • Deploy an Azure Resource Manager template and Bicep
  • Understand the difference between multiple command-line options

• Create and manage SQL Agent jobs

• • Schedule necessary maintenance activities for your databases.
  • Configure notifications and alerts on SQL Server Agent jobs, and SQL Server.
  • Configure alerts based on performance monitor values.

• Manage Azure PaaS tasks using automation

• • Understand the benefits of Azure policy
  • Explore the capabilities of Azure Automation
  • Configure elastic jobs
  • Use Logic Apps for database workflow

Plan and implement a high availability and disaster recovery environment

• Describe High Availability and Disaster Recovery Strategies
• Describe recovery time objective (RTO) and recovery point objective (RPO)
• Explore high availability and disaster recovery options
• Describe Azure high availability and disaster recovery features for Virtual Machines
• Describe high availability and disaster recovery for PaaS deployments
• Explore high availability and disaster recovery solutions for IaaS
• Describe hybrid solutions

Explore IaaS and PaaS Solutions for High Availability and Disaster Recovery

• Describe failover clusters in Windows Server
• Configure Always On Availability Groups
• Describe active geo-replication for Azure SQL Database
• Explore auto-failover groups for Azure SQL Database and Azure SQL Managed Instance
• Monitor availability
• Exercise: Configure geo-replication for Azure SQL Database

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 for SQL Database and SQL Managed Instance
• Exercise: Backup to URL