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

Hey guys!
No feriado do dia 01/05, fiz a prova beta “DP-300 – Administering Relational Databases on Microsoft Azure”, que é focada para DBA’s que trabalham ou querem trabalhar com Azure e queria compartilhar minhas impressões sobre a prova e alguns materiais para estudo.

Antes de mais nada, se você quiser dicas de como fazer provas de certificação em casa sem ter muitos problemas, sugiro a leitura do meu post Microsoft Certification – Tips on how to take the test at home without a headache.

Em geral, achei essa prova difícil, especialmente por não ter estudado e a prova ter um conteúdo bem abrangente, especialmente nas comparações entre Azure SQL Database e o Azure Managed Instance, que não estou tão habituado de trabalhar, especialmente comparando preços entre os 2 e também em diversos cenários de migração entre On-Premises para Azure.

Material de estudo da prova DP-300 (Administering Relational Databases on Microsoft Azure)

Como essa prova ainda está em fase beta, a página do Learning Path dessa prova ainda não está disponível, ou seja, não tem material oficial pra estudo (como toda prova beta).

Material de estudo de acordo com o que eu lembro da prova

Vou destacar o que eu lembro que caiu na prova pra tentar direcionar o estudo de vocês, lembrando que existem várias variações da prova e nem sempre serão as mesmas questões:

• Cenários ideais para uso do elastic pool
• Estudem bastante a parte de Configuração de AlwaysOn em máquinas virtuais (Link aqui)
• Segurança do Azure SQL Database
• Criação de logins no Azure SQL Database usando autenticação Azure Active Directory
• Roles do Azure SQL Database
• Execução de backup em réplica secundária de AlwaysOn (Link aqui)
• DMV’s para fazer um troubleshooting de performance e locks em Azure SQL Database
• Cenários de Alta Disponibilidade entre Azure SQL Database e Azure Managed Instance com Leitura Habilitada na Réplica (preste atenção em custo e tiers Premium)
• Criação de conexão entre Azure SQL Database e Azure VM usando VPN, Endpoints ou Service Points
• Cenários de Replicação vs Failover Cluster com Automatic Failover: Qual o melhor cenário para cada
• Dynamic Data Masking (Link aqui)
• Criptografia de Colunas – Link aqui
• Transparent Data Encryption (TDE) – Link aqui
• Automatic Tuning
• Query Store
• Azure Managed Instance
• Criação de Backup do Azure Managed Instance
• Azure Site Recovery
• Redundância de Storage
• Muitos cenários de migração de On-premise para Azure SQL Database, Managed Instance e VM usando SSMS, PowerShell (Link aqui e veja os outros tutoriais)
• MySQL e Postgres rodando no Azure como PaaS (Link aqui)
• Azure Database Migration Service
• Alertas e notificações do SQL Server Agent
• Redução de contenção de tempdb

Material de estudo de acordo com a página da Microsoft sobre a prova

Também estou disponibilizando um grande trabalho feito pelo também MVP, Gregor Suttie, que compilou essa coletânea de links para ajudar nos estudos nessa prova e que estou compartilhando de forma a ficar no meu padrão de estilo de artigo:

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

• Deploy resources by using manual methods
  Clique para visualizar o conteúdo
  – 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
  Clique para visualizar o conteúdo
  – 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
  Clique para visualizar o conteúdo
  – 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
  Clique para visualizar o conteúdo
  – 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
  Clique para visualizar o conteúdo
  – 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
  Clique para visualizar o conteúdo
  – 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
  Clique para visualizar o conteúdo
  – 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
  Clique para visualizar o conteúdo
  – 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
  Clique para visualizar o conteúdo
  – 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
  Clique para visualizar o conteúdo
  – 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
  Clique para visualizar o conteúdo
  – 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
  Clique para visualizar o conteúdo
  – 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
  Clique para visualizar o conteúdo
  – 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
  Clique para visualizar o conteúdo
  – 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
  Clique para visualizar o conteúdo
  – 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
  Clique para visualizar o conteúdo
  – 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
  Clique para visualizar o conteúdo

  – 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
  Clique para visualizar o conteúdo
  – 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
  Clique para visualizar o conteúdo
  – 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
  Clique para visualizar o conteúdo
  – 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
  Clique para visualizar o conteúdo
  – 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
  Clique para visualizar o conteúdo
  – 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
  Clique para visualizar o conteúdo
  – 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
  Clique para visualizar o conteúdo
  – 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
  Clique para visualizar o conteúdo
  – 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
  Clique para visualizar o conteúdo
  – 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
  Clique para visualizar o conteúdo
  – 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
  Clique para visualizar o conteúdo
  – 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
  Clique para visualizar o conteúdo
  – 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
  Clique para visualizar o conteúdo
  – 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
  

And that's it, folks!
Espero que essas dicas tenham sido úteis para vocês terem mais confiança em fazer essa prova e até a próxima.