O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Exam Ref 70-764 Administering a SQL Database Infrastructure

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