Exam Ref 70-764 Administering a SQL Database Infrastructure

Exam Ref 70-764 Administering a SQL Database Infrastructure

by Victor Isakov
Released October 2017
Publisher(s): Microsoft Press
ISBN: 9781509303885

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Prepare for Microsoft Exam 70-764—and help demonstrate your real-world mastery of skills for database administration. This exam is intended for database administrators charged with installation, maintenance, and configuration tasks. Their responsibilities also include setting up database systems, making sure those systems operate efficiently, and regularly storing, backing up, and securing data from unauthorized access.

Focus on the expertise measured by these objectives:

  • Configure data access and auditing
  • Manage backup and restore of databases
  • Manage and monitor SQL Server instances
  • Manage high availability and disaster recovery

This Microsoft Exam Ref:

  • Organizes its coverage by exam objectives
  • Features strategic, what-if scenarios to challenge you
  • Assumes you have working knowledge of database installation, configuration, and maintenance tasks. You should also have experience with setting up database systems, ensuring those systems operate efficiently, regularly storing and backing up data, and securing data from unauthorized access.

About the Exam

Exam 70-764 focuses on skills and knowledge required for database administration.

About Microsoft Certification

Passing both Exam 70-764 and Exam 70-765 (Provisioning SQL Databases) earns you credit toward an MCSA: SQL 2016 Database Administration certification. See full details at: microsoft.com/learning

Table of contents

  1. Cover
  2. Title Page
  3. Copyright Page
  4. Contents at a glance
  5. Contents
  6. Introduction
    1. Organization of this book
    2. Microsoft certifications
    3. Acknowledgments
    4. Microsoft Virtual Academy
    5. Quick access to online references
    6. Errata, updates, & book support
    7. We want to hear from you
    8. Stay in touch
  7. Important: How to use this book to study for the exam
  8. Chapter 1. Configure data access and auditing
    1. Skill 1.1: Configure encryption
      1. Implement column-level encryption
      2. Implement Always Encrypted
      3. Configure transparent data encryption
      4. Implement backup encryption
      5. Configure encryption for connections
      6. Troubleshoot encryption errors
    2. Skill 1.2 Configure data access and permissions
      1. Create and maintain users
      2. Create and maintain custom roles
      3. Manage database object permissions
      4. Configure row-level security
      5. Configure dynamic data masking
      6. Configure user options for Azure SQL Database
    3. Skill 1.3: Configure auditing
      1. Configure an audit on SQL Server
      2. Query the SQL Server audit log
      3. Manage a SQL Server audit
      4. Configure an Azure SQL Database audit
      5. Analyze audit logs and reports from Azure SQL Database
    4. Thought experiment
    5. Thought experiment answers
    6. Chapter summary
  9. Chapter 2. Manage backup and restore of databases
    1. Skill 2.1: Develop a backup strategy
      1. Design a backup strategy
      2. Back up databases
      3. Back up VLDBs
      4. Manage transaction log backups
      5. Configure backup automation
    2. Skill 2.2 Restore databases
      1. Design a restore strategy
      2. Restore a database
      3. Perform piecemeal restores
      4. Perform page recovery
      5. Perform point-in-time recovery
      6. Restore a filegroup
      7. Develop a plan to automate and test restores
    3. Skill 2.3 Manage database integrity
      1. Implement database consistency checks
      2. Identify database corruption
      3. Recover from database corruption
    4. Thought experiment
    5. Thought experiment answers
    6. Chapter summary
  10. Chapter 3. Manage and monitor SQL Server instances
    1. Skill 3.1: Monitor database activity
      1. Monitor current sessions
      2. Identify sessions that cause blocking activity
      3. Identify sessions that consume tempdb resources
      4. Configure the data collector
    2. Skill 3.2 Monitor queries
      1. Manage the Query Store
      2. Configure Extended Events and trace events
      3. Identify problematic execution plans
      4. Troubleshoot server health using Extended Events
    3. Skill 3.3 Manage indexes
      1. Identify and repair index fragmentation
      2. Identify and create missing indexes
      3. Identify and drop underutilized indexes
      4. Manage existing columnstore indexes
    4. Skill 3.4 Manage statistics
      1. Identify and correct outdated statistics
      2. Implement Auto Update Statistics
      3. Implement statistics for large tables
    5. Skill 3.5 Monitor SQL Server instances
      1. Configure database mail
      2. Create and manage operators
      3. Create and manage SQL Agent alerts
      4. Define custom alert actions
      5. Define failure actions
      6. Configure policy based management
      7. Identify available space on data volumes
      8. Identify the cause of performance degradation
    6. Thought experiment
    7. Thought experiment answers
    8. Chapter summary
  11. Chapter 4. Manage high availability and disaster recovery
    1. Skill 4.1: Design a high availability solution
    2. Skill 4.2: Design a disaster recovery solution
    3. Skill 4.3: Implement log shipping
      1. Architect log shipping
      2. Configure log shipping
      3. Monitor log shipping
    4. Skill 4.4: Implement Availability Groups
      1. Architect Availability Groups
      2. Configure Windows clustering
      3. Create an Availability Group
      4. Configure read-only routing
      5. Monitor Availability Groups
      6. Manage failover
      7. Create Distributed Availability Group
    5. Skill 4.5: Implement failover clustering
      1. Architect failover clustering
      2. Configure failover clustering
      3. Manage Shared Disks
      4. Configure Cluster Shared Volumes
    6. Thought experiment
    7. Thought experiment answers
    8. Chapter summary
  12. Index
  13. About the author
  14. Hear about it first
  15. Survey
  16. Code Snippets

Product information

  • Title: Exam Ref 70-764 Administering a SQL Database Infrastructure
  • Author(s): Victor Isakov
  • Release date: October 2017
  • Publisher(s): Microsoft Press
  • ISBN: 9781509303885