Azure DP-300 Study Guide

The following I will be using to study for the DP-300 exam, now I am no SQL Server expert, far from it, some of these links might not be the best fit for that particular area, use them at your peril! 🙂

If your looking for extra study material or amazing SQL Server Workshops then check this out https://microsoft.github.io/sqlworkshops/

Hopefully this is of some use to people – if you find more accurate links let me know and I’ll update.

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

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

Good luck with the Exam!