Data Loading...
Professional Azure SQL Database Administration Second Edition Equip yourself with the skills to manage and maintain data in the cloud
Ahmad Osama
www.packt.com
FOR SALE IN INDIA ONLY
Professional Azure SQL Database Administration Second Edition Equip yourself with the skills to manage and maintain data in the cloud
Ahmad Osama
Professional Azure SQL Database Administration Second Edition Copyright © 2019 Packt Publishing All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews. Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, nor Packt Publishing, and its dealers and distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book. Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information. Author: Ahmad Osama Technical Reviewers: Aaditya Pokkunuri and Shashikant Shakya Managing Editor: Aditya Datar Acquisitions Editors: Aditya Date and Karan Wadekar Production Editor: Shantanu Zagade Editorial Board: David Barnes, Mayank Bhardwaj, Ewan Buckingham, Simon Cox, Mahesh Dhyani, Taabish Khan, Manasa Kumar, Alex Mazonowicz, Douglas Paterson, Dominic Pereira, Shiny Poojary, Erol Staveley, Ankita Thakur, and Jonathan Wray First Published: May 2018 Second Edition: July 2019 Production Reference: 1100719 ISBN: 978-1-78980-254-2 Published by Packt Publishing Ltd. Livery Place, 35 Livery Street Birmingham B3 2PB, UK
Table of Contents Preface
i
Microsoft Azure SQL Database Primer
1
Introduction .................................................................................................... 2 The Azure SQL Database Architecture ........................................................ 2 Azure SQL Database Request Flow .................................................................... 5
Provisioning an Azure SQL Database .......................................................... 6 Connecting and Querying the SQL Database from the Azure Portal .......... 13 Connecting to and Querying the SQL Database from SQL Server Management Studio .............................................................................. 16 Deleting Resources ............................................................................................ 21
Differences between Azure SQL Database and SQL Server ................... 24 Unsupported Features ...................................................................................... 27
Introduction to Managed Instance ............................................................ 28 Purchasing Model .............................................................................................. 29
Connecting to SQL Managed Instance ...................................................... 30 Exercise: Provisioning a SQL-Managed Instance Using the Azure Portal ........................................................................................................ 32 Activity: Provisioning an Azure SQL Server and SQL Database Using PowerShell ............................................................................................... 38 Exercise: Provisioning a Managed Instance .................................................... 46
Summary ....................................................................................................... 53
Migrating a SQL Server Database to an Azure SQL Database
55
Introduction .................................................................................................. 56 Finding the Migration Benefits ......................................................................... 56 Finding the Blockers .......................................................................................... 56 Selecting a Service Model ................................................................................. 56 Selecting a Service Tier ...................................................................................... 56 Selecting the Main Region and Disaster Recovery Region ............................ 57 Selecting a Migration Tool ................................................................................ 57 Choosing Between Azure SQL Database and SQL Database Managed Instance ............................................................................................. 57 Features .............................................................................................................. 57 Migration ............................................................................................................ 58 Time to Develop and Market ............................................................................ 58 Azure SQL Database Service Tiers ................................................................... 58
DTU Pricing Models ...................................................................................... 59 Database Transaction Units ............................................................................. 59 vCore Pricing Model ........................................................................................... 64 Hardware Generations ...................................................................................... 64 vCore Service Tiers ............................................................................................. 65 Azure Hybrid Benefit ......................................................................................... 65 Hyperscale Service Tier ..................................................................................... 66 Exercise: Provisioning a Hyperscale SQL Database Using PowerShell ........ 71 Scaling up the Azure SQL Database Service Tier ............................................ 72 Changing a Service Tier ..................................................................................... 76
Choosing Between a vCore Pricing Model and DTU-based Service Tiers .................................................................................................. 76 Licensing ............................................................................................................. 76 Flexibility ............................................................................................................. 77 Determining an Appropriate DTU Based Service Tier ................................... 79 Azure SQL Database DTU Calculator ............................................................... 79 DTU Calculator Work Flow ................................................................................ 79 Finding an Initial Service Tier for the Database to be Migrated Using the DTU Calculator .................................................................................. 80 Overall Recommendation ................................................................................. 83 Recommendation Based on CPU Utilization .................................................. 84 Recommendation Based on IOPs Utilization .................................................. 84 Recommendation Based on Log Utilization ................................................... 85 Determining Compatibility Issues ................................................................... 85 Data Migration Assistant .................................................................................. 85 SQL Server Data Tools (SSDT) for Visual Studio ............................................. 86 SQL Server Management Studio (SSMS) .......................................................... 86 SQLPackage.exe ................................................................................................. 86 SQL Azure Migration Wizard ............................................................................. 86
Azure Database Migration Services ........................................................... 86 Exercise: Migrating a SQL Server Database to Azure SQL Database Using Azure DMS .............................................................................. 87 Determining the Migration Method ................................................................ 97 Migrating an On-Premises SQL Server Database to Azure SQL Database .......................................................................................... 99 Activity: Using DMA ......................................................................................... 108 Activity: Performing Transactional Replication ........................................... 119
Summary ..................................................................................................... 140
Backing Up an Azure SQL Database
143
Introduction ............................................................................................... 144 Automatic Backups .................................................................................... 144 Backup Storage ............................................................................................... 144 Backup Retention Period ............................................................................... 145 Configuring Long-Term Backup Retention for Azure SQL Database ........ 145
Manual Backups ......................................................................................... 147 Backing up an Azure SQL Database Using SQL Server Management Studio (SSMS) ........................................................................... 148 DACPAC and BACPAC ...................................................................................... 151 Manual versus Automated Backups ............................................................. 153 Activity: Performing Backups ........................................................................ 153
Summary ..................................................................................................... 158
Restoring an Azure SQL Database
161
Introduction ................................................................................................ 162 Restore Types ............................................................................................. 162 Point-In-Time Restore ..................................................................................... 162 Long-Term Database Restore ........................................................................ 169 Restoring Deleted Databases ........................................................................ 173 Geo-Restore Database ................................................................................... 175 Importing a Database ..................................................................................... 178 Activity: Performing PITR ............................................................................... 182 Activity: Performing Geo-Restore ................................................................. 185
Summary ..................................................................................................... 189
Securing an Azure SQL Database
191
Introduction ................................................................................................ 192 Access Control ............................................................................................ 192 Firewall Rules .................................................................................................. 192 Managing Server-Level Firewall Rules Using the Azure Portal .................. 194 Managing Server-Level Firewall Rules Using Transact-SQL ....................... 198 Managing Database-level Firewall Rules Using Transact-SQL ................... 200
Authentication ........................................................................................... 203 SQL Authentication ......................................................................................... 203 Azure Active Directory Authentication ........................................................ 204 Using Active Directory – Password to Authenticate to an Azure SQL Database ....................................................................................... 207
Azure SQL Database Authentication Structure ...................................... 212 Azure SQL Database Authentication Considerations ................................. 213
Authorization .............................................................................................. 214 Server-Level Administrative Roles ................................................................ 214 Database Creators .......................................................................................... 214 Login Manager ................................................................................................. 214 Non-Administrative Users ............................................................................. 215
Creating Contained Database Users for Azure AD Authentication ..... 215 Groups and Roles ............................................................................................ 218 Row-level Security ........................................................................................... 219 Dynamic Data Masking .................................................................................. 220
Advanced Data Security ............................................................................ 221 Exercise: Exploring Advanced Data Security Features ............................... 224
Auditing ....................................................................................................... 233 Exercise: Configuring SQL Database Auditing ............................................. 234 Activity: Implementing Row-level Security .................................................. 242 Activity: Implementing Dynamic Data Masking .......................................... 248 Activity: Implementing Advanced Data Security to Detect SQL Injection ....................................................................................... 251
Summary ..................................................................................................... 262
Scaling out an Azure SQL Database
265
Introduction ................................................................................................ 266 Vertical Scaling ........................................................................................... 266 Scale-up or Scale-down Service Tiers ........................................................... 266 Using T-SQL to Change the Service Tier ....................................................... 268 Vertical Partitioning ........................................................................................ 272 Horizontal Scaling ........................................................................................... 273 Shard Map Manager ....................................................................................... 276 Activity: Creating Alerts .................................................................................. 280 Activity: Creating Shards ................................................................................ 305 Activity: Splitting Data Between Shards ....................................................... 324 Activity: Using Elastic Queries ....................................................................... 336
Summary ..................................................................................................... 339
Azure SQL Database Elastic Pools
341
Introducing Elastic Pools ........................................................................... 342 When Should You Consider Elastic Pools? ................................................... 342 Sizing an Elastic Pool ...................................................................................... 345 Comparing the Pricing .................................................................................... 346 Sizing Best Practices ....................................................................................... 346 Create an Elastic Pool and Add Toystore Shards to the Elastic Pool ........ 348
Elastic Database Jobs ................................................................................ 353 Elastic Job Agent .............................................................................................. 354 Job Database .................................................................................................... 354 Target Group ................................................................................................... 354 Job .................................................................................................................... 354 Use Cases ......................................................................................................... 355 Exercise: Configuring an Elastic Database Job Using T-SQL ....................... 355 Activity: Exploring Elastic Pools ..................................................................... 366
Summary ..................................................................................................... 374
High Availability and Disaster Recovery
377
High Availability ......................................................................................... 378 Accelerated Database Recovery (ADR) .................................................... 380 Zone Redundant Configuration .................................................................... 385
Disaster Recovery ...................................................................................... 386 Standard Geo-Replication .............................................................................. 386 Active Geo-Replication ................................................................................... 389
Synchronous Replication .......................................................................... 390 Auto-Failover Groups ..................................................................................... 390 Configuring Active Geo-Replication and Performing Manual Failover ............................................................................................... 392 Configuring the Auto-Failover Group ........................................................... 402 Activity: Configuring Active Geo-Replication ............................................... 404 Activity: Configuring Auto-Failover Groups ................................................. 412 Activity: Evaluating Accelerated Database Recovery ................................. 420
Summary ..................................................................................................... 425
Monitoring and Tuning Azure SQL Database
427
Introduction ................................................................................................ 428 Monitoring an Azure SQL Database Using the Azure Portal ................ 428 Monitoring Database Metrics ........................................................................ 429 Alert Rules, Database Size, and Diagnostic Settings .................................. 431 Database Data Storage .................................................................................. 436 Diagnostic Settings ......................................................................................... 437 Query Performance Insight ........................................................................... 441 Creating Alerts ................................................................................................. 454 Activity: Monitoring Azure SQL Database with Log Analytics and Power BI ................................................................................... 461
Monitoring Queries Using the Query Performance Insight Blade ............................................................................................... 474 Monitoring an Azure SQL Database Using DMVs ................................... 480 Monitoring Database Metrics ........................................................................ 480 Monitoring Connections ................................................................................ 482 Monitoring Query Performance .................................................................... 484 Monitoring Blocking ....................................................................................... 485 Extended Events .............................................................................................. 487 Examining Queries .......................................................................................... 488
Tuning an Azure SQL Database ................................................................ 493 Automatic Tuning ........................................................................................... 494 In-Memory Technologies ............................................................................... 495 In-Memory OLTP ............................................................................................. 496 Activity: Exploring the In-Memory OLTP Feature ........................................ 501
Summary ..................................................................................................... 504
Database Features
507
Introduction ................................................................................................ 508 Azure SQL Data Sync ...................................................................................... 508 Activity: Configuring Data Sync between Two Azure SQL Databases Using PowerShell ......................................................................... 510 Online and Resumable Data Definition Language (DDL) Operations ...... 519
SQL Graph Queries and Improvements .................................................. 520 Edge Constraints ............................................................................................. 524 Machine Learning Services ............................................................................ 526 Differences between Azure SQL Database Machine Learning Services and SQL Server Machine Learning Services ................................. 527 Activity: Using Machine Learning Services in an Azure SQL Database to Forecast Monthly Sales for the toystore Database .............. 528
Summary ..................................................................................................... 535
Index
537
>
Preface
About This section briefly introduces the author, the coverage of this course, the technical skills you'll need to get started, and the hardware and software required to complete all of the included activities and exercises.
ii | Preface
About the Book Despite being the cloud version of SQL Server, Azure SQL Database differs in key ways when it comes to management, maintenance, and administration. This book shows you how to administer Azure SQL Database to fully benefit from its wide range of features and functionality. Professional Azure SQL Database Administration - Second Edition begins by covering the architecture and explaining the difference between Azure SQL Database and the on-premise SQL Server to help you get comfortable with Azure SQL Database. You'll perform common tasks such as migrating, backing up, and restoring a SQL Server database to an Azure database. As you progress, you'll study how you can save costs and manage and scale multiple SQL databases using elastic pools. You'll also implement a disaster recovery solution using standard and active geo-replication. Whether through learning different techniques to monitor and tune an Azure SQL database or improving performance using in-memory technology, this book will enable you to make the most out of Azure SQL Database's features and functionality for data management solutions. By the end of this book, you'll be well versed with key aspects of an Azure SQL Database instance, such as migration, backup restorations, performance optimization, high availability, and disaster recovery.
About the Author Ahmad Osama works for Pitney Bowes India Pvt Ltd as a technical architect and is a Microsoft Data Platform Reconnect MVP and MCSE: SQL Server 2016 data management and analytics. At Pitney Bowes, he works on developing and maintaining highperformance on-premise and cloud SQL Server OLTP environments, building CI/ CD environments for databases and automation. Other than his day-to-day work, Ahmad blogs at DataPlatformLabs and has written over 100 blogs on various topics, including SQL Server administration/development, Azure SQL Database, and Azure Data Factory. He regularly speaks at user group events and webinars conducted by the DataPlatformLabs community. You can reach him on LinkedIn at ahmadosama3 or follow his Twitter handle, @_ahmadosama.
Objectives • Understand Azure SQL Database configuration and pricing options. • Provision a new SQL database or migrate an existing on-premise SQL Server database to Azure SQL Database. • Back up and restore Azure SQL Database. • Secure an Azure SQL database.
About the Book | iii • Scale an Azure SQL database. • Monitor and tune an Azure SQL database. • Implement high availability and disaster recovery with Azure SQL Database. • Automate common management tasks with PowerShell. • Develop a scalable cloud solution with Azure SQL Database. • Manage, maintain, and secure managed instances.
Audience If you're a database administrator, database developer, or an application developer interested in developing new applications or migrating existing ones with Azure SQL database, then this book is for you. Prior experience of working with an on-premise SQL Server or Azure SQL Database, along with a basic understanding of PowerShell scripts and C# code, is necessary to grasp the concepts covered in this book.
Approach Each section in this book has been explicitly designed to engage and stimulate you so that you can retain and apply what you learn in a practical context with maximum impact. You'll learn how to tackle intellectually stimulating programming challenges that will prepare you for real-world topics through test-driven development practices.
Hardware Requirements For an optimal student experience, we recommend the following hardware configuration: • Processor: Pentium 4, 1.8 GHz or higher (or equivalent) • Memory: 4 GB RAM • Hard disk: 10 GB free space • An internet connection
Software Requirements You'll also need the following software installed in advance: • Windows 8 or above. • The latest version of Google Chrome. • An Azure subscription.
Professional Azure SQL Database Administration – Second Edition Despite being the cloud version of SQL Server, Azure SQL Database differs in key ways when it comes to management, maintenance, and administration. This book shows you how to administer Azure SQL Database to fully benefit from its wide range of features and functionality.
Professional Azure SQL Database Administration – Second Edition begins by covering the architecture and explaining the difference between Azure SQL Database and the on-premise SQL Server to help you get comfortable with Azure SQL Database. You'll perform common tasks such as migrating, backing up, and restoring a SQL Server Database to an Azure SQL Database. As you progress, you'll study how to save costs and manage
and scale multiple SQL databases using elastic pools. You'll also implement a disaster recovery solution using standard and active geo-replication. Whether through learning different techniques to monitor and tune an Azure SQL database or improving performance using in-memory technology, this book will enable you to make the most out of Azure SQL Database's features and functionality for data management solutions. By the end of this book, you'll be well versed with key aspects of an Azure SQL database instance, such as migration, backup restoration, performance optimization, high availability, and disaster recovery.
Things you will learn: •
• •
Provision a new SQL database or migrate an existing on-premise SQL Server database to Azure SQL Database Back up and restore Azure SQL Database Secure and scale an Azure SQL Database
•
Monitor and tune an Azure SQL Database
•
Implement high availability and disaster recovery with Azure SQL Database
•
Manage, maintain, and secure managed instances
www.packt.com
FOR SALE IN INDIA ONLY