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!
