Exam Ref 70-765 Provisioning SQL Databases

460 Pages·2017·19.472 MB·English

by  Ford, Christopher

||||||||||||||||||||
||||||||||||||||||||
||||||||||||||||||||
||||||||||||||||||||
Exam Ref 70-765 Provisioning SQL
Databases
Joseph D’Antoni
Scott Klein
||||||||||||||||||||
||||||||||||||||||||
Exam Ref 70-765 Provisioning SQL Databases
Published with the authorization of Microsoft Corporation by: Pearson Education,
Inc.
Copyright © 2018 by Pearson Education
All rights reserved. Printed in the United States of America. This publication is
protected by copyright, and permission must be obtained from the publisher prior to any
prohibited reproduction, storage in a retrieval system, or transmission in any form or by
any means, electronic, mechanical, photocopying, recording, or likewise. For
information regarding permissions, request forms, and the appropriate contacts within
the Pearson Education Global Rights & Permissions Department, please visit
www.pearsoned.com/permissions/. No patent liability is assumed with respect to the
use of the information contained herein. Although every precaution has been taken in the
preparation of this book, the publisher and author assume no responsibility for errors or
omissions. Nor is any liability assumed for damages resulting from the use of the
information contained herein.
ISBN-13: 978-1-5093-0381-6
ISBN-10: 1-5093-0381-2
Library of Congress Control Number: 2017953262
1 17
Trademarks
Microsoft and the trademarks listed at https://www.microsoft.com on the “Trademarks”
webpage are trademarks of the Microsoft group of companies. All other marks are
property of their respective owners.
Warning and Disclaimer
Every effort has been made to make this book as complete and as accurate as possible,
but no warranty or fitness is implied. The information provided is on an “as is” basis.
The authors, the publisher, and Microsoft Corporation shall have neither liability nor
responsibility to any person or entity with respect to any loss or damages arising from
the information contained in this book or programs accompanying it.
Special Sales
For information about buying this title in bulk quantities, or for special sales
opportunities (which may include electronic versions; custom cover designs; and
content particular to your business, training goals, marketing focus, or branding
interests), please contact our corporate sales department at [e-mail]
or [phone].
||||||||||||||||||||
||||||||||||||||||||
For government sales inquiries, please contact [e-mail].
For questions about sales outside the U.S., please contact [e-mail].
Editor-in-Chief Greg Wiegand
Acquisitions Editor Trina MacDonald
Development Editor Troy Mott
Managing Editor Sandra Schroeder
Senior Project Editor Tracey Croom
Editorial Production Backstop Media
Copy Editor Christina Rudloff
Indexer Julie Grady
Proofreader Christina Rudloff
Technical Editor Thomas LaRock
Cover Designer Twist Creative, Seattle
||||||||||||||||||||
||||||||||||||||||||
Contents at a glance
Introduction
Important: How to use this book to study for the exam
CHAPTER 1  Implement SQL in Azure
CHAPTER 2  Manage databases and instances
CHAPTER 3  Manage Storage
Index
||||||||||||||||||||
||||||||||||||||||||
Contents
Introduction
Organization of this book
Microsoft certifications
Acknowledgments
Microsoft Virtual Academy
Quick access to online references
Errata, updates, & book support
We want to hear from you
Stay in touch
Important: How to use this book to study for the exam
Chapter 1  Implement SQL in Azure
Skill 1:1: Deploy a Microsoft Azure SQL Database
Choose a service tier
Create servers and databases
Create a sysadmin account
Configure elastic pools
Skill 1:2: Plan for SQL Server installation
Plan for an IaaS or on-premises SQL Server deployment
Select the appropriate size for a virtual machine
Plan storage pools based on performance requirements
Evaluate best practices for installation
Design a storage layout for a SQL Server virtual machine
Skill 1:3: Deploy SQL Server instances
Deploy a SQL Server instance in IaaS and on-premises
Manually install SQL Server on an Azure Virtual Machine
Provision an Azure Virtual Machine to host a SQL Server instance
Automate the deployment of SQL Server Databases
Deploy SQL Server by using templates
||||||||||||||||||||
||||||||||||||||||||
Skill 1:4: Deploy SQL Server databases to Azure virtual machines
Migrate an on-premises SQL Server Database to an
Azure virtual machine
Generate benchmark data for performance needs
Perform performance tuning on Azure IaaS
Support availability sets in Azure
Thought experiment
Thought experiment answers
Chapter summary
Chapter 2  Manage databases and instances
Skill 2.1: Configure secure access to Microsoft Azure SQL databases
Configure firewall rules
Configure Always Encrypted for Azure SQL Database
Configure Dynamic Data Masking
Configure Transparent Data Encryption
Skill 2.2: Configure SQL Server performance settings
Configure database performance settings
Configure max server memory
Configure the database scope
Configure operators and alerts
Skill 2.3: Manage SQL Server instances
Manage files and filegroups
Create databases
Manage system database files
Configure TempDB
Thought Experiment
Thought experiment answers
Chapter summary
Chapter 3  Manage Storage
Skill 3.1: Manage SQL Storage
||||||||||||||||||||
||||||||||||||||||||
Manage SMB file shares
Manage stretch databases
Configure Azure storage
Change service tiers
Review wait statistics
Manage storage pools
Recover from failed storage
Skill 3.2: Perform database maintenance
Monitor DMVs
Maintain indexes
Automate maintenance tasks
Update statistics
Verify database integrity
Recover from database corruption
Thought experiment
Thought experiment answers
Chapter summary
Index
What do you think of this book? We want to hear from you!
Microsoft is interested in hearing your feedback so we can continually improve our
books and learning resources for you. To participate in a brief online survey, please
visit:
https://aka.ms/tellpress
||||||||||||||||||||
||||||||||||||||||||
Introduction
This book contains three chapters to define and detail the objectives of the Microsoft
70-765 exam. The content contained in this publication covers what you should expect
to see on the exam, but you should have a solid working knowledge of SQL Server and
Azure skills. It is recommended to concentrate on one chapter at a time as you study the
materials contained in this guide. At the end of each chapter you will find a thought
experiment that you should complete. Complete the questions and review the answers
for each experiment to test your knowledge of the subject material.
The exam reference series covers a high level of knowledge that you are expected to
know in regards to the exam by covering why topics and “how to” processes with tasks
allowing you to fully understand a topic and its use with the product in a working
environment. The exam reference series makes the assumption you have some practical
experience in the subject material through regular use of SQL Server, or possibly a
previous version of the product. To be successful in taking the exam, you should be able
to plan and architect Azure SQL Database, SQL Server in Azure IaaS, and SQL Server
on-premises based solutions.
There are specific walkthroughs in different areas of the book, especially in new
feature topic areas. There are numerous notes and links to external material so you can
deep dive into additional subjects that will enable you to gain a more in depth
understanding of features of SQL Server and allow you to obtain a better understanding
of the subject material.
This book covers all of the objectives of the exam, however it may not cover every
exam question. Only the Microsoft exam team knows the exam questions. Exam
questions are regularly updated, so this book should be considered a supplement to real
use experience of SQL Server, and not a complete comprehensive guide to every exam
question. This edition of the book covers Azure and SQL Server as of mid-2017. As
Azure SQL Database, SQL Server and Azure IaaS evolve, be sure to check the exam
objectives to check for any changes or new version related information.
If you master the material in this book, coupled with the external links provided, and
use the product to gain real world experience, you should have a recipe for success in
your quest for Microsoft certification. Good luck on your goal!
Organization of this book
This book is organized by the “Skills measured” list published for the exam. The “Skills
measured” list is available for each exam on the Microsoft Learning website:
https://aka.ms/examlist. Each chapter in this book corresponds to a major topic area in
||||||||||||||||||||
||||||||||||||||||||
the list, and the technical tasks in each topic area determine a chapter’s organization. If
an exam covers six major topic areas, for example, the book will contain six chapters.
Microsoft certifications
Microsoft certifications distinguish you by proving your command of a broad set of
skills and experience with current Microsoft products and technologies. The exams and
corresponding certifications are developed to validate your mastery of critical
competencies as you design and develop, or implement and support, solutions with
Microsoft products and technologies both on-premises and in the cloud. Certification
brings a variety of benefits to the individual and to employers and organizations.
More Info All Microsoft Certifications
For information about Microsoft certifications, including a full list of available
certifications, go to https://www.microsoft.com/learning.
Acknowledgments
Joseph D’Antoni I would like to thank my wife Kelly and my team at Denny Cherry and
Associates consulting (Denny, John, Kerry, and Monica) for their help and patience with
this project.
Scott Klein When writing the acknowledgments, I always struggle with who to list first
because there are a handful of people that have played a huge role in this and they all
deserve to be at the top of the list. However, having said that, I would like to thank Joey
D’Antoni for making the initial connection and getting this whole thing started for me.
A very close second (and third) are the two individuals who not only brought me on
board for this project but were also very patient while I jumped in; Trina MacDonald
and Troy Mott. Thank you both for this opportunity.
Next comes the always amazing Tom LaRock, a good friend of mine who provided
amazing and very appreciated technical feedback. Tom has reviewed a couple of my
other books so when I heard he was the technical reviewer for this, there was an
element of both excitement and “oh crap,” because I knew Tom would keep me honest,
but at the same time he’d have a LOT of feedback, which I don’t mind at all.
Lastly, my family. Thank you for letting me disappear for a few weeks.
Microsoft Virtual Academy
Build your knowledge of Microsoft technologies with free expert-led online training