.svg)
.svg)
.svg)
Machine Learning Engineer
The content of this intensive training is derived from the exam "DP-300: Administering Microsoft Azure SQL Solutions".
Module 1: Introduction to Azure database administration
This module looks at the role of a database administrator in the world of Azure SQL. It also provides some basic information that is relevant to the overall content. This includes an overview of the different SQL Server-based options (SQL Server in a VM, SQL Managed Instance and Azure SQL Database).
Lessons
- Preparing for the maintenance of SQL databases on Azure
Module 2: Planning and implementing data platform resources
This module introduces methods for provisioning data platform resources in Azure SQL. You will learn about the options for upgrading and migrating existing SQL databases to Azure. You will learn how to set up Azure resources to host SQL Server on a virtual machine, SQL Managed Instance, and SQL database. You will learn how to determine which options are best based on specific requirements, including High Availability and Disaster Recovery (HADR) requirements. You will learn to calculate resource requirements and understand hybrid approaches.
Lessons
- Provision of IaaS/PaaS solutions with Azure SQL
- Evaluation of strategies for migration to Azure SQL
- Migrate SQL workloads to Azure SQL databases
- Migrate SQL workloads to Azure Managed Instances
Exercise: Deploying SQL Server on an Azure virtual machine
- Exploring the Azure portal
- Deploying a SQL Server on an Azure virtual machine
- Establishing a connection to SQL Server on an Azure virtual machine
Exercise: Deploying an Azure SQL database
- Create a virtual network
- Provide an Azure SQL database
- Establish a connection to an Azure SQL database with Azure Data Studio
- Querying an Azure SQL database with SQL Notebook
Module 3: Implementation of a secure environment for a database service
This module examines the practices for backing up your SQL Server database as well as an Azure SQL database. This includes an overview of the different SQL Server-based options as well as the different Azure options for securing Azure SQL Database. attendees will learn why security is important when working with databases and explain the authentication options for Azure SQL Database.
Lessons
- Configuring database authentication and authorization
- Data protection during transmission and at rest
- Implementation of compliance controls for sensitive data
Exercise: Authorizing access to Azure SQL Database with Azure Active Directory
- Create user
- Manage access to database objects
- Validate access
Exercise: Configuring a server-based firewall rule via the Azure portal
- Configure Azure SQL database firewall rules
- Validate access
Exercise: Enable Microsoft Defender for SQL and data classification
- Activate Microsoft Defender for the Azure SQL database
- Configure data classification for Azure SQL database
Module 4: Monitoring and optimizing operating resources
In this module, you will learn about resource optimization for your databases created with either IaaS or PaaS services. The module also covers server and hardware resource monitoring. It will familiarize you with the different tools available for monitoring performance and creating a baseline. You will learn how to interpret performance metrics for the most important resources. You will also learn how to troubleshoot database performance issues with Azure SQL Insights.
Lessons
- Describing performance monitoring
- Configure SQL Server resources for optimal performance
- Configuring databases for optimal performance
Exercise: Isolate performance problems through monitoring
- Check the CPU utilization in the Azure portal
- Identify queries with high CPU utilization
Exercise: Recognizing and solving fragmentation problems
- Investigate index fragmentation
- Restore fragmented indices
- Validate performance improvements
Module 5: Optimization of query performance
Query execution plans are perhaps the most important aspect of database performance. Improving poor plans is certainly an area where a small amount of effort can yield large improvements. While hardware issues can limit query performance, improving the hardware usually results in performance gains in the 10-20% range at most. More commonly, database administrators encounter unoptimized queries, outdated or missing statistics, missing indexes, or poor database designs that cause the database engine to do more work than necessary to deliver results for a given query. Improving plans can sometimes lead to performance improvements in the range of 100-200% or even more, meaning that a query can run twice or three times as fast after improving a plan with better indexes or statistics! In this module, you will learn how to analyze the performance of individual queries and identify where improvements can be made.
Lessons
- Investigation of the optimization of query performance
- Investigating the performance-based database design
- Evaluate performance improvements
Exercise: Identifying and eliminating blockage problems
- Execute report on blocked queries
- Read Commit Snapshot Activate isolation level
- Evaluate performance improvements
Exercise: Recognizing problems in database design
- Examine the query and identify the problem
- Identify options for rectifying the warning message
- Improve the code
Exercise: Isolate problem areas in poorly performing queries in an SQL database
- Generate the actual execution plan
- Resolve a suboptimal query plan
- Use Query Store to recognize and handle regression
- Check the report on the most resource-intensive queries
- Forcing a better execution plan
- Use query hints to improve performance
Module 6: Automating database tasks
A common goal of database administrators in many environments is to automate as many of their repetitive tasks as possible. This can be as simple as using scripts to automate a backup process or as complex as building a fully automated alerting system. This module provides details on automating tasks to simplify the DBA's work. Methods include scheduling tasks for regular maintenance as well as using Elastic Jobs and Azure Automation Runbooks.
Lessons
- Automate the provision of database resources
- Create and manage SQL agent jobs
- Manage Azure PaaS tasks with the help of automation
Exercise: Deploy Azure SQL database with an Azure Resource Manager template
- Explore the Azure Resource Manager template
Exercise: Creating a CPU status warning for an SQL server
- Create a warning if a CPU exceeds an average of 80 percent
Exercise: Deploy an automation runbook to automatically recreate indexes
- Create an automation account
- Establish a connection to an existing Azure SQL database
- Configure the assets of the automation account
- Create a PowerShell runbook
- Create a schedule for a runbook
Module 7: Planning and implementing a high availability and disaster recovery solution
Data must be available when the company needs it. This means that the solutions where the data is hosted need to be designed with availability and recoverability in mind. Let's say you work for a company that sells widgets both in-store and online. Your main application uses a highly transactional database for orders. What would happen if the server or platform hosting the transactional database was unavailable or inaccessible for some reason? What impact would this have on the business? With the right solution, the database could be brought back online in a reasonable timeframe and with minimal effort, allowing business operations to continue with little to no impact. This module and associated lab covers the configuration, testing and management of a High Availability and Disaster Recovery (HADR) solution in Azure for both Infrastructure-as-a-Service (IaaS) and Platform-as-a-Service (PaaS) deployments. This module covers not only the basic requirements, but also the various options available for HADR.
Lessons
- High availability and disaster recovery strategies
- IaaS/PaaS platform and database tools for HADR
- Backup and restore of databases
Exercise: Configuring georeplication for Azure SQL Database
- Activating georeplication
- Failover to a secondary region
Exercise: Saving to URL and restoring URL
- Create a credential
- Save to URL
- Validate backup via Azure CLI and Storage Explorer
- Restore from the URL
- Plan, deploy and configure Azure SQL offerings
- Monitor database performance and optimize a database and queries to achieve optimal performance
- Planning and configuring a high availability solution
This intensive training prepares you for:
Exam: " DP-300: Administering Microsoft Azure SQL Solutions " for the
certification: " Microsoft Certified: Azure Database Administrator Associate "
This course consists of training training and is led by a trainer who supervises the participants live. Theory and practice are taught with live demonstrations and practical exercises. The video conferencing software Zoom is used.
The target audience for this course is data professionals who manage data and databases and want to learn about managing the data platform technologies available on Microsoft Azure. This course is also valuable for data architects and application developers who need to understand what data platform technologies are available with Azure and how to work with these technologies through applications. Successful Azure database administrators start this role with professional database administration experience and technical knowledge of cloud technologies.
This is especially true in the following cases:
- Work and development with SQL Server and maintenance of SQL Server
- Experience with Azure, e.g. provisioning and managing resources
You should at least be familiar with the information in the following course before attending this course:
- Microsoft Azure Data Fundamentals
.svg)
