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

Speak guys!
On the holiday of 01/05, 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 wanted to share my impressions about the test and some study materials.

First of all, if you want tips on how to take certification exams 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 didn't study and the test has a very comprehensive content, especially in the comparisons between Azure SQL Database and Azure Managed Instance, which I'm not so used to working, especially comparing prices between 2 and also in various migration scenarios between On-Premises for Azure.

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

As this test is still in beta, the Learning Path page for that exam it is not yet available, that is, it does not have official material for study (like any beta test).

Study material according to what I remember from the exam

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

• Ideal scenarios for using the elastic pool
• Study the configuration part of AlwaysOn on virtual machines (Link here)
• Azure SQL Database security
• Creating logins to Azure SQL Database using Azure Active Directory authentication
• Azure SQL Database Roles
• Perform backup on secondary replica of AlwaysOn (Link here)
• DMV's to perform a performance and lock troubleshooting in Azure SQL Database
• High Availability Scenarios between Azure SQL Database e Azure Managed Instance with Read Enabled on Replica (pay attention to cost and Premium tiers)
• Connection creation between Azure SQL Database and Azure VM using VPN, Endpoints or Service Points
• Replication vs Failover Cluster Scenarios 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
• Query Store
• 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
• Tempdb contention reduction

Study material according to the Microsoft exam page

I'm also making available a great job done by the MVP too, Gregor Suttie, who compiled this collection of links to help with studies in this exam and that I am sharing in order to stay in my pattern of article style:

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 the 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 the 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%)

• Activity and performance monitor
  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 the 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 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
  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 to database by 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 by 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 to database by 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 by 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 by 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 have been useful for you to have more confidence in taking this test and until next time.