Azure DP-300 Exam Study guide
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-single-database-get-started?tabs=azure-portal&?WT.mc_id=AZ-MVP-5003451 - configure customized deployment templates
https://docs.microsoft.com/en-us/azure/templates/microsoft.sql/servers?tabs=json&?WT.mc_id=AZ-MVP-5003451 - apply patches and updates for hybrid and IaaS deployment
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-single-database-get-started?tabs=azure-portal&WT.mc_id=AZ-MVP-5003451
Recommend an appropriate database offering based on specific requirements - evaluate requirements for the deployment
https://docs.microsoft.com/en-us/azure/devops/pipelines/targets/azure-sqldb?view=azure-devops&tabs=yaml&WT.mc_id=AZ-MVP-5003451 - evaluate the functional benefits/impact of possible database offerings
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-paas-vs-sql-server-iaas?WT.mc_id=AZ-MVP-5003451 - evaluate the scalability of the possible database offering
https://techcommunity.microsoft.com/t5/azure-sql-database/azure-sql-database-8211-scalability/ba-p/386201?WT.mc_id=AZ-MVP-5003451 - evaluate the HA/DR of the possible database offering
https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-high-availability-dr?WT.mc_id=AZ-MVP-5003451 - evaluate the security aspects of the possible database offering
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-security-overview?WT.mc_id=AZ-MVP-5003451
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-scale-introduction - configure Azure SQL managed instances for scale and performance
https://docs.microsoft.com/en-us/azure/azure-sql/database/scale-resources?WT.mc_id=AZ-MVP-5003451 - 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?WT.mc_id=AZ-MVP-5003451 - calculate resource requirements
https://docs.microsoft.com/en-us/sharepoint/administration/storage-and-sql-server-capacity-planning-and-configuration?WT.mc_id=AZ-MVP-5003451 - evaluate database partitioning techniques, such as database sharding
https://docs.microsoft.com/en-us/azure/architecture/patterns/sharding?WT.mc_id=AZ-MVP-5003451 - set up SQL Data Sync
https://docs.microsoft.com/en-us/azure/azure-sql/database/sql-data-sync-sql-server-configure?WT.mc_id=AZ-MVP-5003451
Evaluate a strategy for moving to Azure - evaluate requirements for the migration
https://docs.microsoft.com/en-us/azure/dms/pre-reqs?WT.mc_id=AZ-MVP-5003451 - evaluate offline or online migration strategies
https://azure.microsoft.com/en-gb/services/database-migration/?WT.mc_id=AZ-MVP-5003451 - 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#pre-upgrade-checklist&WT.mc_id=AZ-MVP-5003451 - evaluate offline or online upgrade strategies
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-manage-application-rolling-upgrade?WT.mc_id=AZ-MVP-5003451
Implement a migration or upgrade strategy for moving to Azure - implement an online migration strategy
https://docs.microsoft.com/en-us/azure/dms/tutorial-sql-server-azure-sql-online?WT.mc_id=AZ-MVP-5003451 - implement an offline migration strategy
https://docs.microsoft.com/en-us/azure/dms/tutorial-sql-server-to-azure-sql?WT.mc_id=AZ-MVP-5003451 - implement an online upgrade strategy
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-manage-application-rolling-upgrade?WT.mc_id=AZ-MVP-5003451 - 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?WT.mc_id=AZ-MVP-5003451
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?WT.mc_id=AZ-MVP-5003451 - create users from Azure AD identities
https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure?tabs=azure-powershell#create-contained-users-mapped-to-azure-ad-identities&WT.mc_id=AZ-MVP-5003451 - configure security principals
https://techcommunity.microsoft.com/t5/azure-sql-database/azure-ad-service-principal-authentication-to-sql-db-code-sample/ba-p/481467?WT.mc_id=AZ-MVP-5003451
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/authentication-access/getting-started-with-database-engine-permissions?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451 - 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?WT.mc_id=AZ-MVP-5003451
Implement security for data at rest - implement Transparent Data Encryption (TDE)
https://docs.microsoft.com/en-us/azure/sql-database/transparent-data-encryption-azure-sql?tabs=azure-portal&WT.mc_id=AZ-MVP-5003451 - implement object-level encryption
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-always-encrypted-azure-key-vault?tabs=azure-powershell&WT.mc_id=AZ-MVP-5003451 - implement Dynamic Data Masking
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-dynamic-data-masking-get-started-portal?WT.mc_id=AZ-MVP-5003451 - implement Azure Key Vault and disk encryption for Azure VMs
https://docs.microsoft.com/en-us/azure/virtual-machines/windows/disk-encryption-portal-quickstart?WT.mc_id=AZ-MVP-5003451
Implement security for data in transit - configure server and database-level firewall rules
https://docs.microsoft.com/en-us/azure/azure-sql/database/firewall-configure#server-level-versus-database-level-ip-firewall-rules?WT.mc_id=AZ-MVP-5003451 - implement Always Encrypted
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-always-encrypted?WT.mc_id=AZ-MVP-5003451
Implement compliance controls for sensitive data - apply a data classification strategy
https://docs.microsoft.com/bs-latn-ba/azure/cloud-adoption-framework/govern/policy-compliance/data-classification?WT.mc_id=AZ-MVP-5003451 - configure server and database audits
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-auditing?WT.mc_id=AZ-MVP-5003451 - implement data change tracking
https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/enable-and-disable-change-tracking-sql-server?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451 - perform a vulnerability assessment
https://docs.microsoft.com/en-us/azure/sql-database/sql-vulnerability-assessment?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451
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&WT.mc_id=AZ-MVP-5003451 - determine sources for performance metrics
https://docs.microsoft.com/en-us/azure/azure-monitor/platform/data-sources?WT.mc_id=AZ-MVP-5003451 - interpret performance metrics
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-query-performance?WT.mc_id=AZ-MVP-5003451 - assess database performance by using Intelligent Insights for Azure SQL Database and
Managed Instance
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-intelligent-insights?WT.mc_id=AZ-MVP-5003451 - configure and monitor activity and performance at the infrastructure, server, service, and
database levels
https://docs.microsoft.com/en-us/azure/azure-sql/database/monitor-tune-overview?WT.mc_id=AZ-MVP-5003451
Implement performance-related maintenance tasks - implement index maintenance tasks
https://docs.microsoft.com/answers/questions/129461/automating-azure-sql-database-index-maintenance.html?WT.mc_id=AZ-MVP-5003451 - implement statistics maintenance tasks
https://docs.microsoft.com/en-us/sql/relational-databases/maintenance-plans/update-statistics-task-maintenance-plan?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451 - configure database auto-tuning
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-automatic-tuning-enable?WT.mc_id=AZ-MVP-5003451 - automate database maintenance tasks
https://docs.microsoft.com/en-us/azure/azure-sql/database/automation-manage?WT.mc_id=AZ-MVP-5003451 - manage storage capacity
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-file-space-management?WT.mc_id=AZ-MVP-5003451
Identify performance-related issues - configure Query Store to collect performance data
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-query-performance?WT.mc_id=AZ-MVP-5003451 - identify sessions that cause blocking
https://azure.microsoft.com/en-in/blog/finding-blocking-queries-in-sql-azure?WT.mc_id=AZ-MVP-5003451 - assess growth/fragmentation of databases and logs
https://docs.microsoft.com/en-us/azure/azure-sql/database/monitor-tune-overview?WT.mc_id=AZ-MVP-5003451 - assess performance-related database configuration parameters
https://docs.microsoft.com/en-us/azure/azure-sql/database/performance-guidance?WT.mc_id=AZ-MVP-5003451
Configure resources for optimal performance - configure storage and infrastructure resources
https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-server-storage-configuration?WT.mc_id=AZ-MVP-5003451 - configure server and service account settings for performance
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?WT.mc_id=AZ-MVP-5003451 - configure Resource Governor for performance
https://azure.microsoft.com/en-in/blog/resource-governance-in-azure–sql-database/?WT.mc_id=AZ-MVP-5003451
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/synapse-analytics/sql-data-warehouse/sql-data-warehouse-manage-compute-overview?WT.mc_id=AZ-MVP-5003451 - configure Intelligent Query Processing (IQP)
https://docs.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451
Optimize Query Performance (5-10%)
Review query plans - determine the appropriate type of execution plan
https://docs.microsoft.com/en-us/sql/relational-databases/performance/live-query-statistics?view=sql-server-ver15?WT.mc_id=AZ-MVP-5003451 - 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&WT.mc_id=AZ-MVP-5003451 - extract query plans from the Query Store
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-query-store-plan-transact-sql?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451
Evaluate performance improvements - determine the appropriate Dynamic Management Views (DMVs) to gather query
performance information
https://docs.microsoft.com/en-us/azure/azure-sql/database/monitoring-with-dmvs#monitoring-query-performance?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451 - identify performance issues using DMVs
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-monitoring-with-dmvs?WT.mc_id=AZ-MVP-5003451 - 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&WT.mc_id=AZ-MVP-5003451 - recommend query construct modifications based on resource usage
https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451 - assess the use of hints for query performance
https://docs.microsoft.com/en-us/azure/azure-sql/database/performance-guidance#query-tuning-and-hinting?WT.mc_id=AZ-MVP-5003451
Review database table and index design - identify data quality issues with duplication of data
https://docs.microsoft.com/en-us/sql/data-quality-services/data-matching?view=sql-server-ver15?WT.mc_id=AZ-MVP-5003451 - identify normal form of database tables
https://docs.microsoft.com/en-us/office/troubleshoot/access/database-normalization-description?WT.mc_id=AZ-MVP-5003451 - assess index design for performance
https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-index-design-guide?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451 - validate data types defined for columns
https://docs.microsoft.com/en-us/t-sql/data-types/data-types-transact-sql?WT.mc_id=AZ-MVP-5003451 - recommend table and index storage including filegroups
https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-files-and-filegroups?view=sql-server-ver15#Recommendations&WT.mc_id=AZ-MVP-5003451 - evaluate table partitioning strategy
https://docs.microsoft.com/en-us/azure/architecture/best-practices/data-partitioning?WT.mc_id=AZ-MVP-5003451 - evaluate the use of compression for tables and indexes
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-estimate-data-compression-savings-transact-sql?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451
Perform Automation of Tasks (10-15%)
Create scheduled tasks - manage schedules for regular maintenance jobs
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-job-automation-overview?WT.mc_id=AZ-MVP-5003451 - configure multi-server automation
https://docs.microsoft.com/en-us/sql/ssms/agent/automated-administration-across-an-enterprise?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451 - 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&WT.mc_id=AZ-MVP-5003451
Evaluate and implement an alert and notification strategy - create event notifications based on metrics
https://docs.microsoft.com/en-us/azure/mysql/howto-alert-on-metric?WT.mc_id=AZ-MVP-5003451 - create event notifications for Azure resources
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-event-notification-transact-sql?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451 - create alerts for server configuration changes
https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/alerts-create?WT.mc_id=AZ-MVP-5003451 - create tasks that respond to event notifications
https://docs.microsoft.com/en-us/azure/azure-monitor/learn/tutorial-response?WT.mc_id=AZ-MVP-5003451
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&WT.mc_id=AZ-MVP-5003451 - automate backups
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-automated-backups?tabs=single-database&WT.mc_id=AZ-MVP-5003451 - automate performance tuning and patching
https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-automated-patching?WT.mc_id=AZ-MVP-5003451 - 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&WT.mc_id=AZ-MVP-5003451
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://azure.microsoft.com/en-in/blog/understanding-and-leveraging-azure-sql-database-sla?&WT.mc_id=AZ-MVP-5003451 - evaluate HADR for hybrid deployments
https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-high-availability-dr#hybrid-it-disaster-recovery-solutions?&WT.mc_id=AZ-MVP-5003451 - evaluate Azure-specific HADR solutions
https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-high-availability-dr#azure-only-disaster-recovery-solutions?&WT.mc_id=AZ-MVP-5003451 - identify resources for HADR solutions
https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/business-continuity-high-availability-disaster-recovery-hadr-overview?&WT.mc_id=AZ-MVP-5003451
Test an HADR strategy by using platform, OS, and database tools - test HA by using failover
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-configure-failover-group?tabs=azure-portal&WT.mc_id=AZ-MVP-5003451 - test DR by using failover or restore
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-disaster-recovery?WT.mc_id=AZ-MVP-5003451
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/back-up-database-backup-options-page?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451 - perform a database restore with options
https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451 - perform a database restore to a point in time
https://azure.microsoft.com/en-in/blog/azure-sql-database-point-in-time-restore?WT.mc_id=AZ-MVP-5003451 - configure long-term backup retention
https://docs.microsoft.com/bs-cyrl-ba/azure/sql-database/sql-database-long-term-backup-retention-configure?WT.mc_id=AZ-MVP-5003451
Configure HA/DR by using OS, platform, and database tools - configure replication
https://docs.microsoft.com/en-us/azure/sql-database/replication-to-sql-database?WT.mc_id=AZ-MVP-5003451 - create an Availability Group
https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-portal-sql-availability-group-tutorial?WT.mc_id=AZ-MVP-5003451 - configure auto-failover groups
https://docs.microsoft.com/en-us/azure/azure-sql/database/auto-failover-group-overview?tabs=azure-powershell&WT.mc_id=AZ-MVP-5003451 - 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&WT.mc_id=AZ-MVP-5003451 - configure quorum options for a Windows Server Failover Cluster
https://docs.microsoft.com/en-us/windows-server/failover-clustering/manage-cluster-quorum?WT.mc_id=AZ-MVP-5003451 - 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&WT.mc_id=AZ-MVP-5003451
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?WT.mc_id=AZ-MVP-5003451 - evaluate server health using DMVs
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/system-dynamic-management-views?view=sql-server-ver15?WT.mc_id=AZ-MVP-5003451 - 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&WT.mc_id=AZ-MVP-5003451
Monitor database configuration by using T-SQL - assess proper database autogrowth configuration
https://docs.microsoft.com/en-us/troubleshoot/sql/admin/considerations-autogrow-autoshrink?WT.mc_id=AZ-MVP-5003451 - 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&WT.mc_id=AZ-MVP-5003451 - 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&WT.mc_id=AZ-MVP-5003451
Perform backup and restore a database by using T-SQL - prepare databases for Always On 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&WT.mc_id=AZ-MVP-5003451 - 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&WT.mc_id=AZ-MVP-5003451 - perform restore of user databases
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-a-database-backup-using-ssms?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451 - perform database backups with options
https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-ver15#Additional_Considerations?WT.mc_id=AZ-MVP-5003451
Manage authentication by using T-SQL - manage certificates
https://docs.microsoft.com/en-us/sql/relational-databases/security/sql-server-certificates-and-asymmetric-keys?WT.mc_id=AZ-MVP-5003451 - manage security principals
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-managed-instance-aad-security-tutorial?WT.mc_id=AZ-MVP-5003451
Manage authorization by using T-SQL - configure permissions for users to access database objects
https://docs.microsoft.com/en-us/sql/relational-databases/security/permissions-database-engine?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451 - configure permissions by using custom roles
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-role-transact-sql?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451
