My impressions and study material for the DP-300 - Administering Relational Databases on Microsoft Azure (beta) exam

Hey guys!
On the holiday of 05/01, I took the beta test “DP-300 – Administering Relational Databases on Microsoft Azure”, which is focused on DBA's who work or want to work with Azure and I wanted to share my impressions about the test and some study materials.

First of all, if you want tips on how to take certification tests at home without having too many problems, I suggest reading my post Microsoft Certification – Tips on how to take the test at home without a headache.

In general, I found this test difficult, especially because I hadn't studied it and the test has very comprehensive content, especially in the comparisons between Azure SQL Database and Azure Managed Instance, which I'm not so used to working with, especially comparing prices between the 2 and also in different migration scenarios between On-Premises to Azure.

DP-300 (Administering Relational Databases on Microsoft Azure) study material

As this test is still in beta phase, the Learning Path page for this test it is not yet available, that is, there is no official material for study (like all beta tests).

Study material according to what I remember from the test

I will highlight what I remember from the test to try to guide your study, remembering that there are several variations of the test and they will not always be the same questions:

• Ideal scenarios for using elastic pool
• Study the AlwaysOn Configuration part on virtual machines (Link here)
• Azure SQL Database Security
• Creating logins to Azure SQL Database using Azure Active Directory authentication
• Azure SQL Database roles
• Executing backup on secondary AlwaysOn replica (Link here)
• DMV’s to troubleshoot performance and locks in Azure SQL Database
• High Availability Scenarios between Azure SQL Database and Azure Managed Instance with Read Enabled Replica (pay attention to cost and Premium tiers)
• Creating a connection between Azure SQL Database and Azure VM using VPN, Endpoints or Service Points
• Replication Scenarios vs Failover Cluster with Automatic Failover: What is the best scenario for each
• Dynamic Data Masking (Link here)
• Column Encryption – Link here
• Transparent Data Encryption (TDE) – Link here
• Automatic Tuning
• QueryStore
• Azure Managed Instance
• Azure Managed Instance Backup Creation
• Azure Site Recovery
• Storage Redundancy
• Many migration scenarios from On-premise to Azure SQL Database, Managed Instance and VM using SSMS, PowerShell (Link here and see the other tutorials)
• MySQL and Postgres running on Azure as PaaS (Link here)
• Azure Database Migration Service
• SQL Server Agent alerts and notifications
• Reducing tempdb contention

Study material according to the Microsoft page about the test

I'm also making available a great work done by fellow MVP, Gregor Suttie, who compiled this collection of links to help with studying for this test and which I am sharing in order to comply with my article style standard:

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

• Deploy resources by using manual methods
  Click to view content
  – 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
  

• Configure resources for scale and performance
  Click to view content
  – 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
  Click to view content
  – 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
  Click to view content
  – 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
  Click to view content
  – 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
  Click to view content
  – 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
  Click to view content
  – 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
  Click to view content
  – 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
  Click to view content
  – 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
  Click to view content
  – 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
  Click to view content
  – 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

  – 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
  Click to view content
  – 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

  – 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
  Click to view content
  – optimize IOPS, throughput, and latency
  https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-performance

  – optimize tempdb performance
  https://support.microsoft.com/en-us/help/2154845/recommendations-to-reduce-allocation-contention-in-sql-server-tempdb-d

  – 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
  Click to view content
  – 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
  Click to view content
  – determine the appropriate type of execution plan
  https://www.sqlshack.com/sql-server-execution-plans-types/

  – 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
  Click to view content
  – 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 construction 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
  Click to view content

  – 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/

  – 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
  Click to view content
  – 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
  Click to view content
  – 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
  Click to view content
  – 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
  Click to view content
  – 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
  Click to view content
  – 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 using database tools
  Click to view content
  – 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
  Click to view content
  – 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
  Click to view content
  – 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
  Click to view content
  – 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 using T-SQL
  Click to view content
  – 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 using T-SQL
  Click to view content
  – 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 using T-SQL
  Click to view content
  – 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 using T-SQL
  Click to view content
  – 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
  

That's it, folks!
I hope these tips were useful for you to have more confidence in taking this test and see you next time.