WIXLIB

SQL Server 2017Administration Inside OutSecond EditionWilliam AssafRandolph WestSven AeltermanMindy Curnutt

Published with the authorization of Microsoft Corporation by:Pearson Education, Inc.Copyright 2018 by Pearson Education Inc.All rights reserved. Printed in the United States of America. This publication is protected by copyright, andpermission 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. Forinformation regarding permissions, request forms, and the appropriate contacts within the Pearson EducationGlobal Rights & Permissions Department, please visit www.pearsoned.com/permissions/. No patent liability isassumed with respect to the use of the information contained herein. Although every precaution has been takenin the preparation of this book, the publisher and author assume no responsibility for errors or omissions. Nor isany liability assumed for damages resulting from the use of the information contained herein.ISBN-13: 978-1-5093-0521-6ISBN-10: 1-5093-0521-1Library of Congress Control Number: 2017961300Printed and bound in the United States of America.1 18TrademarksMicrosoft and the trademarks listed at https://www.microsoft.com on the “Trademarks” webpage are trademarksof the Microsoft group of companies. All other marks are property of their respective owners.Warning and DisclaimerEvery effort has been made to make this book as complete and as accurate as possible, but no warranty or fitnessis implied. The information provided is on an “as is” basis. The authors, the publisher, and Microsoft Corporationshall have neither liability nor responsibility to any person or entity with respect to any loss or damages arisingfrom the information contained in this book or programs accompanying it.Special SalesFor information about buying this title in bulk quantities, or for special sales opportunities (which may includeelectronic versions; custom cover designs; and content particular to your business, training goals, marketing focus, orbranding interests), please contact our corporate sales department at corpsales@pearsoned.com or (800) 382-3419.For government sales inquiries, please contact governmentsales@pearsoned.com.For questions about sales outside the U.S., please contact intlcs@pearson.com.Editor-in-Chief: Greg WiegandAcquisitions Editor: Trina MacDonaldDevelopment Editor: Mark RenfrowTechnical Editor: Louis DavidsonManaging Editor: Sandra SchroederSenior Project Editor: Tracey CroomEditorial Production: Octal Publishing, Inc.Copy Editor: Octal Publishing, Inc.Indexer: Octal Publishing, Inc.Proofreader: Octal Publishing, Inc.Cover Designer: Twist Creative, Seattle

To David IIIfor inspiring and enabling STEM careers for many, including my own.—William AssafTo Marinus and Trixiefor putting up with the lies of “almost done,” and sharing my lap with a MacBook.—Randolph WestTo Ebony, Edward, and Sofiain recognition of their sacrifices, support, and endless love.—Sven AeltermanTo ChrisFor believing in me more than I even believed in myself.—Mindy Curnutt

This page intentionally left blank

Contents at a glanceChapter 1Getting started with SQL Server tools. . . . . . . . . . . 1Chapter 8Understanding and designing tables . . . . . . . . 333Chapter 2Introducing database server components . . . . 45Chapter 9Performance tuning SQL Server. . . . . . . . . . . . . 383Chapter 3Designing and implementing a databaseinfrastructure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79infrastructureChapter 10Understanding and designing indexes. . . . . . . 429Chapter 4Provisioning databases . . . . . . . . . . . . . . . . . . . . . 127Chapter 11Developing, deploying, and managing datarecovery. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 459Chapter 5Provisioning Azure SQL Database . . . . . . . . . . . 197Chapter 12Implementing high availability and disasterrecovery. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 493Chapter 6Administering security and permissions . . . . . 241Chapter 13Managing and monitoring SQL Server. . . . . . . 557Chapter 7Securing the server and its data. . . . . . . . . . . . . 291Chapter 14Automating SQL Server administration . . . . . . 607v

This page intentionally left blank

Table of contentsForeword . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xviiIntroduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xixWho this book is for . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xixAssumptions about you . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xixHow this book is organized . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxAbout the companion content . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiiAcknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiiSupport and feedback . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxivErrata & support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxivStay in touch . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxivChapter 1Getting started with SQL Server tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1SQL Server setup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1Installing SQL Server by using the Installation Center . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2Planning before an upgrade or installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3Installing or upgrading SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6Tools and services installed with the SQL Server Database Engine . . . . . . . . . . . . . . . . . . . . . . . . 7Machine Learning Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7Data Quality Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7Command-line interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9SQL Server Configuration Manager . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11Performance and reliability monitoring tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12Database Engine Tuning Advisor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12Extended events . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13Management data warehouse . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15SQL Server Reporting Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19Report Services Configuration Manager . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20vii