DP-300 Exam Study Guide (Administering Relational Databases on MicrosoftAzure)

Dp-300 Administering Relational Databases on Azure certificate Study Guide

Preparing for the DP-300 Administering Relational Databases on Microsoft Azure exam? Don’t know where to start? This post is the DP-300 Certificate Study Guide (with links to each exam objective).

I have curated a list of articles from Microsoft documentation for each objective of the DP-300 exam. Please share the post within your circles so it helps them to prepare for the exam.

DP-300 Administer Relational Databases Course

DP-300 Azure DB Administrator Practice Test

DP-300 Azure Database Administrator e-books

To view other Azure certificate study guides, click here

Full Disclosure: Some of the links in this post are affiliate links. I receive a commission when you purchase through them.

Looking for DP-300 Dumps? Read This!

Using DP-300 exam dumps can get you permanently banned from taking any future Microsoft certificate exam. Read the FAQ page for more information. However, I strongly suggest you validate your understanding with practice questions.

Plan and Implement Data Platform Resources (15-20%)

Deploy Resources by Using Manual Methods

Deploy database offerings on selected platforms

Create an Azure SQL Database single database

Configure customized deployment templates

Create a single database in Azure SQL Database using an ARM template

Apply patches and updates for hybrid and IaaS deployment

Manage updates and patches for your VMs

Patching SQL Azure

Recommend an Appropriate Database Offering Based on Specific Requirements

Evaluate requirements for the deployment

Azure SQL database deployment

Evaluate the functional benefits/impact of possible database offerings

What is Azure SQL?

Evaluate the scalability of the possible database offering

Azure SQL Database – scalability

Evaluate the HA/DR of the possible database offering

High availability for Azure SQL Database & SQL Managed Instance

Business continuity and HADR for SQL Server on Azure VMs

DR strategies using Azure SQL Database elastic pools

Evaluate the security aspects of the possible database offering

Azure SQL Database & SQL Managed Instance

Configure Resources for Scale and Performance

Configure Azure SQL database/elastic pools for scale and performance

Scaling out with Azure SQL Database

Scale elastic pool resources in Azure SQL Database

Tune performance for Azure SQL Database/MI

Configure Azure SQL managed instances for scale and performance

What is Azure SQL Managed Instance?

Dynamically scale resources with minimal downtime

Performance improvements for Managed Instance

Configure SQL Server in Azure VMs for scale and performance

Performance guidelines for SQL Server on Azure VMs

Calculate resource requirements

Storage & SQL Server capacity planning & configuration

Evaluate database partitioning techniques, such as database sharding

Sharding pattern

Set up SQL Data Sync

Set up SQL Data Sync between DBs in Azure SQL Database & SQL Server

Evaluate a Strategy for Moving to Azure

Evaluate requirements for the migration

Azure Database Migration Service – Prerequisites

Evaluate offline or online migration strategies

Migrate SQL Server to Azure SQL Database

Evaluate requirements for the upgrade

Pre upgrade Checklist

Evaluate offline or online upgrade strategies

Using active geo-replication in Azure SQL Database

dp-300 Exam details & Tips

Implement a Migration or Upgrade Strategy for Moving to Azure

Implement an online migration strategy

Using Azure Database Migration Service to migrate the databases

Implement an offline migration strategy

Migrate SQL Server to Azure SQL Database offline using DMS

Implement an online upgrade strategy

Azure SQL Database active geo-replication

Implement an offline upgrade strategy

Upgrade to a Different Edition of SQL Server

DP-300

Amazon link (affiliate)

Implement a Secure Environment (15-20%)

Configure Database Authentication by Using Platform and Database Tools

Azure certification Frequently Asked Questions

Configure database authorization by using platform and database tools

Configure database and object-level permissions using graphical tools

Getting Started with Database Engine Permissions

Apply the principle of least privilege for all securable

Authorization & Permissions in SQL Server

Implement Security for Data at Rest

Implement Transparent Data Encryption (TDE)

TDE for SQL Database, SQL Managed Instance & Synapse Analytics

Implement object-level encryption

Configure Always Encrypted by using Azure Key Vault

Implement Dynamic Data Masking

Implement dynamic data masking with the Azure portal

Implement Azure Key Vault and disk encryption for Azure VMs

Create & encrypt a Windows VM with the Azure portal

Implement Security for Data in Transit

Implement Compliance Controls for Sensitive Data

Monitor and Optimize Operational Resources (15-20%)

Monitor Activity and Performance

Prepare an operational performance baseline

Establish a Performance Baseline

Determine sources for performance metrics

Sources of monitoring data for Azure Monitor

Interpret performance metrics

Query Performance Insight for Azure SQL Database

Monitoring in Azure SQL Database & Azure SQL Managed Instance

Assess database performance by using Azure SQL Database Intelligent Performance

Intelligent Insights using AI to monitor & troubleshoot databases

Configure and monitor activity and performance at the infrastructure, server, service, and database levels

Monitoring & performance tuning in Azure SQL DB/MI

Microsoft Azure database monitoring playbook

Monitor Azure SQL DB/MI performance using DMVs

Implement Performance-related Maintenance Tasks

Identify Performance-related Issues

Configure Query Store to collect performance data

Query Performance Insight for Azure SQL Database

Identify sessions that cause blocking

Finding Blocking Queries in SQL Azure

Assess growth/fragmentation of databases and logs

SQL Server Database Growth & Shrink Events

Resolve index fragmentation by reorganizing or rebuilding indexes

Assess performance-related database configuration parameters

Including AutoClose, AutoShrink, AutoGrowth

Set the AUTO_CLOSE Database Option to OFF

Autogrow & Autoshrink settings in SQL Server

Configure Resources for Optimal Performance

Configure storage and infrastructure resources

Optimize IOPS, throughput, and latency

Storage configuration for SQL Server VMs

Optimize tempdb performance

Learn about the tempdb database

Optimize data and log files for performance

Mapping data flow performance & tuning guide

Manage the size of the transaction log file

Configure server and service account settings for performance

Determining proper SQL Server configuration settings

Configure Resource Governor for performance

Resource governance in Azure SQL Database

Configure a User Database for Optimal Performance

Implement database-scoped configuration

Alter database scoped configuration (Transact-SQL)

Configure compute resources for scaling

Manage to compute in Azure Synapse Analytics data warehouse

Configure Intelligent Query Processing (IQP)

Intelligent query processing in SQL databases

Optimize Query Performance (5-10%)

Review Query Plans

Determine the appropriate type of execution plan

Live Query Statistics, Actual Execution Plan, Estimated Execution Plan

Live query statistics

Display an actual execution plan

Display the estimated execution plan

Identify problem areas in execution plans

Analyzing actual graphical execution plans

Identifying and fixing performance problems using execution plans

Extract query plans from the Query Store

sys.query_store_plan (Transact-SQL)

Evaluate Performance Improvements

Determine the appropriate Dynamic Management Views (DMVs) to gather query performance information

Monitoring query performance with DMVs

Identify performance issues using DMVs

Monitor Azure SQL DB/MI performance using DMVs

Identify and implement index changes for queries

SQL Server index architecture & design guide

Recommend query construct modifications based on resource usage

Monitoring performance by using the Query Store

Assess the use of hints for query performance

Query hints: Transact-SQL

Query tuning and hinting

Review Database Table and Index Design

Identify data quality issues with duplication of data

Data Quality Services (DQS) data matching process

Identify the normal form of database tables

Description of the database normalization basics

Assess index design for performance

Improving the performance of Azure SQL Databases with Index Advisor

SQL Server index architecture & design guide

Validate data types defined for columns

Data types (Transact-SQL)

SQL statement to get column type

Recommend table and index storage including filegroups

Recommendations when working with files & filegroups

Evaluate table partitioning strategy

Horizontal, vertical & functional data partitioning

Evaluate the use of compression for tables and indexes

sp_estimate_data_compression_savings (Transact-SQL)

Perform Automation of Tasks (10-15%)

Create Scheduled Tasks

Manage schedules for regular maintenance jobs

Automate management tasks using database jobs

Configure multi-server automation

Automated Administration Across an Enterprise

Configure notifications for task success/failure/non-completion

Notify an Operator of Job Status

Evaluate and Implement an Alert and Notification Strategy

Create event notifications based on metrics

Set up Azure Database for MySQL alerts using the Azure portal

Create event notifications for Azure resources

Create event notification (Transact-SQL)

Create alerts for server configuration changes

Capturing & Alerting on SQL Server Configuration Changes

Create tasks that respond to event notifications

Respond to events with Azure Monitor Alerts

Manage and Automate Tasks in Azure

Plan and Implement a High Availability and Disaster Recovery (HADR) Environment (15-20%)

Recommend an HADR Strategy for a Data Platform Solution

Test a HADR Strategy by Using Platform, OS and, Database Tools

Perform Backup and Restore a Database by Using Database Tools

Perform a database backup with options

Back up database

Perform a database restore with options

Restore Statements (Transact-SQL)

Perform a database restore to a point in time

Azure SQL database point in Time Restore

Restore a database to a point in time in SQL Server

Configure long-term backup retention

Manage Azure SQL Database long-term backup retention

Configure HA/DR by Using OS, Platform, and Database Tools

Configure replication

Replication to Azure SQL Database

Create an Availability Group

Configure a SQL Server availability group on Azure VMs

Integrate a database into an Availability Group

Add a database to an Always On availability group

Configure quorum options for a Windows Server Failover Cluster

Configure and manage quorum

Configure an Availability Group listener

Configure a listener for an Always On availability group

Perform Administration by Using T-SQL (10-15%)

Examine System Health

Evaluate database health using DMVs

Monitor Azure SQL Database & SQL Managed Instance using DMs

Evaluate server health using DMVs

System Dynamic Management Views

Perform database consistency checks by using DBCC

DBCC CHECKDB (Transact-SQL)

Monitor Database Configuration by Using T-SQL

Assess proper database Autogrowth configuration

SQL server database Growth & Autogrowth settings

Report on database free space

Display data & log space information for a database

Review database configuration options

Change the configuration settings for a database

Perform Backup and Restore a Database by Using T-SQL

Prepare databases for Always On Availability Groups

Prepare a database for an Always On availability group

Perform transaction log backup

Back up a Transaction Log

Perform restore of user databases

Restore a database backup using SSMS

Perform database backups with options

Additional considerations about backup options

Manage Authentication by Using T-SQL

Manage Authorization by Using T-SQL

Configure permissions for users to access database objects

Permissions in the Database Engine

Configure permissions by using custom roles

Create a new database role (Transact-SQL)

This brings us to the end of DP-300 Administering Relational Databases on Microsoft Azure Study Guide

What do you think? Let me know in the comments section if I have missed out on anything. Also, I love to hear from you about how your preparation is going on!

In case you are looking for other Azure certification exams check out this page

Follow/Like ravikirans.com to Receive Updates

Sign up for Newsletter

Want to be notified as soon as I post? Subscribe to RSS feed / leave your email address in the subscribe section. Share the article to your social networks with the below links so it can benefit others.

Share the Article in Your Social Media Networks

  • 1
  • 1
  •  
  •  
  •  
    2
    Shares

You may also like

16 Comments

  1. did anyone took the exam after live?

    may i know how was exam?

    how long it took to get the results?

    how much is the pass percentage ?

    how many questions in the exam?

  2. Thanks for this article. I am about to start preparing for this exam. if you have completed this already, is this exam is like multiple choice questions or we might have to write any sql or procs?

  3. Hey Ravi, thanks for the blog. It helped in my preparation. One quick question – when do we get results? It says few weeks if you pass and then 2 months to get score card after I end my exam.

  4. In the udemy course, it says that you should have prior experience of working on an on-premises SQL Server and you know PowerShell and C#. I have some experience with SQL Server but haven’t used PowerShell or C#. Any tips? Do I need to know how to use those for the exam?

    1. Hello Heba,

      Whenever you work with Azure, it is best to equip yourself with Azure. They are not difficult and is not meant to be memorized. Copy-paste a few commands and run them, and you will unearth the structure of them.
      Good luck

Leave a Reply

Your email address will not be published. Required fields are marked *