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

Dp-300 Administering Relational Databases on Azure Study Guide

Preparing for the DP-300 Administering Relational Databases on 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.

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

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-single-database-get-started

Configure customized deployment templates

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-single-database-get-started-template

Apply patches and updates for hybrid and IaaS deployment

https://docs.microsoft.com/en-us/azure/automation/automation-tutorial-update-management

https://azure.microsoft.com/en-in/blog/patching-sql-azure/

 

Recommend an appropriate database offering based on specific requirements

Evaluate requirements for the deployment

https://docs.microsoft.com/en-us/azure/devops/pipelines/targets/azure-sqldb

Evaluate the functional benefits/impact of possible database offerings

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-paas-vs-sql-server-iaas

Evaluate the scalability of the possible database offering

https://techcommunity.microsoft.com/azure-sql-database/azure-sql-database-scalability

Evaluate the HA/DR of the possible database offering

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-high-availability

https://docs.microsoft.com/en-us/virtual-machines/windows/sql/virtual-machines-windows-sql-high-availability-dr

https://docs.microsoft.com/en-us/azure/sql-database-disaster-recovery-strategies-for-applications-with-elastic-pool

Evaluate the security aspects of the possible database offering

https://docs.microsoft.com/en-us/azure/sql-database-security-overview

 

Configure resources for scale and performance

Configure Azure SQL database/elastic pools for scale and performance

https://docs.microsoft.com/en-us/azure/sql-database-elastic-scale-introduction

https://docs.microsoft.com/en-us/azure/sql-database-elastic-pool-scale

https://docs.microsoft.com/en-us/azure/sql-database-performance-guidance

Configure Azure SQL managed instances for scale and performance

Scaling: https://docs.microsoft.com/en-us/azure/sql-database-managed-instance#managed-instance-tiers

Performance: https://docs.microsoft.com/en-us/azure/sql-database-managed-instance#managed-instance-tiers

Configure SQL Server in Azure VMs for scale and performance

https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-performance

Calculate resource requirements

https://docs.microsoft.com/en-us/sharepoint/administration/storage-and-sql-server-capacity-planning-configuration

Evaluate database partitioning techniques, such as database sharding

https://docs.microsoft.com/en-us/azure/architecture/patterns/sharding

 

Evaluate a strategy for moving to Azure

Evaluate requirements for the migration

https://docs.microsoft.com/en-us/azure/dms/pre-reqs

Evaluate offline or online migration strategies

https://datamigration.microsoft.com/scenario/sql-to-azuresqldb

Evaluate requirements for the upgrade

https://docs.microsoft.com/en-us/sql/database-engine/install-windows/supported-version-and-edition-upgrades#pre-upgrade-checklist

Evaluate offline or online upgrade strategies

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-manage-application-rolling-upgrade

 

Implement a migration or upgrade strategy for moving to Azure

Implement an online migration strategy

https://docs.microsoft.com/en-us/azure/dms/tutorial-sql-server-azure-sql-online

Implement an offline migration strategy

https://docs.microsoft.com/en-us/azure/dms/tutorial-sql-server-to-azure-sql

Implement an online upgrade strategy

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-manage-application-rolling-upgrade

Implement an offline upgrade strategy

https://docs.microsoft.com/en-us/sql/database-engine/install-windows/upgrade-to-a-different-edition-of-sql-server-setup

DP-300

Amazon link (affiliate)

Implement a Secure Environment (15-20%)

Configure database authentication by using platform and database tools

Configure Azure AD authentication

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-aad-authentication-configure

Create users from Azure AD identities

https://docs.microsoft.com/en-us/azure/sql-database-aad-authentication-configure#create-database-users-mapped-azure-ad-identities

Configure security principals

https://techcommunity.microsoft.com/azure-ad-service-principal-authentication-sql-db-code-sample

 

Configure database authorization by using platform and database tools

Configure database and object-level permissions using graphical tools

https://docs.microsoft.com/en-us/sql/authentication-access/getting-started-with-database-engine-permissions

Apply the principle of least privilege for all securables

https://docs.microsoft.com/en-us/sql/authorization-and-permissions-in-sql-server

 

Implement security for data at rest

Implement Transparent Data Encryption (TDE)

https://docs.microsoft.com/en-us/sql-database/transparent-data-encryption-azure-sql

Implement object-level encryption

https://docs.microsoft.com/en-us/sql-database/sql-database-always-encrypted-azure-key-vault

Implement Dynamic Data Masking

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-dynamic-data-masking-get-started-portal

Implement Azure Key Vault and disk encryption for Azure VMs

https://docs.microsoft.com/en-us/azure/virtual-machines/windows/disk-encryption-portal-quickstart

 

Implement security for data in transit

Configure SQL DB and database-level firewall rules

https://docs.microsoft.com/en-us/sql/system-stored-procedures/sp-set-database-firewall-rule

Implement Always Encrypted

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-always-encrypted

Configure Azure Data Gateway

https://docs.microsoft.com/en-us/azure/analysis-services/analysis-services-gateway-install

 

Implement compliance controls for sensitive data

Apply a data classification strategy

https://docs.microsoft.com/bs-latn-ba/azure/cloud-adoption-framework/govern/policy-compliance/data-classification

https://download.microsoft.com/download/Data-Classification-for-Cloud-Readiness.pdf

Configure server and database audits

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-auditing

https://azure.microsoft.com/en-in/blog/a-boost-in-security-for-azure-sql-database-auditing/

Implement data change tracking

https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/enable-and-disable-change-tracking-sql-server

Perform vulnerability assessment

https://docs.microsoft.com/en-us/azure/sql-database/sql-vulnerability-assessment

DP-300

Amazon link (affiliate)

Monitor and Optimize Operational Resources (15-20%)

Monitor activity and performance

Prepare an operational performance baseline

https://docs.microsoft.com/en-us/sql/relational-databases/performance/establish-a-performance-baseline

Determine sources for performance metrics

https://docs.microsoft.com/en-us/azure/azure-monitor/platform/data-sources

Interpret performance metrics

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-query-performance

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-monitor-tune-overview#sql-database-resource-monitoring

Assess database performance by using Azure SQL Database Intelligent Performance

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-intelligent-insights

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

https://docs.microsoft.com/bs-latn-ba/azure/sql-database/sql-database-monitor-tune-overview

 

Implement performance-related maintenance tasks

Implement index maintenance tasks

https://medium.com/@segovoni/automating-azure-sql-database-maintenance-tasks-overview

Implement statistics maintenance tasks

https://docs.microsoft.com/en-us/sql/relational-databases/maintenance-plans/update-statistics-task-maintenance-plan

Configure database auto-tuning

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-automatic-tuning-enable

Automate database maintenance tasks

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

SQL Agent Jobs: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-job-automation-overview#sql-agent-jobs

Azure Automation: https://azure.microsoft.com/en-us/blog/azure-automation-your-sql-agent-in-the-cloud/

SQL Server Agent Jobs: https://docs.microsoft.com/en-us/sql/ssms/agent/create-a-job

Manage storage capacity

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-file-space-management

 

Identify performance-related issues

Configure Query Store to collect performance data

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-query-performance

Identify sessions that cause blocking

https://azure.microsoft.com/en-in/blog/finding-blocking-queries-in-sql-azure/

Assess growth/fragmentation of databases and logs

https://www.sqlshack.com/get-details-of-sql-server-database-growth-and-shrink-events/

https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes

Assess performance-related database configuration parameters

o Including AutoClose, AutoShrink, AutoGrowth

https://docs.microsoft.com/en-us/sql/policy-management/set-the-auto-close-database-option-off

https://support.microsoft.com/en-us/help/considerations-for-the-autogrow-and-autoshrink-settings-in-sql-server

 

Configure resources for optimal performance

Configure storage and infrastructure resources

o Optimize IOPS, throughput, and latency

https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-server-storage-configuration

o Optimize tempdb performance

https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database

o Optimize data and log files for performance

https://docs.microsoft.com/en-us/azure/concepts-data-flow-performance#optimizing-for-azure-sql-database

https://docs.microsoft.com/en-us/sql/relational-databases/logs/manage-the-size-of-the-transaction-log-file

Configure server and service account settings for performance

https://support.microsoft.com/en-in/help/how-to-determine-proper-sql-server-configuration-settings

Configure Resource Governor for performance

https://azure.microsoft.com/en-in/blog/resource-governance-in-azure-sql-database/

DP-300

Amazon link (affiliate)

Configure a user database for optimal performance

Implement database-scoped configuration

https://docs.microsoft.com/en-us/statements/alter-database-scoped-configuration-transact-sql

Configure compute resources for scaling

https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse-manage-compute-overview

Configure Intelligent Query Processing (IQP)

https://docs.microsoft.com/en-us/relational-databases/performance/intelligent-query-processing

 

Optimize Query Performance (5-10%)

Review query plans

Determine the appropriate type of execution plan

o Live Query Statistics, Actual Execution Plan, Estimated Execution Plan, Showplan

https://docs.microsoft.com/en-us/performance/live-query-statistics

https://docs.microsoft.com/en-us/performance/display-an-actual-execution-plan

https://docs.microsoft.com/en-us/performance/display-the-estimated-execution-plan

https://docs.microsoft.com/en-us/sql-server-profiler/analyze-queries-with-showplan-results-sql-server-profiler

Identify problem areas in execution plans

https://docs.microsoft.com/en-us/performance/analyze-an-actual-execution-plan

https://www.youtube.com/watch?v=pdAaO4w9mxA

Extract query plans from the Query Store

https://docs.microsoft.com/en-us/system-catalog-views/sys-query-store-plan-transact-sql

 

Evaluate performance improvements

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

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-monitoring-with-dmvs

Identify performance issues using DMVs

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-monitoring-with-dmvs

Identify and implement index changes for queries

https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-index-design-guide

Recommend query construct modifications based on resource usage

https://docs.microsoft.com/en-us/performance/monitoring-performance-by-using-the-query-store

Assess the use of hints for query performance

https://docs.microsoft.com/en-us/t-sql/queries/hints-transact-sql-query

https://logicalread.com/sql-server-hints-improve-performance-mc11/

 

Review the database table and index design

Identify data quality issues with duplication of data

https://docs.microsoft.com/en-us/sql/data-quality-services/data-matching

Identify the normal form of database

https://docs.microsoft.com/en-us/office/troubleshoot/access/database-normalization-description

Assess index design for performance

https://azure.microsoft.com/en-in/blog/optimize-database-performance-using-index-advisor/

https://docs.microsoft.com/en-us/sql/relational-databases/sql-index-design-guide

Validate data types defined for columns

https://docs.microsoft.com/en-us/t-sql/data-types/data-types-transact-sql

https://stackoverflow.com/questions/sql-statement-to-get-column-type

Recommend table and index storage including filegroups

https://docs.microsoft.com/en-us/relational-databases/databases/database-files-and-filegroups#Recommendations

Evaluate table partitioning strategy

https://docs.microsoft.com/en-us/azure/architecture/best-practices/data-partitioning

Evaluate the use of compression for tables and indexes

https://docs.microsoft.com/en-us/relational-databases/stored-procedures/estimate-data-compression-savings

DP-300

Amazon link (affiliate)

Perform Automation of Tasks (10-15%)

Create scheduled tasks

Manage schedules for regular maintenance jobs

https://docs.microsoft.com/en-us/azure/sql-database-job-automation-overview

Configure multi-server automation

https://docs.microsoft.com/en-us/agent/automated-administration-across-an-enterprise

Configure notifications for task success/failure/non-completion

https://docs.microsoft.com/en-us/agent/notify-an-operator-of-job-status

 

Evaluate and implement an alert and notification strategy

Create event notifications based on metrics

https://docs.microsoft.com/en-us/azure/howto-alert-on-metric

Create event notifications for Azure resources

https://docs.microsoft.com/en-us/t-sql/statements/create-event-notification-transact-sql

Create alerts for server configuration changes

https://www.mssqltips.com/sqlservertip/capturing-and-alerting-on-sql-server-configuration-changes/

Create tasks that respond to event notifications

https://docs.microsoft.com/en-us/azure/azure-monitor/learn/tutorial-response

 

Manage and automate tasks in Azure

Perform automated deployment methods for resources

https://docs.microsoft.com/en-us/azure/devops/pipelines/targets/azure-sqldb

Automate Backups

https://docs.microsoft.com/en-us/azure/sql-database-automated-backups

Automate performance tuning and patching

https://docs.microsoft.com/en-us/azure/sql-database-automatic-tuning

https://docs.microsoft.com/en-us/azure/virtual-machines/virtual-machines-windows-sql-automated-patching

Implement policies by using automated evaluation modes

https://docs.microsoft.com/en-us/policy-based-management/administer-servers-using-policy-based-management

Perform backup and restore a database by using database tools

Perform a database backup with options

https://docs.microsoft.com/en-us/sql/backup-restore/back-up-database-backup-options-page

Perform a database restore with options

https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql

Perform a database restore to a point in time

https://azure.microsoft.com/en-in/blog/azure-sql-database-point-in-time-restore/

https://docs.microsoft.com/en-us/restore-a-sql-server-database-to-a-point-in-time-full-recovery-model

Configure long-term backup retention

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-long-term-backup-retention-configure

 

Configure DR by using platform and database tools

Configure replication

https://docs.microsoft.com/en-us/azure/sql-database/replication-to-sql-database

Configure Azure Site Recovery for a database offering

https://docs.microsoft.com/en-us/azure/site-recovery/site-recovery-sql

 

Configure HA using platform, OS and database tools

Create an Availability Group

https://docs.microsoft.com/en-us/azure/virtual-machines-windows-portal-sql-availability-group-tutorial

Integrate a database into an Availability Group

https://docs.microsoft.com/en-us/availability-groups/windows/availability-group-add-a-database

Configure quorum options for a Windows Server Failover Cluster

https://docs.microsoft.com/en-us/windows-server/failover-clustering/manage-cluster-quorum

Configure an Availability Group listener

https://docs.microsoft.com/en-us/sql/availability-groups/create-or-configure-an-availability-group-listener-sql-server

Check the FAQs on Azure Certification

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

Examine system health

Evaluate database health using DMVs

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-monitoring-with-dmvs

Evaluate server health using DMVs

https://docs.microsoft.com/en-us/system-dynamic-management-views/system-dynamic-management-views

Perform database consistency checks by using DBCC

https://docs.microsoft.com/en-us/t-sql/database-console-commands/dbcc-checkdb-transact-sql

 

Monitor database configuration by using T-SQL

Assess proper database Autogrowth configuration

https://www.red-gate.com/simple-talk/sql-server-database-growth-and-autogrowth-settings/

Report on database free space

https://docs.microsoft.com/en-us/databases/display-data-and-log-space-information-for-a-database

Review database configuration options

https://docs.microsoft.com/en-us/relational-databases/databases/change-the-configuration-settings-for-a-database

 

Perform backup and restore a database by using T-SQL

Prepare databases for Always On Availability Groups

https://docs.microsoft.com/en-us/database-engine/availability-groups/manually-prepare-secondary-database-availability-group

Perform transaction log backup

https://docs.microsoft.com/en-us/relational-databases/backup-restore/back-up-a-transaction-log-sql-server

Perform restore of user databases

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-a-database-backup-using-ssms

Perform database backups with options

https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql#additional-considerations

 

Manage authentication by using T-SQL

Manage certificates

https://techcommunity.microsoft.com/azure-database-support-blog/using-certificates-in-azure-sql-database-import

Manage security principals

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-managed-instance-aad-security-tutorial

 

Manage authorization by using T-SQL

Configure permissions for users to access database objects

https://docs.microsoft.com/en-us/sql/relational-databases/security/permissions-database-engine

Configure permissions by using custom roles

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-role-transact-sql

This brings us to the end of DP-300 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 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.

Sharing is Caring

  • 1
  • 1
  •  
  •  
  •  
    2
    Shares

You may also like

1 Comment

Leave a Reply

Your e-mail address will not be published. Required fields are marked *