I. Overview:

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.

II. Duration: 04 days (32 hours)

III. Intended Audience:

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.

IV. Course outlines:

1. Plan and implement data platform resources

Deploy data platform resources in Azure SQL. Explore options for deployment and migration. Calculate resource requirements and create templates.

• Prepare to maintain SQL Server-based databases on Azure: Explore the role of a database administrator on Azure. Describe SQL Server-based offerings on Azure.
• Deploy IaaS solutions with Azure SQL: Configure virtual machine sizing, storage, and networking options to ensure adequate performance for your database workloads. Choose and configure appropriate high availability options.
• Deploy PaaS solutions with Azure SQL: Provision and deploy Azure SQL Database and Azure SQL managed instance. Select the appropriate options when performing a migration to the SQL PaaS platform.
• Migrate SQL Server workloads to Azure SQL Database: You will explore different migration tools and migrate SQL Server databases to Azure SQL Database.
• Migrate SQL Server workloads to Azure SQL Managed Instance: You'll explore different migration tools and migrate SQL Server databases to Azure SQL Managed Instance.

2. Implement a secure environment for a database service

Implement SQL Server-based options for authentication and authorization, as well as Azure options for securing Azure SQL databases. Explore encryption, firewalls and advanced threat protection.

• Configure database authentication and authorization: Contrast authentication using Microsoft Entra ID, and SQL Server authentication. Implement various security principals and configure permissions.
• Protect data in-transit and at rest: Explore encryption options available within Azure SQL, including firewall rules, Always Encrypted, and Transport Layer Security. Understand how SQL Injection works.
• Implement compliance controls for sensitive data: Explore data classification capabilities and degrees of confidentiality. Implement security options to maintain private data safe, including Azure SQL auditing, Microsoft Defender for SQL, row-level security, Dynamic Data Masking and Ledger.

3. Monitor and optimize operational resources in Azure SQL

Configure hardware and server resources for optimum performance after establishing a baseline. Identify sources of performance problems. Troubleshoot using Azure SQL Intelligent Insights.

• Describe performance monitoring: Compare Azure, and on-premises tools for monitoring and measuring performance. Determine critical metrics. Understand the purpose of a baseline for comparative analysis. Configure extended event sessions for tracing activities.
• Configure SQL Server resources for optimal performance: Choose the appropriate size and storage options for your Azure SQL databases. Configure server resources such as tempdb. Understand the usage of Resource Governor.
• Configure databases for optimal performance: Implement tasks for both IaaS and PaaS to maintain indexes, and statistics. Explore the automatic tuning features of Azure SQL Database. Control database-level configuration options. Explore Intelligent Query Processing.

4. Optimize query performance in Azure SQL

Analyze individual query performance and determine where improvements can be made. Explore performance-related Dynamic Management Objects. Investigate how indexes and database design affect queries.

• Explore query performance optimization: Read and understand various forms of execution plans. Compare estimated vs actual plans. Learn how and why plans are generated. Understand the purpose and benefits of the Query Store.
• Explore performance-based database design: Explore normalization for relational databases. Investigate the impact of proper datatype usage. Compare types of indexes.
• Evaluate performance improvements: Evaluate possible changes to indexes. Determine the impact of changes to queries and indexes. Explore Query Store hints.

5. Automate database tasks for Azure SQL

Scripting repetitive tasks in Azure SQL for regular maintenance jobs and multi-instance administration. Automation of error and resource alerts. Configuration of notifications for failures.

• Automate database deployment: Explore multiple deployment models available on Azure. Use Azure Resource Manager (ARM) templates and Bicep files for deploying Azure SQL resources. Understand how to use PowerShell and Azure CLI for automation purposes.
• Create and manage SQL Agent jobs: Explore SQL automation for scheduled tasks, and automatic alerts for SQL Server and Azure SQL Managed Instance.
• Manage Azure PaaS tasks using automation: Explore automation for Azure SQL platform. Configure elastic jobs, explore Azure Automation, and evaluate different strategies for monitoring automation tasks.

6. Plan and implement a high availability and disaster recovery environment

Data must be available when needed. Configure, test and manage a solution for high availability and disaster recovery using Azure tools and SQL Server tools.

• Describe high availability and disaster recovery strategies: Plan an appropriate high availability and disaster recovery strategy based on recovery time objective and recovery point objective. Choose the best solution for IaaS or PaaS deployments or hybrid workloads.
• Explore IaaS and PaaS solutions for high availability and disaster recovery: Deploy Windows Server Failover Cluster and availability groups in Azure and hybrid environments. Configure temporal tables, geo-replication, and auto-failover groups.
• Back up and restore databases: Plan and implement policy for recovering data if user errors occur or the technology fails. Explore various options for how and where to back up and restore databases.