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

Dp-300 Administering Relational Databases on Azure certificate Study Guide

DP-300 Preparation Details

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.

Exam Voucher for DP-300 with 1 Retake

Get 40% OFF with the combo

DP-300 Administer Relational Databases Course

Pluralsight Azure SQL Database for the SQL Server DBA
LinkedIn Learning (Free trial) Administer a SQL Database Infrastructure
Udemy Professional Azure SQL DB Administration

DP-300 Azure DB Administrator Practice Test

Whizlabs Exam Questions Azure Relational Test (110 Questions)
Udemy Practice Tests Azure Relational Databases Questions

DP-300 Azure Database Administrator e-books

Amazon e-book (PDF) Administering a SQL Database Infrastructure
Amazon e-book (PDF) Exam Reference: Provisioning SQL Databases

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.

Check out all the other Azure certificate study guides

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

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 Instance 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 and 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

Professional Azure SQL Database Administration DP-300

Amazon link (affiliate)

Implement a Secure Environment (15-20%)

Configure Database Authentication by Using Platform and Database Tools

Configure Azure AD authentication

Configure & manage Azure AD authentication with Azure SQL

Create users from Azure AD identities

Create contained users mapped to Azure AD identities

Configure security principals

Azure AD Service Principal authentication to SQL DB – Code Sample

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

Configure server and database-level firewall rules

Create a server-level IP firewall rule

Database-level IP firewall rules

Server-level versus database-level IP firewall rules

Implement Always Encrypted

Configure Always Encrypted by using the Windows certificate store

Implement Compliance Controls for Sensitive Data

Apply a data classification strategy

What is data classification?

Implement Compliance Controls for Sensitive Data

Configure server and database audits

Auditing for Azure SQL Database & Azure Synapse Analytics

A Boost in Security for Azure SQL Database: Auditing

Implement data change tracking

Enable and Disable Change Tracking

Perform vulnerability assessment

SQL Vulnerability Assessment: Identify database vulnerabilities

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

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

Implement index maintenance tasks

Automating Azure SQL Database maintenance tasks

Implement statistics maintenance tasks

Update Statistics Task

Configure database auto-tuning

Implement Performance-related Maintenance Tasks

Automate database maintenance tasks

Automate SQL DB index & statistics maintenance with Azure Automation

Automating Azure SQL Database maintenance tasks

Azure SQL agent jobs, Azure automation, SQL Server agent jobs

SQL Agent Jobs

Azure Automation: Your SQL Agent in the Cloud

Creating a SQL Server Agent job in SQL Server

Manage storage capacity

Manage file space for databases in Azure SQL Database

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

Perform automated deployment methods for resources

Azure SQL database deployment

Automate Backups

Automated backups: Azure SQL Database & SQL Managed Instance

Automate performance tuning and patching

Automatic tuning: Azure SQL Database & SQL Managed Instance

Automated Patching for SQL Server on Azure VMs

Implement policies by using automated evaluation modes

Administer servers by using Policy-Based Management

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

Recommend an HADR Strategy for a Data Platform Solution

Recommend HADR strategy based on RPO/RTO requirements

Understanding & leveraging Azure SQL Database’s SLA

Evaluate HADR for hybrid deployments

Hybrid IT: Disaster recovery solutions

Evaluate Azure-specific HADR solutions

Azure only: Disaster recovery solutions

Identify resources for HADR solutions

The Always On availability groups feature

Always On Failover Cluster Instances (SQL Server)

About Log Shipping (SQL Server)

SQL Server backup & restore with Microsoft Azure Blob storage service

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

Test HA by using failover

Configure a failover group for Azure SQL Database

Test DR by using failover or restore

Restore your Azure SQL Database or failover to a secondary

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 Always On Availability Group

Configure a SQL Server availability group on Azure VMs

Integrate a database into an Always On 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 Always On Availability Group listener

Configure a listener for an Always On availability group

Configure failover cluster instances on Azure VMs

Failover cluster instances with SQL Server on Azure VMs

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 certificates

Using certificates in Azure SQL database: Import

Manage security principals

Security in Azure SQL Managed Instance using Azure AD server principals

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 the 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 preparing for other Azure certification exams, check out the Azure study guide for those exams.

Follow Me to Receive Updates on DP-300 Exam


Want to be notified as soon as I post? Subscribe to the 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 DP-300 Study Guide in Your Network

You may also like

18 Comments

  1. Hi Ravi. Thanks for this article. Can you please suggest THE BEST online course to prepare for DP-300?

    1. Hi Kishore, There is a DP-300 course only in Whizlabs as of now, so not many options

  2. 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?

  3. 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?

  4. 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.

  5. 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