Administering Relational Databases on Microsoft Azure (Live Online)

Overview

This comprehensive course equips database professionals with essential knowledge and hands-on skills for administering SQL Server database infrastructure across cloud, on-premises, and hybrid environments. Whether working with Microsoft Azure Platform-as-a-Service relational database offerings or managing on-premises SQL Server instances, students will gain practical expertise in deployment, configuration, monitoring, optimization, and disaster recovery strategies.

Audience Profile:

Data professionals who manage databases and wish to advance their expertise in administering SQL Server-based data platforms on Microsoft Azure. The course is also valuable for data architects and application developers who need to understand available Azure data platform technologies and how to work with those technologies through applications.

Job Role:

Database Administrator

Certification Preparation:

This course prepares students for the DP-300 certification exam.

Key Skills You Will Develop:

Plan, deploy, and configure Azure SQL offerings for various deployment models
Monitor database performance and optimize queries and database design for optimal execution
Architect and implement high availability and disaster recovery solutions
Automate routine administration tasks and implement security best practices

Prerequisites:

In addition to professional experience, students should have technical knowledge equivalent to the following courses: Azure Fundamentals and Azure Data Fundamentals.

Course Structure:

Module 1: The Role of the Azure Database Administrator

This module explores the responsibilities and scope of a database administrator working with Azure platforms. Students gain foundational knowledge about SQL Server deployment options including SQL Server on virtual machines, Managed Instances, and Azure SQL Database. The module covers compatibility levels as a critical concept for SQL databases in Azure and introduces additional database platforms available on Azure such as PostgreSQL and MySQL.

Topics Covered

Azure Data Platform Roles and Responsibilities
Azure Database Platforms and Deployment Options
SQL Server Compatibility Levels
Azure Preview Features and Release Cycles

Hands-On Activities

Provision a SQL Server instance on an Azure Virtual Machine
Connect using SQL Server Management Studio and restore database backups
Explore the Azure Portal interface for database management

Module 2: Plan and Implement Data Platform Resources

This module covers methods for deploying and scaling data platform resources within Azure. Students learn about upgrading and migrating existing SQL databases to Azure, setting up infrastructure for SQL Server on virtual machines, Managed Instances, and Azure SQL Database. The module also covers the deployment of PostgreSQL and MySQL. Students will determine appropriate options based on requirements, including high availability and disaster recovery needs, calculate resource requirements, and create deployment templates.

Topics Covered

Deploying SQL Server using Infrastructure-as-a-Service models
Deploying SQL Server using Platform-as-a-Service models
Deploying Open Source Database Solutions on Azure
Migration strategies and tools

Hands-On Activities

Deploy virtual machines using ARM templates
Configure prerequisite resources and networking
Deploy and configure Azure SQL Database instances
Register and validate connectivity using Azure Data Studio
Deploy and test PostgreSQL or MySQL connectivity

Module 3: Implement a Secure Environment

This module examines security practices for SQL Server databases and Azure SQL Database. Students explore SQL Server security options and Azure security features for protecting databases and data within Azure SQL Database. The module emphasizes the critical importance of security in database administration and introduces additional Azure database platforms, including Azure Database for MariaDB, MySQL, and PostgreSQL.

Topics Covered

Configure Database Authentication Methods
Configure Database Authorization and Access Control
Implement Encryption for Data at Rest
Implement Encryption for Data in Transit
Implement Compliance Controls and Data Classification for Sensitive Information

Hands-On Activities

Configure server-based firewall rules through the Azure Portal
Authorize database access using Azure Active Directory
Enable Advanced Data Security and implement data classification
Manage permissions and access to database objects

Module 4: Monitor and Optimize Operational Resources

This module covers resource optimization for databases using both Infrastructure-as-a-Service and Platform-as-a-Service deployments. Students learn to monitor server and hardware resources, establish performance baselines, and interpret performance metrics. The module covers troubleshooting database performance issues using Azure SQL Database Intelligent Insights and Azure Data Studio.

Topics Covered

Establish Baselines and Monitor Performance Metrics
Identify and Address Major Performance Issues
Configure Resources for Optimal Performance
Optimize User Database Configuration
Perform Performance-related Maintenance Tasks

Hands-On Activities

Identify and isolate CPU performance problems
Use Query Store to identify and analyze blocking problems
Detect and correct index fragmentation issues

Module 5: Optimize Query Performance

Query execution plans represent a critical aspect of database performance. Improving poorly optimized execution plans can yield dramatic performance improvements, often in the range of 100 to 200 percent or more. While hardware upgrades typically provide improvements of 10 to 20 percent, optimizing query execution plans through improved indexes, statistics, and database design often delivers significantly greater gains. This module provides detailed guidance on analyzing individual query performance and identifying opportunities for optimization.

Topics Covered

Understanding SQL Server Query Plans and Execution Strategy
Explore Performance-based Database Design Principles
Evaluate and Measure Performance Improvements
Use Query Store for performance analysis

Hands-On Activities

Identify design issues in the AdventureWorks2017 sample database
Isolate and analyze problem areas in poorly performing queries
Use Query Store to detect and handle performance regressions
Apply query hints to impact query execution and performance

Module 6: Automation of Tasks

Database administrators routinely seek to automate repetitive tasks to increase efficiency and reduce errors. Automation approaches range from simple scripting for backup processes to sophisticated automated alerting systems. This module covers techniques for automating routine administrative tasks, scheduling maintenance jobs, administering multiple instances, and configuring notifications for task completion or failure.

Topics Covered

Setting up Automatic Deployment Processes
Defining and Scheduling Maintenance Tasks
Configuring Extended Events for Monitoring
Managing Azure Platform-as-a-Service Resources Using Automated Methods

Hands-On Activities

Deploy Azure templates from GitHub Quickstart templates
Configure notifications based on performance metrics and thresholds
Deploy and configure Azure Automation Runbooks for index maintenance

Module 7: Plan and Implement a High Availability and Disaster Recovery Environment

Data availability is a critical business requirement. Systems hosting organizational data must be designed with availability and recoverability as primary concerns. Consider a company relying on a highly transactional database for order processing; if the infrastructure becomes unavailable, business operations cease immediately. Implementing appropriate high availability and disaster recovery solutions ensures that databases return to service quickly with minimal administrative effort and business impact. This module covers configuring, testing, and managing high availability and disaster recovery solutions for both Infrastructure-as-a-Service and Platform-as-a-Service deployments in Azure.

Topics Covered