Joseph D’Antoni - SQL Server 2019 Administration Inside Out

SQL Server 2019 Administration

Randolph West

Melody Zacharias

William Assaf

Sven Aelterman

Louis Davidson

Joseph DAntoni

SQL Server 2019 Administration Inside Out

Published with the authorization of Microsoft Corporation by:

Pearson Education, Inc.

Copyright 2020 by Pearson Education, Inc.

All rights reserved. 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.pearson.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-0-13-556108-9

ISBN-10: 0-13-556108-6

Library of Congress Control Number: 2020930782

ScoutAutomatedPrintCode

Trademarks

Microsoft and the trademarks listed at http://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 author, 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 from the use of the 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 or (800) 382-3419.

For government sales inquiries, please contact .

For questions about sales outside the U.S., please contact .

Editor-in-Chief: Brett Bartow

Executive Editor: Loretta Yates

Development Editor: Troy Mott

Managing Editor: Sandra Schroeder

Senior Project Editor: Tracey Croom

Copy Editor: Liv Bainbridge

Editorial Assistant: Cindy Teeters

Proofreader: Liv Bainbridge

Cover Designer: Twist Creative, Seattle

Compositor: MAP Systems, Bangalore

Graphics: Vived Graphics

To Marinus (obviously), but also Larry Toothman, Robert Davis, and Ian van Schalkwyk, because I miss you.

Randolph West

To my husband Chris and daughters Jessica and Chloe for all your patience and understanding.

Melody Zacharias

To Christine from her biggest fan

William Assaf

In Memory of Erick Aelterman Your example of hard work and perseverance made this possible

Sven Aeleterman

To the rest of the writing and tech editing team. This edition of the book has been so much more of a joy due to everyones hard work and commitment to the project. I am very proud to have been chosen to not only tech-edit half of this book, but to also take charge of revising two chapters.

Louis Davidson

Id like to thank the SQL Server Product Group for their hard work on the product.

Joseph D'Antoni

To my dog Buster. Thanks for being patient and dealing with fewer dog park visits while I edited this book.

Meagan Longoria

Contents at a Glance

Chapter 1
Getting started with SQL Server tools

Chapter 2
Introducing database server components

Chapter 3
Designing and implementing an on-premises database infrastructure

Chapter 4
Installing and configuring SQL Server instances and features

Chapter 5
Installing and configuring SQL Server on Linux

Chapter 6
Provisioning and configuring SQL Server databases

Chapter 7
Understanding table features

Chapter 8
Maintaining and monitoring SQL Server

Chapter 9
Automating SQL Server administration

Chapter 10
Developing, deploying, and managing data recovery

Chapter 11
Implementing high availability and disaster recovery

Chapter 12
Administering security and permissions

Chapter 13
Protecting data through encryption, privacy and auditing

Chapter 14
Performance tuning SQL Server

Chapter 15
Understanding and designing indexes

Chapter 16
Designing and implementing hybrid and Azure database infrastructure

Chapter 17
Provisioning Azure SQL Database

Chapter 18
Provisioning Azure SQL Database managed instance

Chapter 19
Migrating to SQL Server solutions in Azure

Chapter 20
Leveraging big data and machine learning

Table of Contents

About the authors

Randolph West (they / them) is a technologist and Data Platform MVP from Calgary, Alberta, Canada, and hates writing in the third person because pronouns suck. Randolph is the founder of the Calgary Data User Group, and leader of the Calgary PASS User Group. You can see Randolph presenting at various conferences around the world, and acting on stage and screen. Randolph specializes in implementing best practices, performance tuning, disaster recovery, cloud migrations, software development, and yelling at Microsoft Word. If youre interested in becoming a public speaker, check out SpeakingMentors.com. Blog: bornsql.ca/blog. Twitter: @_randolph_west. Not to be trusted around chocolate.

Melody Zacharias has worked with SQL Server since version 7, and she is very passionate about all things data! As a Microsoft MVP Melody loves to share her passion with users around the world by teaching at events, working within the SQL community, and mentoring. When not working with data she loves to hike with her dog in the Canadian Rockies. You can find her on her blog at SQLMelody.com where she shares musings on how to make SQL sing, or sometimes just carry a tune, or on Twitter @SQLMelody.