DP-300 - Administering Relational Databases on Microsoft Azure

DP-300 Exam Resources 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-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&amp;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</a
• 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&amp;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 a href=“https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/create-a-full-database-backup-sql-server?view=sql-server-ver15#TsqlProcedure"> - 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