Kevin Kline - Pro Database Migration to Azure: Data Modernization for the Enterprise

Pro Database Migrationto AzureData Modernization for the EnterpriseKevin KlineDenis McDowellDustin DorseyMatt GordonForeword by Bob WardPro Database Migration to Azure: Data Modernization for the Enterprise Kevin Kline Dustin Dorsey Mount Juliet, TN, USA Murfreesboro, TN, USA Denis McDowell Matt Gordon Raleigh, NC, USA Lexington, KY, USA ISBN-13 (pbk): 978-1-4842-8229-8 ISBN-13 (electronic): 978-1-4842-8230-4 https://doi.org/10.1007/978-1-4842-8230-4 Copyright 2022 by Kevin Kline, Denis McDowell, Dustin Dorsey, Matt Gordon This work is subject to copyright. All rights are reserved by the Publisher, whether the whole or part of the material is concerned, specifically the rights of translation, reprinting, reuse of illustrations, recitation, broadcasting, reproduction on microfilms or in any other physical way, and transmission or information storage and retrieval, electronic adaptation, computer software, or by similar or dissimilar methodology now known or hereafter developed. Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, logo, or image we use the names, logos, and images only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are not identified as such, is not to be taken as an expression of opinion as to whether or not they are subject to proprietary rights. While the advice and information in this book are believed to be true and accurate at the date of publication, neither the authors nor the editors nor the publisher can accept any legal responsibility for any errors or omissions that may be made.

The publisher makes no warranty, express or implied, with respect to the material contained herein. Managing Director, Apress Media LLC: Welmoed Spahr Acquisitions Editor: Jonathan Gennick Development Editor: Laura Berendson Coordinating Editor: Jill Balzano Cover image photo by Ferdinand Sthr on Unsplash Distributed to the book trade worldwide by Springer Science+Business Media LLC, 1 New York Plaza, Suite 4600, New York, NY 10004. Phone 1-800-SPRINGER, fax (201) 348-4505, e-mail orders-ny@springer-sbm. com, or visit www.springeronline.com. Apress Media, LLC is a California LLC and the sole member (owner) is Springer Science + Business Media Finance Inc (SSBM Finance Inc). SSBM Finance Inc is a Delaware corporation.

For information on translations, please e-mail booktranslations@springernature.com; for reprint, paperback, or audio rights, please e-mail bookpermissions@springernature.com. Apress titles may be purchased in bulk for academic, corporate, or promotional use. eBook versions and licenses are also available for most titles. For more information, reference our Print and eBook Bulk Sales web page at http://www.apress.com/bulk-sales. Any source code or other supplementary material referenced by the author in this book is available to readers on GitHub. Printed on acid-free paper Love is what we have, against age and time and death, against all thepowers ranged to push us down.

You gave me so much a new hope, anew heart, a new future all far better than anything I had before. Idedicate this to you, Rachel, my jewel more precious than rubies.Kevin KlineDedicated to Antoinette, who has been my cheerleader and biggestsupporter, and to Christopher, Bella, Katherine, and Josh, whotolerated geek dad lessons for so many years.Denis McDowellDedicated to my wife Sarah and our three wonderful kids Zoey,Bennett, and Ellis who put up with me through the countless hours ittook to make this a reality.Dustin DorseyThis is dedicated to every member of #sqlfamily that has helped methrough the years. As technical professionals, I feel like we have aresponsibility to pay it forward to help those who come after us justas those who came before us helped us. I am immensely grateful for allwho have helped me through the years, and I hope my efforts to paythat forward have proved similarly helpful to others in our community.Matt GordonTable of Contents About the Authors xv About the Technical Reviewer xvii Acknowledgments xix Foreword xxi Chapter 1: The Azure SQL Data Platform 1 Azure Core Services and Concepts 2 Migration and Modernization 6 Migration and Modernization Strategies Made Simple 7 The Five Disciplines of Cloud Governance 9 Cautionary Tales 10 When the C-Suite Falls for the Hype 11 When Cost Is the Only Consideration 12 When Middle Managers Are Set Adrift 13 Vague Is As Vague Does 13 Fear: The Ultimate Motivator 13 Lessons from Market Research 14 The Benefits of Cloud Computing 15 Repatriation 16 Pilot Projects: Which to Choose or Avoid 18 The Skyscraper with No Architect 21 Worth Every Penny 21 Summary 25 v Table of ConTenTs Chapter 2: Planning Considerations and Analysis 27 Topics Covered in This Chapter 27 Scope Definition 28 Planning 29 Phase 1: Discovery 29 Discovery of the Database 30 Discovery of the Business and Its Requirements 31 Discovery of the Workload 32 Discovery Wrap-Up 34 Phase 2: Landing Zone Testing and Validation 35 Analysis and Testing 36 Availability and Uptime 37 Phase 3: Choose the Migration Strategy 38 Identifying and Configuring the Migration Landing Zone 44 Data Migration Assistant 44 Sizing the Cloud Systems 45 Adopting a Cloud Mindset 48 Analyzing Workloads 49 Workload Replay Options 49 Workload Replay Process 50 Monitoring for Workload Analysis 52 Validating the Data 53 Platform Capability Validation 54 High Availability Configurations 54 Disaster Recovery Scenarios 55 Backup Retention and Recovery Scenarios 55 Geo-replication and Multiregion Deployments 58 Migration Dry Run 59 Summary 60 vi Table of ConTenTs Chapter 3: Budgeting for an Azure Migration 63 Why Should You Care? 64 Does the Cloud Save You Money? 66 Getting Started Building a Budget 67 Ask for Help 68 Introducing the FLAT Method 69 Familiarity 70 Location 72 Architecture 73 Translation 74 Total Cost of Ownership Calculator 76 Azure Pricing Calculator 79 Important Calculator Considerations 83 Getting the Most from the Calculator 84 Big Scary Egress 85 Networking Cost Considerations 86 Reducing Your Azure Costs 87 Azure Hybrid Benefit and Bring Your Own License 87 Reconsider Architectural Decisions 92 Scheduled Shutdown and Startup 92 Autoscaling: Provision Only What You Need 93 Utilize Dev/Test Pricing 94 Azure Reservations 95 The Non-Azure Factors 98 Building an ROI 99 Summary 101 Chapter 4: Azure Cost Management 103 Azure Cost Management and Billing 103 Cost Management Concepts 106 vii Table of ConTenTs Planning 107 Visibility 109 Accountability 110 Optimizations 110 Azure Billing Entity Hierarchy 111 Azure Cost Management in the Portal 112 Cost Analysis 113 Getting the Most from Your Analysis 117 Proactive Performance Tuning 118 Budgets 120 Action Groups 124 Azure Advisor 125 Multicloud Tool 127 Connecting External Sources 128 Lowering Costs 130 Summary 132 Chapter 5: Service and Systems Monitoring 133 Monitoring and Observability 133 Observability Platforms 134 What to Collect 138 Metrics, Logs, and Traces 138 From Servers to Services 140 Baselines 142 Baselines vs Thresholds 142 Using Baselines to Build Smart Thresholds 145 Using Baselines to Right-Size Your Cloud Platform 147 Data Platform Monitoring in Microsoft Azure 148 Azure Monitor 148 Continuous Monitoring 150 Data Platform Monitoring Tools 151 Summary 155 viii Table of ConTenTs Chapter 6: Migrating Data and Code 157 Migrate Meaningful Data 158 Archive the Unneeded 159 Understand Business Requirements 160 Understand Usage Patterns 163 Considerations on Database Design 166 Migrate Meaningful Code 167 Data Migration Assistant 168 Finding Issues in Your Database Pre-migration 170 Baseline Testing 174 Remediation 177 Summary 178 Chapter 7: Team Success Factors 181 Business Value 181 Business Sponsorship 183 Support of the Team 185 Early Involvement 185 Encourage Growth 186 Dealing with Negative Energy 188 Promote Collaboration 189 Good Communication 192 Training for the Team 194 Asking for Help 196 Celebrate the Wins Along the Way 197 Have Fun 198 Summary 200 Chapter 8: Security, Privacy, and Compliance with the Law 201 Topics Covered in This Chapter 202 The Threat Environment 202 Use a Framework When New to Cybersecurity 203 ix Table of ConTenTs Defend in Depth 205 Access Control in a Nutshell 205 Network Address Translation (NAT) 206 Azure Virtual Networking 207 Allowing Access to Services and Resources 208 Virtual Network (VNet) Firewall Rules and Azure Private Endpoints 209 Azure Private Link 209 Setting Up a Private Endpoint 211 Restrict Public Access 211 More Secure Routing 212 Private Endpoint Tips and Tricks 212 Azure Service Endpoints 213 Creating and Using Service Endpoints 214 Choosing Between Private Endpoints and Service Endpoints 215 Authentication for Azure SQL 217 Required Administrator Accounts 217 Best Practices for Azure Users and Groups 220 Connecting to Azure SQL 222 Connection Tips and Tricks 224 Azure SQL Database Firewall 225 When Undesirable Clients Attempt to Connect 226 What Do Firewalls Do? 226 Best Practices for Firewall Rules 228 Viewing Azure SQL Database Firewall Rules 230 Implementing Data Protection 231 Fine-Grained Data Access Using Row-Level Security 232 Prevent Internal Snooping on Sensitive Data Using Dynamic Data Masking 233 Protecting Data with Encryption 234 Encrypting Data in Transit Using Transport Layer Security 234 Encrypting Data at Rest Using Transparent Data Encryption 235 Encrypting Data in Use with Always Encrypted 236 How Does Always Encrypted Work? 236 x Table of ConTenTs Advanced Data Security 238 Advanced Threat Detection 238 Data Discovery and Classification 239 Vulnerability Assessment 240 Summary 240 Chapter 9: Documenting Data Sources and Metadata in a Data Dictionary 241 Creating Your Data Dictionary 242 Planning Your Data Dictionary 242 Extended Properties 244 Data Classification and Labels 245 Creating the Data Dictionary 248 Data Dictionary Metadata Sources 249 Completing the Picture 253 Linked Servers 253 Object Dependencies 255 Azure Service Mapping 256 Data Lineage 257 Azure Data Catalog 258 Azure Purview 258 Using the Azure Purview Data Dictionary 262 Summary 262 Chapter 10: Moving Your Data to the Cloud 263 Which Service(s) Do You Need? 264 Considerations on Moving Your Data 265 Internet Throughput 266 Internet Connections 268 All-in-One Tools 269 Database Migration Service 270 Backup and Restore 272 BACPAC 273 xi Table of ConTenTs Log Shipping 274 Physical Transfer 275 User Interfaces 276 Handling Schema and Data Separately 277 Command-Line Methods 278 Azure Data Factory 279 Data Synchronization/Replication 279 Third-Party Tools and Resources 281 Importance of Testing 282 Hybrid Scenarios 282 Summary 283 Chapter 11: Data Validation Testing 285 Topics Covered in This Chapter 285 Why Validate the Data 287 Scope Definition 288 Data Validation Output 291 Summary 294 Chapter 12: Postmigration Tasks 295 Decommissioning Legacy Resources 296 Validation and Optimization 298 Performance Issues 298 Business Impact Analysis 299 Right-Sizing Your Data Platform 301 Technical Debt 302 Summary 304 Chapter 13: Post Mortem 305 The Benefits 306 The Process 307 Post-Mortem Questionnaire 309 Elect a Moderator 312 xii Table of ConTenTs Post-Mortem Workshop 313 Meeting Rules and Guidelines 316 Actionable Change 318 The Other Items 319 Show Appreciation 320 Onto the Next Project 321 Summary 322 Index 323 xiii