Exam Ref 70-764 Administering a SQL Database Infrastructure

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. Title Page......Page 2 Copyright Page......Page 3 Contents at a glance......Page 6 Contents......Page 7 Organization of this book......Page 12 Microsoft Virtual Academy......Page 13 Stay in touch......Page 14 Important: How to use this book to study for the exam......Page 16 Chapter 1. Configure data access and auditing......Page 17 Skill 1.1: Configure encryption......Page 18 Implement column-level encryption......Page 19 Implement Always Encrypted......Page 28 Configure transparent data encryption......Page 43 Implement backup encryption......Page 49 Configure encryption for connections......Page 51 Troubleshoot encryption errors......Page 52 Create and maintain users......Page 54 Create and maintain custom roles......Page 64 Manage database object permissions......Page 66 Configure row-level security......Page 72 Configure dynamic data masking......Page 79 Configure user options for Azure SQL Database......Page 82 Configure an audit on SQL Server......Page 84 Query the SQL Server audit log......Page 90 Manage a SQL Server audit......Page 92 Configure an Azure SQL Database audit......Page 94 Analyze audit logs and reports from Azure SQL Database......Page 98 Thought experiment......Page 99 Thought experiment answers......Page 100 Chapter summary......Page 101 Chapter 2. Manage backup and restore of databases......Page 103 Design a backup strategy......Page 104 Back up databases......Page 110 Back up VLDBs......Page 131 Manage transaction log backups......Page 135 Configure backup automation......Page 159 Design a restore strategy......Page 204 Restore a database......Page 209 Perform piecemeal restores......Page 214 Perform page recovery......Page 222 Perform point-in-time recovery......Page 225 Restore a filegroup......Page 232 Develop a plan to automate and test restores......Page 233 Skill 2.3 Manage database integrity......Page 234 Implement database consistency checks......Page 235 Identify database corruption......Page 240 Recover from database corruption......Page 243 Thought experiment......Page 248 Thought experiment answers......Page 251 Chapter summary......Page 252 Chapter 3. Manage and monitor SQL Server instances......Page 254 Skill 3.1: Monitor database activity......Page 255 Monitor current sessions......Page 256 Identify sessions that cause blocking activity......Page 259 Identify sessions that consume tempdb resources......Page 263 Configure the data collector......Page 267 Manage the Query Store......Page 276 Configure Extended Events and trace events......Page 287 Identify problematic execution plans......Page 298 Troubleshoot server health using Extended Events......Page 302 Identify and repair index fragmentation......Page 304 Identify and create missing indexes......Page 308 Identify and drop underutilized indexes......Page 311 Manage existing columnstore indexes......Page 314 Skill 3.4 Manage statistics......Page 316 Identify and correct outdated statistics......Page 317 Implement Auto Update Statistics......Page 322 Implement statistics for large tables......Page 325 Configure database mail......Page 327 Create and manage operators......Page 336 Create and manage SQL Agent alerts......Page 338 Define custom alert actions......Page 341 Define failure actions......Page 342 Configure policy based management......Page 344 Identify available space on data volumes......Page 352 Identify the cause of performance degradation......Page 353 Thought experiment......Page 361 Thought experiment answers......Page 362 Chapter summary......Page 363 Chapter 4. Manage high availability and disaster recovery......Page 366 Skill 4.1: Design a high availability solution......Page 367 Skill 4.2: Design a disaster recovery solution......Page 372 Skill 4.3: Implement log shipping......Page 373 Architect log shipping......Page 374 Configure log shipping......Page 378 Monitor log shipping......Page 391 Skill 4.4: Implement Availability Groups......Page 394 Architect Availability Groups......Page 395 Configure Windows clustering......Page 409 Create an Availability Group......Page 415 Configure read-only routing......Page 437 Monitor Availability Groups......Page 440 Manage failover......Page 442 Create Distributed Availability Group......Page 447 Skill 4.5: Implement failover clustering......Page 449 Architect failover clustering......Page 450 Configure failover clustering......Page 457 Manage Shared Disks......Page 501 Configure Cluster Shared Volumes......Page 502 Thought experiment......Page 507 Thought experiment answers......Page 510 Chapter summary......Page 511 Index......Page 513 About the author......Page 539 Hear about it first......Page 540 Survey......Page 542 Code Snippets......Page 544