DP-300 Exam Study Guide (Administering Relational Databases On MicrosoftAzure)
You can access DP-300 Exam Microsoft official page here
Preparing for the DP-300 Administering Relational Databases on Microsoft Azure exam? Don’t know where to start? This post is the DP-300 Certificate Study Guide (with links to each exam objective).
I have curated a list of articles from Microsoft documentation for each objective of the DP-300 exam. Please share the post within your circles so it helps them to prepare for the exam.
Skills Measured
- Plan and Implement Data Platform Resources (15-20%)
- Implement a Secure Environment (15-20%)
- Monitor and Optimize Operational Resources (15-20%)
- Optimize Query Performance (5-10%)
- Perform Automation of Tasks (10-15%)
- Plan and Implement a High Availability and Disaster Recovery (HADR) Environment (15-20%)
- Perform Administration by Using T-SQL (10-15%)
DP-300 Exam Free Study Guide
For each test objective, I’ve compiled a thorough collection of articles from Microsoft documentation. Please feel free to forward this page to your friends and coworkers in order to assist them in preparing for the test .
Plan and Implement Data Platform Resources (15-20%)
Deploy Resources by Using Manual Methods
- Deploy database offerings on selected platforms
- Create an Azure SQL Database single database
- Configure customized deployment templates
- Create a single database in Azure SQL Database using an ARM template
- Apply patches and updates for hybrid and IaaS deployment
- Manage updates and patches for your VMs
- Patching SQL Azure
Recommend an Appropriate Database Offering Based on Specific Requirements
- Evaluate requirements for the deployment
- Azure SQL database deployment
- Evaluate the functional benefits/impact of possible database offerings
- What is Azure SQL?
- Evaluate the scalability of the possible database offering
- Azure SQL Database – scalability
- Evaluate the HA/DR of the possible database offering
- High availability for Azure SQL Database & SQL Managed Instance
- Business continuity and HADR for SQL Server on Azure VMs
- DR strategies using Azure SQL Database elastic pools
- Evaluate the security aspects of the possible database offering
- Azure SQL Database & SQL Managed Instance
Configure Resources for Scale and Performance
- Configure Azure SQL database/elastic pools for scale and performance
- Scaling out with Azure SQL Database
- Scale elastic pool resources in Azure SQL Database
- Tune performance for Azure SQL Database/MI
- Configure Azure SQL managed instances for scale and performance
- What is Azure SQL Managed Instance?
- Dynamically scale resources with minimal downtime
- Performance improvements for Managed Instance
- Configure SQL Server in Azure VMs for scale and performance
- Performance guidelines for SQL Server on Azure VMs
- Calculate resource requirements
- Storage & SQL Server capacity planning & configuration
- Evaluate database partitioning techniques, such as database sharding
- Sharding pattern
- Set up SQL Data Sync
- Set up SQL Data Sync between DBs in Azure SQL Database & SQL Server
Evaluate a Strategy for Moving to Azure
- Evaluate requirements for the migration
- Azure Database Migration Service – Prerequisites
- Evaluate offline or online migration strategies
- Migrate SQL Server to Azure SQL Database
- Evaluate requirements for the upgrade
- Pre upgrade Checklist
- Evaluate offline or online upgrade strategies
- Using active geo-replication in Azure SQL Database
Implement a Migration or Upgrade Strategy for Moving to Azure
- Implement an online migration strategy
- Using Azure Database Migration Service to migrate the databases
- Implement an offline migration strategy
- Migrate SQL Server to Azure SQL Database offline using DMS
- Implement an online upgrade strategy
- Azure SQL Database active geo-replication
- Implement an offline upgrade strategy
- Upgrade to a Different Edition of SQL Server
Implement a Secure Environment (15-20%)
Configure Database Authentication by Using Platform and Database Tools
- Configure Azure AD authentication
- Configure & manage Azure AD authentication with Azure SQL
- Create users from Azure AD identities
- Create contained users mapped to Azure AD identities
- Configure security principals
- Azure AD Service Principal authentication to SQL DB – Code Sample
Configure database authorization by using platform and database tools
- Configure database and object-level permissions using graphical tools
- Getting Started with Database Engine Permissions
- Apply the principle of least privilege for all securable
- Authorization & Permissions in SQL Server
Implement Security for Data at Rest
- Implement Transparent Data Encryption (TDE)
- TDE for SQL Database, SQL Managed Instance & Synapse Analytics
- Implement object-level encryption
- Configure Always Encrypted by using Azure Key Vault
- Implement Dynamic Data Masking
- Implement dynamic data masking with the Azure portal
- Implement Azure Key Vault and disk encryption for Azure VMs
- Create & encrypt a Windows VM with the Azure portal
Implement Security for Data in Transit
- Configure server and database-level firewall rules
- Create a server-level IP firewall rule
- Database-level IP firewall rules
- Server-level versus database-level IP firewall rules
- Implement Always Encrypted
- Configure Always Encrypted by using the Windows certificate store
Implement Compliance Controls for Sensitive Data
- Apply a data classification strategy
- What is data classification?
- Implement Compliance Controls for Sensitive Data
- Configure server and database audits
- Auditing for Azure SQL Database & Azure Synapse Analytics
- A Boost in Security for Azure SQL Database: Auditing
- Implement data change tracking
- Enable and Disable Change Tracking
- Perform vulnerability assessment
- SQL Vulnerability Assessment: Identify database vulnerabilities
Monitor and Optimize Operational Resources (15-20%)
Monitor Activity and Performance
- Prepare an operational performance baseline
- Establish a Performance Baseline
- Determine sources for performance metrics
- Sources of monitoring data for Azure Monitor
- Interpret performance metrics
- Query Performance Insight for Azure SQL Database
- Monitoring in Azure SQL Database & Azure SQL Managed Instance
- Configure and monitor activity and performance at the infrastructure, server, service, and database levels
- Monitoring & performance tuning in Azure SQL DB/MI
- Microsoft Azure database monitoring playbook
- Monitor Azure SQL DB/MI performance using DMVs
Implement Performance-related Maintenance Tasks
- Implement index maintenance tasks
- Automating Azure SQL Database maintenance tasks
- Implement statistics maintenance tasks
- Update Statistics Task
- Configure database auto-tuning
- Implement Performance-related Maintenance Tasks
- Automate database maintenance tasks
- Automate SQL DB index & statistics maintenance with Azure Automation
- Automating Azure SQL Database maintenance tasks
- Azure SQL agent jobs, Azure automation, SQL Server agent jobs
- SQL Agent Jobs
- Azure Automation: Your SQL Agent in the Cloud
- Creating a SQL Server Agent job in SQL Server
- Manage storage capacity
- Manage file space for databases in Azure SQL Database
Identify Performance-related Issues
- Configure Query Store to collect performance data
- Query Performance Insight for Azure SQL Database
- Identify sessions that cause blocking
- Finding Blocking Queries in SQL Azure
- Assess growth/fragmentation of databases and logs
- SQL Server Database Growth & Shrink Events
- Resolve index fragmentation by reorganizing or rebuilding indexes
- Assess performance-related database configuration parameters
- Including AutoClose, AutoShrink, AutoGrowth
- Set the AUTO_CLOSE Database Option to OFF
- Autogrow & Autoshrink settings in SQL Server
Configure Resources for Optimal Performance
- Configure storage and infrastructure resources
- Optimize IOPS, throughput, and latency
- Storage configuration for SQL Server VMs
- Optimize tempdb performance
- Learn about the tempdb database
- Optimize data and log files for performance
- Mapping data flow performance & tuning guide
- Manage the size of the transaction log file
- Configure server and service account settings for performance
- Determining proper SQL Server configuration settings
- Configure Resource Governor for performance
- Resource governance in Azure SQL Database
Configure a User Database for Optimal Performance
- Implement database-scoped configuration
- Alter database scoped configuration (Transact-SQL)
- Configure compute resources for scaling
- Manage to compute in Azure Synapse Analytics data warehouse
- Configure Intelligent Query Processing (IQP)
- Intelligent query processing in SQL databases
Optimize Query Performance (5-10%)
Review Query Plans
- Determine the appropriate type of execution plan
- Live Query Statistics, Actual Execution Plan, Estimated Execution Plan
- Live query statistics
- Display an actual execution plan
- Display the estimated execution plan
- Identify problem areas in execution plans
- Analyzing actual graphical execution plans
- Identifying and fixing performance problems using execution plans
- Extract query plans from the Query Store
- sys.query_store_plan (Transact-SQL)
Evaluate Performance Improvements
- Determine the appropriate Dynamic Management Views (DMVs) to gather query performance information
- Monitoring query performance with DMVs
- Identify performance issues using DMVs
- Monitor Azure SQL DB/MI performance using DMVs
- Identify and implement index changes for queries
- SQL Server index architecture & design guide
- Recommend query construct modifications based on resource usage
- Monitoring performance by using the Query Store
- Assess the use of hints for query performance
- Query hints: Transact-SQL
- Query tuning and hinting
Review Database Table and Index Design
- Identify data quality issues with duplication of data
- Data Quality Services (DQS) data matching process
- Identify the normal form of database tables
- Description of the database normalization basics
- Assess index design for performance
- Improving the performance of Azure SQL Databases with Index Advisor
- SQL Server index architecture & design guide
- Validate data types defined for columns
- Data types (Transact-SQL)
- SQL statement to get column type
- Recommend table and index storage including filegroups
- Recommendations when working with files & filegroups
- Evaluate table partitioning strategy
- Horizontal, vertical & functional data partitioning
- Evaluate the use of compression for tables and indexes
- sp_estimate_data_compression_savings (Transact-SQL)
Perform Automation of Tasks (10-15%)
Create Scheduled Tasks
- Manage schedules for regular maintenance jobs
- Automate management tasks using database jobs
- Configure multi-server automation
- Automated Administration Across an Enterprise
- Configure notifications for task success/failure/non-completion
- Notify an Operator of Job Status
Evaluate and Implement an Alert and Notification Strategy
- Create event notifications based on metrics
- Set up Azure Database for MySQL alerts using the Azure portal
- Create event notifications for Azure resources
- Create event notification (Transact-SQL)
- Create alerts for server configuration changes
- Capturing & Alerting on SQL Server Configuration Changes
- Create tasks that respond to event notifications
- Respond to events with Azure Monitor Alerts
Manage and Automate Tasks in Azure
- Perform automated deployment methods for resources
- Azure SQL database deployment
- Automate Backups
- Automated backups: Azure SQL Database & SQL Managed Instance
- Automate performance tuning and patching
- Automatic tuning: Azure SQL Database & SQL Managed Instance
- Automated Patching for SQL Server on Azure VMs
- Implement policies by using automated evaluation modes
- Administer servers by using Policy-Based Management
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
- Understanding & leveraging Azure SQL Database’s SLA
- Evaluate HADR for hybrid deployments
- Hybrid IT: Disaster recovery solutions
- Evaluate Azure-specific HADR solutions
- Azure only: Disaster recovery solutions
- Identify resources for HADR solutions
- The Always On availability groups feature
- Always On Failover Cluster Instances (SQL Server)
- About Log Shipping (SQL Server)
- SQL Server backup & restore with Microsoft Azure Blob storage service
Test a HADR Strategy by Using Platform, OS and, Database Tools
- Test HA by using failover
- Configure a failover group for Azure SQL Database
- Test DR by using failover or restore
- Restore your Azure SQL Database or failover to a secondary
Perform Backup and Restore a Database by Using Database Tools
- Perform a database backup with options
- Back up database
- Perform a database restore with options
- Restore Statements (Transact-SQL)
- Perform a database restore to a point in time
- Azure SQL database point in Time Restore
- Restore a database to a point in time in SQL Server
- Configure long-term backup retention
- Manage Azure SQL Database long-term backup retention
Configure HA/DR by Using OS, Platform, and Database Tools
- Configure replication
- Replication to Azure SQL Database
- Create an Always On Availability Group
- Configure a SQL Server availability group on Azure VMs
- Integrate a database into an Availability Group
- Add a database to an Always On availability group
- Configure quorum options for a Windows Server Failover Cluster
- Configure and manage quorum
- Configure an Availability Group listener
- Configure a listener for an Always On availability group
Perform Administration by Using T-SQL (10-15%)
Examine System Health
- Evaluate database health using DMVs
- Monitor Azure SQL Database & SQL Managed Instance using DMs
- Evaluate server health using DMVs
- System Dynamic Management Views
- Perform database consistency checks by using DBCC
- DBCC CHECKDB (Transact-SQL)
Monitor Database Configuration by Using T-SQL
- Assess proper database Autogrowth configuration
- SQL server database Growth & Autogrowth settings
- Report on database free space
- Display data & log space information for a database
- Review database configuration options
- Change the configuration settings for a database
Perform Backup and Restore a Database by Using T-SQL
- Prepare databases for Always On Availability Groups
- Prepare a database for an Always On availability group
- Perform transaction log backup
- Back up a Transaction Log
- Perform restore of user databases
- Restore a database backup using SSMS
- Perform database backups with options
- Additional considerations about backup options
Manage Authentication by Using T-SQL
- Manage certificates
- Using certificates in Azure SQL database: Import
- Manage security principals
- Security in Azure SQL Managed Instance using Azure AD server principals
Manage Authorization by Using T-SQL
- Configure permissions for users to access database objects
- Permissions in the Database Engine
- Configure permissions by using custom roles
- Create a new database role (Transact-SQL)
This brings us to the end of the DP-300 Administering Relational Databases on Microsoft Azure Study Guide.
Follow Me to Receive Updates on DP-300 Exam
Share the DP-300 Study Guide in Your Network
Share on facebook
Share on twitter
Share on linkedin
