70-764: Administering a SQL Server 2016 Database Infrastructure


Microsoft Course Demo

70-764: Administering a SQL Server 2016 Database Infrastructure

In this instructor-led, online training course, students will learn the knowledge and skills to administer a SQL server database infrastructure. Students will be taught how to authenticate and authorize users, protect data with encryption and auditing, backup and restore SQL Server, configure security for the SQL Server agent, and more.

Exam Number: 70-764


Doug Perez, MCSA

Course Information

Skills Learned

After completing this course, students will be able to:

  • Authenticate and authorize users
  • Assign server and database roles
  • Authorize users to access resources
  • Protect data with encryption and auditing
  • Describe recovery models and backup strategies
  • Backup SQL Server databases
  • Restore SQL Server databases
  • Automate database management
  • Configure security for the SQL Server agent
  • Manage alerts and notifications
  • Managing SQL Server using PowerShell
  • Trace access to SQL Server
  • Monitor a SQL Server infrastructure
  • Troubleshoot a SQL Server infrastructure
  • Import and export data

Who Should Attend This Course

This instructor led, online training course is for those who administer and maintain SQL Server databases.


We recommend that students have:

  • Basic knowledge of the Microsoft Windows operating system and its core functionality

Course Outline

Configuring 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

Developing a backup strategy

  • Configure database recovery models
  • Managing transaction log backups
  • Back up very large databases
  • Configure alerting for failed backups
  • Back up databases to Azure
  • Configure backup automation

Monitoring database activity

  • Monitor current sessions
  • Identify sessions that cause blocking activity
  • Identify sessions that consume tempdb resources
  • Configure the data collector

Monitoring Queries

  • Manage the Query Store
  • Configure extended events and trace events
  • Identify problematic execution plans
  • Troubleshoot server health using extended events

Managing indexes

  • Identify and repair index fragmentation
  • Identify and create missing indexes
  • Identify and drop underutilized indexes
  • Manage existing columnstore indexes

Managing statistics

  • Identify and correct outdated statistics
  • Implement auto update statistics
  • Implement statistics for large tables

Monitoring queries

  • Create and manage operators
  • Create and manage SQL Agent alerts
  • Define custom alert actions and failure actions
  • Configure database mail
  • Configure Policy-Based Management
  • Identify available space on data volumes
  • Identify the cause of performance degradation

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 (CSV)

Monitoring database activity

  • SQL edition capability map
  • Replication definitions
  • Publishing industry metaphor concepts
  • What can be replicated?
  • How replication works
  • Replication use cases
  • Typical transactional replication use case

Replication setup walkthrough
Monitoring replication
Performance tuning


Are you a student and need support?

Don’t see the class you’re looking for?

Get a demo or pricing!

Call (800) 850-9932 or submit this form to get pricing for you or your team.

We absolutely love being able to tailor the StormWind training experience to you and your goals. Simply provide us with a little bit of upfront info and we’ll get to work on customizing learning packages for you—at a price you just can’t resist.