MCSA: SQL 2016 Database Administration – Skills

Measured

NOTE: The bullets that appear below each of the skills measured are intended to illustrate how
we are assessing that skill. This list is not definitive or exhaustive.

NOTE: In most cases, exams do NOT cover preview features, and some features will only be
added to an exam when they are GA (General Availability).

Exam 70-764: Administering a SQL Database Infrastructure

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

Exam 70-765: Provisioning SQL Databases

Implement SQL in Azure (40–45%)

Deploy a Microsoft Azure SQL Database

 choose a service tier, create servers and databases, create a sysadmin account, configure
elastic pools

Plan for SQL Server installation

 plan for an IaaS or on-premises deployment, select the appropriate size for a virtual
machine, plan storage pools based on performance requirements, evaluate best practices
for installation, design a storage layout for a SQL Server virtual machine

Deploy SQL Server instances

 deploy a SQL Server instance in IaaS and on-premises, manually install SQL Server on an
Azure Virtual Machine, provision an Azure Virtual Machine to host a SQL Server instance,
automate the deployment of SQL Server databases, deploy SQL Server by using
templates

Deploy SQL Server databases to Azure virtual machines

 migrate an on-premises SQL Server database to an Azure virtual machine, generate

benchmark data for performance needs, perform performance tuning on Azure IaaS,
support availability sets in Azure

Manage databases and instances (30-35%)

Configure secure access to Microsoft Azure SQL Databases

  configure firewall rules, configure Always Encrypted for Azure SQL Database, configure
cell-level encryption, configure dynamic data masking, configure transparent data
encryption (TDE)

Configure SQL Server performance settings

 configure database performance settings, configure max server memory, configure the
database scope, configure operators and alerts

Manage SQL Server instances

 create databases, manage files and file groups, manage system database files, configure
tempdb

Manage Storage (30–35%)

Manage SQL Storage

 manage SMB file shares, manage stretch databases, configure Azure storage, change
service tiers, review wait statistics, manage storage pools, recover from failed storage

Perform database maintenance

 monitor DMVs, maintain indexes, automate maintenance tasks, update statistics, verify
database integrity, recover from database corruption