DP-300: Administering Relational Databases on Microsoft Azure

DP-300: Administering Relational Databases on Microsoft Azure is the requirements for the Microsoft Certified: Azure Database Administrator Associate

Exam requirements

The official exam document are published here: https://docs.microsoft.com/en-us/learn/certifications/exams/dp-300

Exam preparation

Books covering the exam

Video training for the exam

PluralSight

• Azure SQL Database for the SQL Server DBA
  https://www.pluralsight.com/courses/azure-sql-database-dba
• Implementing a Relational Database in Microsoft Azure SQL Database
  https://app.pluralsight.com/library/courses/microsoft-azure-implementing-relational-database-solutions/table-of-contents
• Managing Azure SQL Database
  https://app.pluralsight.com/library/courses/microsoft-azure-sql-database-managing/table-of-contents
• Securing Access to Microsoft Azure Databases
  https://app.pluralsight.com/library/courses/microsoft-azure-databases-access-securing/table-of-contents
• Managing Data Security and Policy in Microsoft Azure
  https://app.pluralsight.com/library/courses/microsoft-azure-data-security-policy-managing/table-of-contents
• Microsoft Azure Database Monitoring Playbook
  https://app.pluralsight.com/library/courses/microsoft-azure-database-playbook-monitoring/table-of-contents
• Optimizing Microsoft Azure Data Solutions
  https://app.pluralsight.com/library/courses/microsoft-azure-optimizing-data-solutions/table-of-contents
• Implementing Data Continuity and Availability in Microsoft Azure
  https://app.pluralsight.com/library/courses/microsoft-azure-implementing-data-continuity-availability/table-of-contents

Channel 9

• Azure SQL for Beginners
  https://www.youtube.com/playlist?list=PLlrxD0HtieHi5c9-i_Dnxw9vxBY-TqaeN&WT.mc_id=azuresql4beg_azuresql-ch9-niner

SQLBits

• XVIII (2019) Conference
  https://sqlbits.com/content/Event18?type=3

Online training

Microsoft Learn (free)

• Azure Fundamentals
  https://docs.microsoft.com/en-us/learn/paths/azure-fundamentals/

• Azure SQL fundamentals
  https://docs.microsoft.com/en-us/learn/paths/azure-sql-fundamentals/
• Plan and implement data platform resources
  https://docs.microsoft.com/en-us/learn/paths/plan-implement-data-platform-resources/
• Implement a secure environment for a database service
  https://docs.microsoft.com/en-us/learn/paths/implement-secure-environment-database-service/
• Monitor and optimize operational resources in SQL Server
  https://docs.microsoft.com/en-us/learn/paths/monitor-optimize-operational-resources-sql-server/
• Optimize query performance in SQL Server
  https://docs.microsoft.com/en-us/learn/paths/optimize-query-performance-sql-server/
• Automate tasks in SQL Server
  https://docs.microsoft.com/en-us/learn/paths/automate-tasks-sql-server/
• Plan and implement a high availability and disaster recovery environment
  https://docs.microsoft.com/en-us/learn/paths/plan-implement-high-availability-disaster-recovery-environment

SQL Workshop

• SQL Server Workshops (microsoft.github.io)

Instructor-led training

Microsoft Learning Partner

• Course DP-300T00-A: Administering Relational Databases on Microsoft Azure
  https://docs.microsoft.com/en-us/learn/certifications/courses/dp-300t00

Exam Objectives

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
  • set up SQL Data Sync
• 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