Azure DP-300 Study Guide
The following I will be using to study for the DP-300 exam, now I am no SQL Server expert, far from it, some of these links might not be the best fit for that particular area, use them at your peril! 🙂
If your looking for extra study material or amazing SQL Server Workshops then check this out https://microsoft.github.io/sqlworkshops/
Hopefully this is of some use to people – if you find more accurate links let me know and I’ll update.
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-paas-vs-sql-server-iaas
• configure customized deployment templates
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-resource-manager-samples?tabs=single-database
• apply patches and updates for hybrid and IaaS deployment
https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-automated-patching
Recommend an appropriate database offering based on specific requirements
• evaluate requirements for the deployment
• evaluate the functional benefits/impact of possible database offerings
• evaluate the scalability of the possible database offering
• evaluate the HA/DR of the possible database offering
• evaluate the security aspects of the possible database offering
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/sql-database-elastic-pool-manage
• configure Azure SQL managed instances for scale and performance
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-managed-instance
• 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-and-configuration
• evaluate database partitioning techniques, such as database sharding
https://docs.microsoft.com/en-us/azure/architecture/best-practices/data-partitioning
Evaluate a strategy for moving to Azure
• evaluate requirements for the migration
https://docs.microsoft.com/en-us/sql/dma/dma-assesssqlonprem?view=sql-server-ver15
• evaluate offline or online migration strategies
https://docs.microsoft.com/en-us/azure/dms/tutorial-sql-server-to-azure-sql
• evaluate requirements for the upgrade
https://docs.microsoft.com/en-us/sql/database-engine/install-windows/supported-version-and-edition-upgrades-version-15?view=sql-server-ver15
• 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://datamigration.microsoft.com/scenario/sql-to-azuresqldb?step=1
• 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?view=sql-server-ver15
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?tabs=azure-powershell
• create users from Azure AD identities
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-aad-authentication
• configure security principals
https://docs.microsoft.com/en-us/sql/relational-databases/security/securing-sql-server?view=sql-server-ver15
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/relational-databases/security/permissions-database-engine?view=sql-server-ver15
• apply principle of least privilege for all securables
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/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/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-ver15
• implement object-level encryption
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/encrypt-a-column-of-data?view=sql-server-ver15
• implement Dynamic Data Masking
https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-ver15
• implement Azure Key Vault and disk encryption for Azure VMs
https://docs.microsoft.com/en-us/azure/virtual-machines/windows/disk-encryption-key-vault
Implement security for data in transit
• configure SQL DB and database-level firewall rules
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-firewall-configure
• implement Always Encrypted
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-ver15
• configure Azure Data Gateway
https://docs.microsoft.com/en-us/azure/analysis-services/analysis-services-gateway
Implement compliance controls for sensitive data
• apply a data classification strategy
https://docs.microsoft.com/en-us/sql/relational-databases/security/sql-data-discovery-and-classification?view=sql-server-ver15&tabs=t-sql
• configure server and database audits
https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/create-a-server-audit-and-database-audit-specification?view=sql-server-ver15
• implement data change tracking
https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/track-data-changes-sql-server?view=sql-server-ver15
• perform vulnerability assessment
https://docs.microsoft.com/en-us/sql/relational-databases/security/sql-vulnerability-assessment?view=sql-server-ver15
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?view=sql-server-ver15
• determine sources for performance metrics
https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver15
• interpret performance metrics
https://docs.microsoft.com/en-us/sql/relational-databases/performance/performance-monitoring-and-tuning-tools?view=sql-server-ver15
• assess database performance by using Azure SQL Database Intelligent
Performance
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-query-performance
• configure and monitor activity and performance at the infrastructure, server, service, and database levels
https://docs.microsoft.com/en-us/sql/relational-databases/performance/query-profiling-infrastructure?view=sql-server-ver15
Implement performance-related maintenance tasks
• implement index maintenance tasks
https://docs.microsoft.com/en-us/sql/relational-databases/maintenance-plans/rebuild-index-task-maintenance-plan?view=sql-server-ver15
• implement statistics maintenance tasks
https://docs.microsoft.com/en-us/sql/relational-databases/maintenance-plans/update-statistics-task-maintenance-plan?view=sql-server-ver15
• configure database auto-tuning
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-automatic-tuning-enable
• automate database maintenance tasks
– Azure SQL agent jobs, Azure automation, SQL server agent jobs
https://docs.microsoft.com/en-us/sql/relational-databases/maintenance-plans/use-the-maintenance-plan-wizard?view=sql-server-ver15
• manage storage capacity
https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15
Identify performance-related issues
• configure Query Store to collect performance data
https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver15
• identify sessions that cause blocking
https://support.microsoft.com/en-gb/help/224453/inf-understanding-and-resolving-sql-server-blocking-problems
• assess growth/fragmentation of databases and logs
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15
• assess performance-related database configuration parameters
– including AutoClose, AutoShrink, AutoGrowth
https://support.microsoft.com/en-gb/help/315512/considerations-for-the-autogrow-and-autoshrink-settings-in-sql-server
Configure resources for optimal performance
• configure storage and infrastructure resources
– optimize IOPS, throughput, and latency
– optimize tempdb performance
– optimize data and log files for performance
https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-server-storage-configuration
• configure server and service account settings for performance
https://support.microsoft.com/en-gb/help/319942/how-to-determine-proper-sql-server-configuration-settings
• configure Resource Governor for performance
https://docs.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor?view=sql-server-ver15
Configure a user database for optimal performance
• implement database-scoped configuration
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql?view=sql-server-ver15
• configure compute resources for scaling
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-single-database-scale
• configure Intelligent Query Processing (IQP)
https://docs.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing?view=sql-server-ver15
Optimize Query Performance (5-10%)
Review query plans
• determine the appropriate type of execution plan
– live Query Statistics, Actual Execution Plan, Estimated Execution Plan, Showplan
https://docs.microsoft.com/en-us/sql/relational-databases/performance/execution-plans?view=sql-server-ver15
• identify problem areas in execution plans
https://docs.microsoft.com/en-us/sql/relational-databases/performance/analyze-an-actual-execution-plan?view=sql-server-ver15
• extract query plans from the Query Store
https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver15
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?view=sql-server-ver15
• recommend query construct modifications based on resource usage
https://docs.microsoft.com/en-us/sql/relational-databases/performance/upgrade-dbcompat-using-qta?view=sql-server-ver15
• assess the use of hints for query performance
https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-ver15
Review database table and index design
• identify data quality issues with duplication of data
https://docs.microsoft.com/en-us/sql/data-quality-services/introduction-to-data-quality-services?view=sql-server-ver15
• identify normal form of database
https://docs.microsoft.com/en-us/office/troubleshoot/access/database-normalization-description
• assess index design for performance
https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-index-design-guide?view=sql-server-ver15
• validate data types defined for columns
https://www.mssqltips.com/sqlservertip/4799/validate-integer-and-decimal-values-in-sql-server/
• recommend table and index storage including filegroups
• evaluate table partitioning strategy
https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-files-and-filegroups?view=sql-server-ver15
• evaluate the use of compression for tables and indexes
https://docs.microsoft.com/en-us/sql/relational-databases/data-compression/enable-compression-on-a-table-or-index?view=sql-server-ver15
Perform Automation of Tasks (10-15%)
Create scheduled tasks
• manage schedules for regular maintenance jobs
https://docs.microsoft.com/en-us/sql/ssms/agent/schedule-a-job?view=sql-server-ver15
• configure multi-server automation
https://docs.microsoft.com/en-us/sql/ssms/agent/automated-administration-across-an-enterprise?view=sql-server-ver15
• configure notifications for task success/failure/non-completion
https://docs.microsoft.com/en-us/sql/ssms/agent/notify-an-operator-of-job-status?view=sql-server-ver15
Evaluate and implement an alert and notification strategy
• create event notifications based on metrics
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-insights-alerts-portal
• create event notifications for Azure resources
https://docs.microsoft.com/en-us/sql/relational-databases/service-broker/event-notifications?view=sql-server-ver15
• create alerts for server configuration changes
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-alert-transact-sql?view=sql-server-ver15
• create tasks that respond to event notifications
https://docs.microsoft.com/en-us/sql/relational-databases/service-broker/event-notifications?view=sql-server-ver15
Manage and automate tasks in Azure
• perform automated deployment methods for resources
https://docs.microsoft.com/en-us/azure/devops/pipelines/targets/azure-sqldb?view=azure-devops&tabs=yaml
• automate Backups
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-automated-backups?tabs=single-database
• automate performance tuning and patching
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-automatic-tuning
• implement policies by using automated evaluation modes
https://docs.microsoft.com/en-us/sql/relational-databases/policy-based-management/administer-servers-by-using-policy-based-management?view=sql-server-ver15
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
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-business-continuity
• evaluate HADR for hybrid deployments
https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-high-availability-dr
• evaluate Azure-specific HADR solutions
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-high-availability
• identify resources for HADR solutions
https://www.sqlskills.com/blogs/erin/sql-server-hadr-features/
Test an HADR strategy by using platform, OS and database tools
• test HA by using failover
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/perform-a-planned-manual-failover-of-an-availability-group-sql-server?view=sql-server-ver15
• test DR by using failover or restore
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-disaster-recovery
Perform backup and restore a database by using database tools
• perform a database backup with options
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/create-a-full-database-backup-sql-server?view=sql-server-ver15#TsqlProcedure
• perform a database restore with options
https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql?view=sql-server-ver15
• perform a database restore to a point in time
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-a-sql-server-database-to-a-point-in-time-full-recovery-model?view=sql-server-ver15
• configure long-term backup retention
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-long-term-retention
Configure DR by using platform and database tools
• configure replication
https://docs.microsoft.com/en-us/sql/relational-databases/replication/tutorial-preparing-the-server-for-replication?view=sql-server-ver15
• 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/sql/t-sql/statements/create-availability-group-transact-sql?view=sql-server-ver15
• integrate a database into an Availability Group
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-group-add-a-database?view=sql-server-ver15
• configure quorum options for a Windows Server Failover Cluster
https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/wsfc-quorum-modes-and-voting-configuration-sql-server?view=sql-server-ver15
• configure an Availability Group listener
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/create-or-configure-an-availability-group-listener-sql-server?view=sql-server-ver15
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#monitoring-query-performance
• evaluate server health using DMVs
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-monitoring-with-dmvs
• perform database consistency checks by using DBCC
https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql?view=sql-server-ver15
Monitor database configuration by using T-SQL
• assess proper database autogrowth configuration
https://www.red-gate.com/simple-talk/sql/database-administration/sql-server-database-growth-and-autogrowth-settings/
• report on database free space
https://docs.microsoft.com/en-us/sql/relational-databases/databases/display-data-and-log-space-information-for-a-database?view=sql-server-ver15#TsqlProcedure
• review database configuration options
https://docs.microsoft.com/en-us/sql/relational-databases/databases/change-the-configuration-settings-for-a-database?view=sql-server-ver15#TsqlProcedure
Perform backup and restore a database by using T-SQL
• prepare databases for AlwaysOn Availability Groups
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/manually-prepare-a-secondary-database-for-an-availability-group-sql-server?view=sql-server-ver15
• perform transaction log backup
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/back-up-a-transaction-log-sql-server?view=sql-server-ver15#using-transact-sql
• perform restore of user databases
https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql?view=sql-server-ver15
• perform database backups with options
https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-ver15
Manage authentication by using T-SQL
• manage certificates
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-certificate-transact-sql?view=sql-server-ver15
• manage security principals
https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/principals-database-engine?view=sql-server-ver15
Manage authorization by using T-SQL
• configure permissions for users to access database objects
https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/grant-a-permission-to-a-principal?view=sql-server-ver15
• configure permissions by using custom roles
https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/create-an-application-role?view=sql-server-ver15#TsqlProcedure
Good luck with the Exam!
Looks great. I am preparing as well.
btw, found some ‘related’ questions at
https://www.examtopics.com/exams/microsoft/dp-200/view/
https://www.examtopics.com/exams/microsoft/dp-201/view/
Really not a whole lot of study material aside from the MS study docs
[…] Created a DP-300 Administering Relational Databases on Microsoft Azurestudy guide: – https://gregorsuttie.com/2020/04/19/azure-dp-300-study-guide/ […]
wow this is super nice …I am also preparing for the exam even though i am very much new to SQL
For the following…
Deploy resources by using manual methods
• apply patches and updates for hybrid and IaaS deployment
I think the link should be:
https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-automated-patching
Hi Jov
Thanks I’ve updated and moved this into a github page so if you find any more please submit a pr
Hi, how can i find the github page?
https://github.com/gsuttie/AzureResources/tree/master/Exams – hope this helps
[…] I studied from this study guide from Gregor Suttie -> https://gregorsuttie.com/2020/04/19/azure-dp-300-study-guide/ […]
Hello,
Can you please let me know if DP – 300 is lab based test or similar to AZ – 900 with multiple choices?
Thanks!
Hi Vijay
Due to Covid-19 there are no labs at the moment.
Hi,
Are there still no labs for DP-300.It will be great if I get to know as I am planning to take the exam in sometime now. Thanks.
Regards,
Karim
[…] Azure DP-300 Study Guide December 2, 2020 | No Comments | Category: DP-300 Relational Database […]
Thanks for sharing this
This essay is really educational. I simply wanted to say how much I appreciate what you do. Keep on disseminating the message. I appreciate your fantastic posting. You are a really good writer, and I really liked reading it. I urge people to read your blog.
This is a very informative essay. I just want to express my admiration for your work. Continue to spread the word. Thanks for the amazing posting! I truly enjoyed reading it, you are really a great author. I recommend others to read your blog.
Attractive! In every sense, this article was outstanding. Thank you for sharing this knowledge with me. Yes, your blog is fantastic, and I frequently read your stuff. Keep up the excellent work! You already know that a lot of people are hunting for this information, so you could really help them out.
Attractive! This article excelled in every way. I appreciate you sharing this knowledge with me. Yes, your blog is great, and I enjoy reading your writing. Keep up the great work! You are already aware of how popular this knowledge is, and you could help a lot of people.