Ahmad Yaseen

How to prepare for the Exam DP-300: Administering Relational Databases on Microsoft Azure

September 14, 2020 by

In this article, we will show how to prepare yourself for the DP-300 exam: Administering Relational Databases on the Microsoft Azure certificate exam.

Exam Overview

The Administering Relational Databases on Microsoft Azure certificate exam measures your intermediate-level knowledge on seven main areas. This includes:

  • How to plan and implement data platform resources, with relative questions weight in the exam up to 20%
  • How to implement a secure environment, with relative questions weight in the exam up to 20%
  • How to monitor and optimize operational resources, with relative questions weight in the exam up to 20%
  • How to optimize query performance, with relative questions weight in the exam up to 10%
  • How to perform automation of tasks, with relative questions weight in the exam up to 15%
  • How to plan and implement a High Availability and Disaster Recovery (HADR) environment, with relative questions weight in the exam up to 20%
  • How to perform administration by using T-SQL, with relative questions weight in the exam up to 15%

Before starting this course, it is recommended to have basic knowledge in:

  • Operating system and virtualization concepts, such as Virtual Machines, virtual networking, and virtual hard disks
  • The SQL Server Network configurations, such as TCP/IP, DNS, virtual, VPNs, firewalls, and in-transit encryption
  • SQL Server database creation, management, and configuration
  • Performing different administration tasks and querying using T-SQL language
  • SQL Server table and index types and structures

With no official prerequisites for this exam, it is recommended, but not mandatory, to take the Microsoft Azure Fundamentals (AZ-900) exam if you are very new to Microsoft Azure world, and taking the Microsoft Azure Data Fundamentals (DP-900) if you are new to all Microsoft Azure data platform.

You can easily schedule the exam from the Administering Relational Databases on Microsoft Azure certificate page.

Certificate Candidate

With most of the large companies moving their data to the cloud, it is the best time for the database administrators to validate their knowledge in the Microsoft Azure data platform.

The Administering Relational Databases on Microsoft Azure exam is designed as an intermediate level exam, for the relational database administrators and data management specialists who are interested in starting their journey in administrating the relational databases that are hosted in Microsoft Azure.

To become an Azure Database Administrator, you need to have the proper skills to implement and manage the different aspects of the data platform solutions that are built on Microsoft Azure data services and the on-premises Microsoft SQL Server, including the databases availability, security, performance monitoring and optimization.

Study Guideline

In order to prepare yourself for the Administering Relational Databases on Microsoft Azure exam, you can go through the 7-module Administering Relational Databases on Microsoft Azure Learning Path self-study course provided by Microsoft that helps you in getting the basic knowledge required to pass that exam.

If you are not interested in reading the pages and prefer to listen, you can subscribe to any online course such as Udemy or any other training provided by training sites and centers.

Take into consideration that this exam contains a large number of subjects. In order to pass the exam, you need to have enough knowledge in each subject, without going very deep in each subject. For me, I prefer to be fully prepared for the certificates exams and gain all the required knowledge in order to be able to provide training in the courses I am certified in and apply these skills in my customers’ sites. So, I will list all measured skills in this course and the official resource to study that subject.

Plan and Implement Data Platform Resources

In this module, you will be measured in the skills below:

Implement a Secure Environment

In this module, you will be measured in the skills below:

Monitor and Optimize Operational Resources

In this module, you will be measured in the skills below:

Optimize Query Performance

In this module, you will be measured in the skills below:

Perform Automation of Tasks

In this module, you will be measured in the skills below:

Plan and Implement a High Availability and Disaster Recovery (HADR) Environment

In this module, you will be measured in the skills below:

Perform Administration by Using T-SQL

In this module, you will be measured in the skills below:

Practicing

As any exam, after completing the study material, you need to make sure that you are prepared well for the exam. You can search on the internet for any free practice tests, such as the ExamTopics site or any other free test, but after making sure that you have completed studying the official course outline.

In this article, I will provide some review questions that I usually use to measure my trainees general skills, to make sure that they are ready for the Administering Relational Databases on Microsoft Azure exam, taking into consideration that most of the exam questions are scenario-related questions in which you are requested to apply what you learn in these issues.

  1. An Azure SQL Database Managed Instance can be categorized as a _______cloud service

    Platform as a Service (PAAS)

  2. The compatibility level settings can be changed from:

    The individual database properties page

  3. The storage type that offers the lowest latency in Azure:

    Ultra Disk

  4. The migration tool that can be used to assess and migrate your databases from an on-premises SQL Server to an Azure VM:

    Data Migration Assistant

  5. To reduce the cost of an Azure SQL Server VM that will run full time for 3 years, you should use:

    Azure Reserved VM Instances

  6. In order to migrate a set of databases that use distributed transactions from on-premises SQL Server, the target database platform that you will use in Microsoft Azure is:

    Azure SQL Managed Instance

  7. -The best option to host your new cloud database that you expect to grow to 50 TB, is:

    Azure SQL Database Hyperscale

  8. The best option to host your new database in the cloud for testing purposes that will be used less than 8 hours a day and is expected to be 20 GB in size, is

    Azure SQL Database Serverless

  9. How could you upgrade a major version of the Azure Database for MySQL?

    Create a dump and restore it to a server at the higher version

  10. The protocol that is used by Azure Active Directory for Authorization?

    OAuth

  11. The system database that stores the information about logins in SQL Server is:

    master

  12. The role that allows users to create users within a database, is:

    db_securityadmin

  13. The permission that allows the user to perform any option against a database object, is:

    Control

  14. The feature that allows a user to execute a stored procedure even if he does not have permission to access the tables referenced in the stored procedure, is called:

    Ownership chaining

  15. The security object that is required in order to enable transparent data encryption, is:

    Master Key

  16. The feature that prevents members of the sysadmin role from viewing the values of data in a table, is called:

    Always Encrypted

  17. The feature that provides a private IP address for an Azure SQL Database, is called:

    Private Link

  18. The technique that is used to create database firewall rules in Azure SQL Database:

    Executing a T-SQL statement

  19. An example of the threats that can be analyzed by Advanced Threat Protection, that is commonly associated with dynamic SQ, called:

    SQL Injection

  20. The Performance Monitor counter that reflects how long SQL Server expects to retain data in memory, is:

    Page Life Expectancy

  21. In order to see the sizes of your SQL Server Databases running in an Azure VM, you should use:

    The SQL VM Resource Provider

  22. The isolation level that should be used if you want to prevent users from reading data from blocking users writing data, is:

    Read Committed Snapshot Isolation

  23. The DMV that shows sessions holding locks:

    Sys.dm_tran_locks

  24. The Query Store catalog view that provides the Query ID to allow for query tracking:

    Sys.query_store_queries

  25. The storage type that should be used in conjunction with Azure VMs for SQL Server data files?

    Disk Storage

  26. The intelligent query processing feature that allows for faster calculations of a large number of rows, called:

    Batch Mode on Row Store

  27. The component of resource governor that allows you to configure limits on system resources, is called:

    Resource Pools

  28. The Microsoft Azure data platform that supports automatic index management:

    Azure SQL Database

  29. The DVM that can be used to show the status of a plan updated by automatic tuning:

    sys.dm_db_tuning_recommendations

  30. The type of execution plan that is stored in the plan cache:

    Estimated execution plan

  31. The DMV that should be used to find the index utilization:

    sys.dm_db_index_usage_stats

  32. The database design type that should be used for a data warehouse when you want to reduce the data volume of your dimensions?

    Snowflake schema

  33. The compression type that offers the highest level of compression is:

    Columnstore Archival

  34. The type of index that is best used on a data warehouse fact table:

    Clustered Columnstore

  35. The DMV that can be used to capture the last Actual Execution Plan for a given cached query plan:

    sys.dm_exec_query_plan_stats

  36. In order to pass the region for a resource group in the deployment template, we should include _____ in the ARM template

    Parameter

  37. In order to configure the SQL Server Agent can send e-mail, we should first configure:

    A mail profile

  38. The index maintenance operation that recalculates the statistics on an index:

    Rebuild

  39. The Extended Events target that writes only to memory and is not persisted:

    Ring Buffer

  40. The unit of execution for the Azure Automation Account is called:

    Runbook

  41. The SQL Elastic Job scope is configured as:

    Target Group

  42. RPO can be defined as:

    The point to which data needs to be recovered after a failure.

  43. The Microsoft Azure component that needs to be configured for the listener in an AG to work properly:

    A load balancer

  44. The WSFC in Microsoft Azure for AGs and FCIs can be created using:

    PowerShell

  45. The Microsoft Azure feature that allows you to test disaster recovery without bringing down your production system:

    Azure Site Recovery

Good luck!

Table of contents

It is time to specify your Microsoft Certifications path
Data Engineer Interview Questions and Answers: SQL Workload Migration to Microsoft Azure Database Platforms
How to prepare for the Exam AZ-900: Microsoft Azure Fundamentals
How to prepare for the Exam DP-300: Administering Relational Databases on Microsoft Azure
How to prepare for the Exam DP-200: Implementing an Azure Data Solution
How to prepare for the Exam DP-201: Designing an Azure Data Solution

Ahmad Yaseen
564 Views