SQL Server DBA Exam 70-764

Posted on April 27, 2019 Updated on April 27, 2019

• Technology: SQL Server 2016/2017/2019
• Credit toward certification: MCSA
• Microsoft Site Link: Exam 70-764
  

Configure data access and auditing (20–25%)

• Configure encryption
  • Implement cell-level encryption
  • Implement Always Encrypted
  • Implement backup encryption
  • Configure transparent data encryption
  • Configure encryption for connections
  • Troubleshoot encryption errors
• Configure data access and permissions
  • Manage database object permissions
  • Create and maintain users
  • Create and maintain custom roles
  • Configure user options for Azure SQL Database
  • Configure row-level security
  • Configure dynamic data masking
• Configure auditing
  • Configure an audit on SQL Server
  • Query the SQL Server audit log
  • Manage a SQL Server audit
  • Configure an Azure SQL Database audit
  • Analyze audit logs and reports from Azure SQL Database

Manage backup and restore of databases (20–25%)

• Develop a backup strategy
  • Back up very large databases
  • Configure alerting for failed backups
  • Back up databases to Azure
  • Manage transaction log backups,
  • Configure database recovery models
  • Configure backup automation
• Restore databases
  • Perform piecemeal restores
  • Perform page recovery
  • Perform point-in-time recovery
  • Restore file groups
  • Develop a plan to automate and test restores
• Manage database integrity
  • Implement database consistency checks
  • Identify database corruption
  • Recover from database corruption

Manage and monitor SQL Server instances (35–40%)

• Monitor database activity
  • Monitor current sessions
  • Identify sessions that cause blocking activity
  • Identify sessions that consume tempdb resources
  • Configure the data collector
• Monitor queries
  • Manage the Query Store
  • Configure Extended Events and trace events
  • Identify problematic execution plans
  • Troubleshoot server health using Extended Events
• Manage indexes
  • Identify and repair index fragmentation
  • Identify and create missing indexes
  • Identify and drop underutilized indexes
  • Manage existing columnstore indexes
• Manage statistics
  • Identify and correct outdated statistics
  • Implement Auto Update Statistics
  • Implement statistics for large tables
• Monitor SQL Server instances
  • Create and manage operators,
  • Create and manage SQL Agent alerts
  • Define custom alert actions
  • Define failure actions
  • Configure database mail
  • Configure Policy-Based Management
  • Identify available space on data volumes
  • Identify the cause of performance degradation

Manage high availability and disaster recovery (20–25%)

• Implement log shipping
  • Configure log shipping
  • Monitor log shipping
• Implement AlwaysOn Availability Groups
  • Configure Windows clustering
  • Create an availability group
  • Configure read-only routing
  • Manage failover
  • Create distributed availability groups
• Implement Failover Cluster Instances
  • Manage shared disks
  • Configure cluster shared volumes