Exam 70-764: Administering a SQL Database Infrastructure

Administering a SQL Database Infrastructure (70-764)

Microsoft’s SQL Server platform is one of the top three relational database products in the world’s enterprise marketplace, which means that those with SQL Server skills are in high demand everywhere. Administering a SQL Database Infrastructure (70-764) is designed for those who want to become a SQL Server administrator. After this exam, professionals who administer and maintain SQL Server databases and who develop applications that deliver content from SQL Server databases will gain the knowledge and skills to administer a SQL server database infrastructure.

Who can take this exam?

This exam is intended for database professionals who perform installation, maintenance, and configuration tasks. Other responsibilities include setting up database systems, making sure those systems operate efficiently, and regularly storing, backing up, and securing data from unauthorized access.

Learning Objectives

Microsoft provides a learning path for Administering a SQL Database Infrastructure (70-764) exam which is specifically designed for the candidates who are seeking to build a career as Microsoft Certified Solutions Associate (MCSA): SQL 2016 Database Administration. The objectives for (70-764) exam are as follows:

Configure data access and auditing (20–25%)
Manage backup and restore of databases (20–25%)
Manage and monitor SQL Server instances (35–40%)
Manage high availability and disaster recovery (20–25%)

Learning Path

Microsoft provides various certifications in several fields. However, Administering a SQL Database Infrastructure (70-764) Exam and Provisioning SQL Databases (70-765) are paths to become MCSA: SQL 2016 Database Administration.

Exam Details

Explaining the format of Administering a SQL Database Infrastructure (70-764) exam is the most important step. The exam includes 40-60 number of questions. The questions will appear in the form of multiple-choice. The candidate will get 120 minutes to complete the exam, in total, they get 150 minutes. The exam is available in various languages such as English, Japanese, Chinese (Simplified), Korean. The candidate has to pay $165 USD as the examination fee.

Scheduling the Exam

In order to appear in Microsoft exam, the candidate has to schedule the exam and make themselves register with Microsoft. The candidate can schedule their exam with the Pearson VUE.

Detailed Course Outline

The Administering a SQL Database Infrastructure (70-764) exam includes its objectives and sub-topics in it. The detailed course outline is mentioned below:

Configure data access and auditing (20–25%)

Configure encryption

implement cell-level encryption (Microsoft Documentation: Encrypt a Column of Data)
implement Always Encrypted (Microsoft Documentation: Always Encrypted)
implement backup encryption (Microsoft Documentation: Backup Encryption)
configure transparent data encryption (Microsoft Documentation: Transparent Data Encryption (TDE))
configure encryption for connections (Microsoft Documentation: Enable encrypted connections to the Database Engine)
troubleshoot encryption errors

Configure data access and permissions

manage database object permissions (Microsoft Documentation: Permissions (Database Engine))
create and maintain users
create and maintain custom roles (Microsoft Documentation: Create a Server Role)
configure user options for Azure SQL Database (Microsoft Documentation: Authorize database access to SQL Database)
configure row-level security (Microsoft Documentation: Row-Level Security)
configure dynamic data masking (Microsoft Documentation: Dynamic Data Masking)

Configure auditing

configure an audit on SQL Server (Microsoft Documentation: SQL Server Audit (Database Engine))
query the SQL Server audit log (Microsoft Documentation: View a SQL Server Audit Log)
manage a SQL Server audit (Microsoft Documentation: SQL Server Audit (Database Engine))
configure an Azure SQL Database audit (Microsoft Documentation: SQL Server Audit (Database Engine))
analyze audit logs and reports from Azure SQL Database (Microsoft Documentation: Auditing for Azure SQL Database)

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

Develop a backup strategy

back up very large databases (Microsoft Documentation: Back Up and Restore of SQL Server Databases)
configure alerting for failed backups
back up databases to Azure (Microsoft Documentation: SQL Server Backup in Azure VMs)
manage transaction log backups (Microsoft Documentation: Transaction Log Backups (SQL Server))
configure database recovery models (Microsoft Documentation: View or Change the Recovery Model of a Database (SQL Server))
configure backup automation

Restore databases

perform piecemeal restores (Microsoft Documentation: Piecemeal Restores (SQL Server))
perform page recovery (Microsoft Documentation: Restore Pages (SQL Server))
perform point-in-time recovery (Microsoft Documentation: Recover using automated database backups)
restore file groups (Microsoft Documentation: Restore Files and Filegroups (SQL Server))
develop a plan to automate and test restores

Manage database integrity

Microsoft Documentation: Check Database Integrity Task (Maintenance Plan)

implement database consistency checks
identify database corruption
recover from database corruption

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

Monitor database activity

Microsoft Documentation: Database-Level Roles

monitor current sessions
identify sessions that cause blocking activity
identify sessions that consume tempdb resources (Microsoft Documentation: Monitoring Microsoft Azure SQL Database and Azure SQL Managed Instance performance)
configure the data collector (Microsoft Documentation: Data Collection)

Monitor queries

manage the Query Store (Microsoft Documentation: Best practices with Query Store, Monitoring performance by using the Query Store)
configure Extended Events and trace events (Microsoft Documentation: Extended events in SQL Server)
identify problematic execution plans (Microsoft Documentation: Automatic tuning)
troubleshoot server health using Extended Events (Microsoft Documentation: Use the system_health Session)

Manage indexes

Microsoft Documentation: Indexes

identify and repair index fragmentation (Microsoft Documentation: Resolve index fragmentation by reorganizing or rebuilding indexes)
identify and create missing indexes
identify and drop underutilized indexes
manage existing column store indexes (Microsoft Documentation: Columnstore indexes: Overview)

Manage statistics

Microsoft Documentation: Statistics

identify and correct outdated statistics
implement Auto Update Statistics
implement statistics for large tables

Monitor SQL Server instances

Microsoft Documentation: Work with multiple versions and instances of SQL Server

create and manage operators (Microsoft Documentation: Create an Operator)
create and manage SQL Agent alerts (Microsoft Documentation: Alerts)
define custom alert actions
define failure actions
configure database mail (Microsoft Documentation: Configure Database Mail)
configure Policy-Based Management (Microsoft Documentation: Administer Servers by Using 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 and monitor log shipping (Microsoft Documentation: About Log Shipping)

Implement AlwaysOn Availability Groups

configure Windows clustering
create an availability group (Microsoft Documentation: CREATE AVAILABILITY GROUP)
configure read-only routing (Microsoft Documentation: Configure read-only routing for an Always On availability group)
manage failover (Microsoft Documentation: Manage Failover Clusters with Windows Admin Center)
create distributed availability groups (Microsoft Documentation: Create distributed availability group on failover cluster instances)

Implement failover cluster instances

manage shared disks and configure cluster shared volumes (Microsoft Documentation: SQL Server Failover Cluster Installation)

Exam Policies

While preparing for the Microsoft exam, the candidate should visit the Microsoft official site and refer o the Microsoft exam policies and try to understand them. The Microsoft policies provide information regarding basic information such as policies about retaking the exam, scheduling the exam, candidate appeal process, etc.

Administering a SQL Database Infrastructure (70-764) FAQ

Step-by-step Preparation Guide

Here is a step-by-step preparation guide for successfully passing the Administering a SQL Database Infrastructure (70-764) exam.

Microsoft Learning Platform

Microsoft offers various learning paths, the candidate should visit the official website of Microsoft. The candidate can find every possible information on the official site. For this exam, the candidate will find many learning paths and documentations. Finding the relatable content on the Microsoft website is quite an easy task.

Microsoft Documentation

Documentations are an important learning resource while preparing for Administering a SQL Database Infrastructure (70-764). The candidate will find documentation on every topic relating to the particular exam. This step is very valuable in preparing for Implementing an Azure Data Solution exam.

Microsoft Online Training

Microsoft also offers online training for many certifications. For Administering a SQL Database Infrastructure (70-764) exam, there are some online training courses available which are as follows:

Instructor-Led Training

The training programs that the Micorosft provide itself is available on their website. The instructor-led training is an essential resource in order to prepare for the exam like 70-764. The candidate can find the instructor-led training on the page of the particular exam on the Microsoft website. There are various training courses available prior to one exam. The following is the training program offered by Microsoft.

Course 20764C: Administering a SQL Database Infrastructure

Module 1: SQL Server Security
Module 2: Assigning Server and Database Roles
Module 3: Authorizing Users to Access Resources
Module 4: Protecting Data with Encryption and Auditing
Module 5: Recovery Models and Backup Strategies
Module 6: Backing Up SQL Server Databases
Module 7: Restoring SQL Server 2016 Databases
Module 8: Automating SQL Server Management
Module 9: Configuring Security for SQL Server Agent
Module 10: Monitoring SQL Server with Alerts and Notifications
Module 11: Introduction to Managing SQL Server by using PowerShell
Module 12: Tracing Access to SQL Server with Extended events
Module 13: Monitoring SQL Server
Module 14: Troubleshooting SQL Server
Module 15: Importing and Exporting Data

Books and Guides

Candidate should study some books and guides which will definitely help them to gather knowledge about the particular exam. Microsoft itself recommends the following:

Join a Study Group

For passing the exam like Administering a SQL Database Infrastructure (70-764), the candidate needs to get and share knowledge. So, we are suggesting you join some study where you can discuss the concepts with the people who have the same goal. This will lead the candidate throughout their preparation.

Practice Test

The most important step is to try your hands on the practice test. Practice tests are the one which ensures the candidate about their preparation. There are many practice tests are available on the internet nowadays, the candidate can choose whichever they want. The practice test is very beneficial in preparing the exam like 70-764.