Edit this page

Cloud Family > Study Guides > DP-300 - Administering Relational Databases on Microsoft Azure

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&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&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