DP-300: Administering Relational Databases on Microsoft Azure

3 ay / 48 saat 250 m

1. Plan and Implement Data Platform Resources
1.1. Deploy resources by using manual methods
- deploy database offerings on selected platforms
- configure customized deployment templates
- apply patches and updates for hybrid and IaaS deployment

1.2. 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

1.3. Configure resources for scale and performance
- configure Azure SQL database/elastic pools for scale and performance
- configure Azure SQL managed instances for scale and performance
- configure SQL Server in Azure VMs for scale and performance
- calculate resource requirements
- evaluate database partitioning techniques, such as database sharding
- set up SQL Data Sync

1.4. Evaluate a strategy for moving to Azure
- evaluate requirements for the migration
- evaluate offline or online migration strategies
- evaluate requirements for the upgrade
- evaluate offline or online upgrade strategies

1.5. Implement a migration or upgrade strategy for moving to Azure
- implement an online migration strategy
- implement an offline migration strategy
- implement an online upgrade strategy
- implement an offline upgrade strategy

2. Implement a Secure Environment
2.1. Configure database authentication by using platform and database tools
- configure Azure AD authentication
- create users from Azure AD identities
- configure security principals

2.2. Configure database authorization by using platform and database tools
- configure database and object-level permissions using graphical tools
- apply principle of least privilege for all securables

2.3. Implement security for data at rest
- implement Transparent Data Encryption (TDE)
- implement object-level encryption
- implement Dynamic Data Masking
- implement Azure Key Vault and disk encryption for Azure VMs

2.4. Implement security for data in transit
- configure server and database-level firewall rules
- implement Always Encrypted

2.5. Implement compliance Controls for sensitive data
- apply a data classification strategy
- configure server and database audits
- implement data change tracking
- perform a vulnerability assessment

3. Monitor and Optimize Operational Resources
3.1. Monitor activity and performance
- prepare an operational performance baseline
- determine sources for performance metrics
- interpret performance metrics
- assess database performance by using Intelligent Insights for Azure SQL Database and Managed Instance
- configure and monitor activity and performance at the infrastructure, server, service, and database levels

3.2. Implement performance-related maintenance tasks
- implement index maintenance tasks
- implement statistics maintenance tasks
- configure database auto-tuning
- automate database maintenance tasks
- manage storage capacity

3.3. Identify performance-related issues
- configure Query Store to collect performance data
- identify sessions that cause blocking
- assess growth/fragmentation of databases and logs
- assess performance-related database configuration parameters

3.4. Configure resources for optimal performance
- configure storage and infrastructure resources
- configure server and service account settings for performance
- configure Resource Governor for performance

3.5. Configure a user database for optimal performance
- implement database-scoped configuration
- configure compute resources for scaling
- configure Intelligent Query Processing (IQP)

4. Optimize Query Performance
4.1. Review query plans
- determine the appropriate type of execution plan
- identify problem areas in execution plans
- extract query plans from the Query Store

4.2. Evaluate performance improvements
- determine the appropriate Dynamic Management Views (DMVs) to gather query performance information
- identify performance issues using DMVs
- identify and implement index changes for queries
- recommend query construct modifications based on resource usage
- assess the use of hints for query performance

4.3. Review database table and index design
- identify data quality issues with duplication of data
- identify normal form of database tables
- assess index design for performance
- validate data types defined for columns
- recommend table and index storage including filegroups
- evaluate table partitioning strategy
- evaluate the use of compression for tables and indexes

5. Perform Automation of Tasks
5.1. Create scheduled tasks
- manage schedules for regular maintenance jobs
- configure multi-server automation
- configure notifications for task success/failure/non-completion

5.2. Evaluate and implement an alert and notification strategy
- create event notifications based on metrics
- create event notifications for Azure resources
- create alerts for server configuration changes
- create tasks that respond to event notifications

5.3. Manage and automate tasks in Azure
- perform automated deployment methods for resources
- automate backups
- automate performance tuning and patching
- implement policies by using automated evaluation modes

6. Plan and Implement a High Availability and Disaster Recovery (HADR) Environment
6.1. Recommend an HADR strategy for a data platform solution
- recommend HADR strategy based on RPO/RTO requirements
- evaluate HADR for hybrid deployments
- evaluate Azure-specific HADR solutions
- identify resources for HADR solutions

6.2. Test an HADR strategy by using platform, OS, and database tools
- test HA by using failover
- test DR by using failover or restore

6.3. Perform backup and restore a database by using database tools
- perform a database backup with options
- perform a database restore with options
- perform a database restore to a point in time
- configure long-term backup retention

6.4. Configure HA/DR by using OS, platform, and database tools
- configure replication
- create an Availability Group
- configure auto-failover groups
- integrate a database into an Availability Group
- configure quorum options for a Windows Server Failover Cluster
- configure an Availability Group listener

7. Perform Administration by Using T-SQL
7.1. Examine system health
- evaluate database health using DMVs
- evaluate server health using DMVs
- perform database consistency checks by using DBCC

7.2. Monitor database configuration by using T-SQL
- assess proper database autogrowth configuration
- report on database free space
- review database configuration options

7.3. Perform backup and restore a database by using T-SQL
- prepare databases for Always On Availability Groups
- perform transaction log backup
- perform restore of user databases
- perform database backups with options

7.4. Manage authentication by using T-SQL
- manage certificates
- manage security principals

7.5. Manage authorization by using T-SQL
- configure permissions for users to access database objects
- configure permissions by using custom roles