Professional Azure SQL Managed Database Administration : Efficiently Manage and Modernize Data in the Cloud Using Azure SQL, 3rd Edition

Cover FM Table of Contents Preface Acknowledgement Chapter 1: Introduction to Azure SQL managed databases Who manages what? The Azure SQL Database architecture The Client Layer The Service Layer The Platform Layer The Infrastructure Layer The Azure SQL Database request flow Provisioning an Azure SQL Database Connecting and querying the SQL Database from the Azure portal Connecting to and querying the SQL Database from SQL Server Management Studio Deleting resources Introduction to Azure SQL Managed Instance Connecting to Azure SQL Managed Instance Virtual cluster connectivity architecture Network requirements Differences between SQL Database, SQL Managed Instance, and SQL Server Backup and restore Recovery model SQL Server Agent Change Data Capture Auditing Mirroring Table partitioning Replication Multi-part names SQL Server Browser FileStream Common Language Runtime (SQL CLR) Resource Governor Global temporary tables Log shipping SQL Trace and Profiler Trace flags System stored procedures The USE statement Exercise: Provisioning an Azure SQL Managed Instance using the Azure portal Activity: Provisioning Azure SQL Server and SQL Database using PowerShell Exercise: Provisioning an Azure SQL Managed Instance Summary Chapter 2: Service tiers The DTU model DTU service tiers The vCore model vCore service tiers The General Purpose service tier Azure Premium Storage characteristics The Business Critical service tier The Hyperscale service tier vCore hardware generations Determining an appropriate performance tier DMA SKU recommendation Azure SQL Database compute tiers Scaling up the Azure SQL Database service tier Changing a service tier Exercise: Provisioning a Hyperscale SQL database using PowerShell Choosing between vCore and DTU-based purchasing options Licensing Flexibility Summary Chapter 3: Migration Migration methodology Determining the migration benefits Selecting a service model Selecting a service tier Selecting the primary region and disaster recovery region Determining compatibility issues Selecting a migration tool Choosing between Azure SQL Database and SQL Managed Instance Features Migration Time to develop and market Tools for determining compatibility issues Data Migration Assistant SQL Server Data Tools (SSDT) for Visual Studio SQL Server Management Studio (SSMS) SQLPackage.exe Azure Database Migration Services Choosing a migration tool and performing migration Activity: Migrating an on-premises SQL database to Azure SQL Database using DMA Activity: Migrating an SQL Server database on an Azure virtual machine to an Azure SQL database using Azure DMS Activity: Migrating an on-premises SQL Server database to Azure SQL Database using SSMS Activity: Migrating an SQL Server database to an Azure SQL database using transactional replication Activity: Migrating an on-premises SQL Server to Azure SQL Managed Instance using the native backup and restore method (offline approach) Activity: Migrating an SQL Server on an Azure Virtual Machine to SQL Managed Instance using Azure DMS (online approach) Summary Chapter 4: Backups Automatic backups Backup storage Backup retention period Optimize backup storage costs for Azure SQL Database and Azure SQL Managed Instance Choose the right backup storage type Optimize the database backup retention period Maximize your free backup storage space Configure LTR backups Use Azure Policy Configure long-term backup retention for Azure SQL Database and Azure SQL Managed Instance Long-term retention configuration on Azure SQL Managed Instance Activity: Configure LTR Backups for Azure SQL Managed Instance using PowerShell Manual Backups for Azure SQL Database DACPAC and BACPAC Backing up an Azure SQL Database Using SQL Server Management Studio (SSMS) Manual versus Automated Backups Activity: Perform Manual Backups Using PowerShell Perform native COPY_ONLY backup on Azure SQL Managed Instance Perform a manual COPY_ONLY backup using T-SQL commands Summary Chapter 5: Restoration Restore types Point-in-time restore Performing a PITR on an Azure SQL Database using the Azure portal Performing a PITR for an SQL Managed Instance using the Azure portal Long-term database restore Performing an LTDR on an Azure SQL Database using the Azure portal Performing an LTDR for SQL Managed Instance using PowerShell Restoring deleted databases Restoring a deleted database on Azure SQL Database using the Azure portal Restoring a deleted database on SQL Managed Instance using the Azure portal Geo-restoring databases Performing a geo-restore on an SQL Database using the Azure portal Performing a geo-restore on an SQL Managed Instance using the Azure portal Importing a database (Azure SQL Database only) Activity: Performing a PITR for an Azure SQL Database with PowerShell Activity: Performing a geo-restore of an Azure SQL Database with PowerShell Activity: Performing Point-In-Time restore for SQL Managed Instance with PowerShell Part 1: Restoring a database to a point in time using PowerShell on one managed instance Part 2: Performing a cross-instance point-in-time restore from an existing database Activity: Geo-restoring a database hosted on SQL Managed Instance using the Az PowerShell module Activity: Restoring a deleted database on SQL Managed Instance using PowerShell Summary Chapter 6: Security Network security Firewall rules Managing server-level firewall rules using the Azure portal Managing server-level firewall rules using Transact-SQL Managing database-level firewall rules using Transact-SQL Service endpoints Configuring service endpoints for SQL Database Private endpoint Authentication SQL authentication Azure AD authentication Azure AD Active Directory - Password Using Active Directory - Password to authenticate to a SQL database SQL Database authentication structure SQL Database and SQL Managed Instance authentication considerations Authorization Server-level administrative roles Non-administrative users Creating contained database users for Azure AD authentication Groups and roles Row-level security Dynamic data masking Data Discovery & Classification Exercise: Configuring Data Discovery & Classification for SQL Database Auditing Exercise: Configuring SQL Database auditing Exercise: Configuring auditing for SQL Managed Instance Activity: Audit COPY_ONLY backup events on SQL Managed Instance using audit logs Steps to configure an audit for backup and restore events Transparent Data Encryption Azure Defender for SQL Securing data traffic Enforcing a minimal TLS version for SQL Database and SQL Managed Instance Activity: Setting a minimum TLS version using the Azure portal and PowerShell for SQL Managed Instance Using the Azure portal Using PowerShell Configuring and securing public endpoints in SQL Managed Instance Securing SQL Managed Instance public endpoints Locking traffic flow down using NSG or firewall rules Activity: Implementing RLS Activity: Implementing DDM Activity: Implementing Azure Defender for SQL to detect SQL injection and brute-force attacks Summary Chapter 7: Scalability Vertical scaling Scale-up or scale-down service tiers Vertical partitioning Activity: Creating alerts Horizontal scaling Shard map manager Sharding data models Activity: Creating shards Activity: Splitting data between shards Activity: Using elastic database queries Scaling a managed instance Duration of scale-up/down operations Activity: Scaling up SQL Managed Instance using the Azure portal Activity: Scaling a managed instance using the Az.sql PowerShell module Alternate ways of scaling SQL Managed Instance Activity: Connecting to the SQL Managed Instance internal read replica using SSMS Summary Chapter 8: Elastic and instance pools Introducing elastic database pools in SQL Database When should you consider elastic database pools? Sizing an elastic database pool Creating an elastic database pool and adding toystore shards to the elastic database pool Geo-replication considerations for elastic database pools Auto-failover group considerations for elastic database pools Activity: Exploring elastic database pools Elastic database jobs Elastic job agent Job database Target group Jobs Use cases Exercise: Configuring an elastic database job using T-SQL Introducing instance pools in SQL Managed Instance Key differences between an instance pool and a single managed instance Architecture differences between an instance pool and a single SQL managed instance Resource limits Public preview limitations Performance and security considerations for instance pools Deploying an instance pool using PowerShell commands Activity: Deploying and managing a managed instance in an instance pool Summary Chapter 9: High availability and disaster recovery High availability The basic, standard, and general-purpose service tier locally redundant availability model General-purpose service tier zone-redundant configuration The premium/business-critical tier locally redundant availability model The premium/business critical service tier zone-redundant configuration Accelerated database recovery (ADR) The standard database recovery process The ADR process Activity: Evaluating ADR Disaster recovery Active geo-replication Auto-failover groups Activity: Configuring active geo-replication and performing manual failover using the Azure portal Activity: Configuring an Azure SQL Database auto-failover group using Azure portal Activity: Configuring active geo-replication for Azure SQL Database using PowerShell Activity: Configuring auto-failover groups for Azure SQL Database using PowerShell Activity: Configuring an auto-failover group for SQL Managed Instance Summary Chapter 10: Monitoring and tuning Monitoring an Azure SQL Database and SQL Managed Instance using the Azure portal Monitoring database metrics Alert rules, database size, and diagnostic settings Diagnostic settings and logs Intelligent Performance Query Performance Insight Analyzing diagnostic logs using Azure SQL Analytics Creating a Log Analytics workspace Creating an Azure SQL Analytics solution Generating a workload and reviewing insights Activity: Monitoring Azure SQL Database with Log Analytics and Power BI Monitoring queries using the Query Performance Insight pane Monitoring an Azure SQL Database and SQL Managed Instance using DMVs Monitoring database metrics Monitoring connections Monitoring query performance Monitoring blocking Extended events Examining queries using extended events Tuning an Azure SQL database Automatic tuning In-memory technologies In-memory OLTP Memory-optimized tables Natively compiled procedures Columnstore indexes Monitoring cost Activity: Exploring the in-memory OLTP feature Monitoring and tuning an Azure SQL Managed Instance General Purpose instance I/O characteristics Monitoring the first run with the default file configuration of the TPC-C database Summary Chapter 11: Database features Azure SQL Data Sync Activity: Configuring Data Sync between two Azure SQL databases using PowerShell Online and resumable DDL operations SQL Graph queries and improvements Graph database integrity using edge constraints Machine Learning Services Differences between Machine Learning Services in SQL Server and Azure SQL Managed Instance Activity: Run basic Python scripts Activity: Using Machine Learning Services in Azure SQL Managed Instance to forecast monthly sales for the toystore database Distributed transactions in Azure SQL Managed Instance Server Trust Group Activity: Creating a Server Trust Group using the Azure portal Activity: Running distributed transactions using T-SQL Summary Chapter 12: App modernization Migrating an SQL Server workload to SQL Managed Instance Backup and restore SQL installation and patches Scaling High availability and disaster recovery Newly introduced features Support for hosting SSRS catalog databases Azure Machine Learning Distributed transaction support SQL Database serverless Serverless use cases Creating a serverless database Auto-scaling in serverless Cache Reclamation Auto-pausing in serverless Auto-resuming in serverless SQL Database serverless billing Demonstration of auto-scaling and compute billing in serverless Serverless vs. provisioned compute Scaling to the Hyperscale service tier Considering moving to the Hyperscale service tier Activity: Updating an existing SQL database to the Hyperscale service tier using the Azure portal Activity: Updating an existing SQL database to the Hyperscale service tier using PowerShell commands Read scale-out an SQL Hyperscale database Summary Index Master data management by effectively utilizing the features of Azure SQL database. Key Features Learn to automate common management tasks with PowerShell. Understand different methods to generate elastic pools and shards to scale Azure SQL databases. Learn to develop a scalable cloud solution through over 40 practical activities and exercises. Book Description Despite being the cloud version of SQL Server, Azure SQL Database and Azure SQL Managed Instance stands out in various aspects when it comes to management, maintenance, and administration. Updated with the latest Azure features, Professional Azure SQL Managed Database Administration continues to be a comprehensive guide for becoming proficient in data management. The book begins by introducing you to the Azure SQL managed databases (Azure SQL Database and Azure SQL Managed Instance), explaining their architecture, and how they differ from an on-premises SQL server. You will then learn how to perform common tasks, such as migrating, backing up, and restoring a SQL Server database to an Azure database. As you progress, you will study how you can save costs and manage and scale multiple SQL databases using elastic pools. You will also implement a disaster recovery solution using standard and active geo-replication. Finally, you will explore the monitoring and tuning of databases, the key features of databases, and the phenomenon of app modernization. By the end of this book, you will have mastered the key aspects of an Azure SQL database and Azure SQL managed instance, including migration, backup restorations, performance optimization, high availability, and disaster recovery. What you will learn Understanding Azure SQL database configuration and pricing options Provisioning a new SQL database or migrating an existing on-premises SQL Server database to an Azure SQL database Backing up and restoring an Azure SQL database Securing and scaling an Azure SQL database Monitoring and tuning an Azure SQL database Implementing high availability and disaster recovery with an Azure SQL database Managing, maintaining, and securing managed instances Who this book is for This book is designed to benefit database administrators, database developers, or application developers who are interested in developing new applications or migrating existing ones with Azure SQL database. 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. Table of Contents Introduction to Azure SQL managed databases Service tiers Migration Backups Restoration Security Scalability Elastic and instance pools High availability and disaster recovery Monitoring and tuning Database features App modernization **Master data management by effectively utilizing the features of Azure SQL database.** ## Key Features * Learn to automate common management tasks with PowerShell. * Understand different methods to generate elastic pools and shards to scale Azure SQL databases. * Learn to develop a scalable cloud solution through over 40 practical activities and exercises. Despite being the cloud version of SQL Server, Azure SQL Database and Azure SQL Managed Instance stands out in various aspects when it comes to management, maintenance, and administration. Updated with the latest Azure features, Professional Azure SQL Managed Database Administration continues to be a comprehensive guide for becoming proficient in data management. The book begins by introducing you to the Azure SQL managed databases (Azure SQL Database and Azure SQL Managed Instance), explaining their architecture, and how they differ from an on-premises SQL server. You will then learn how to perform common tasks, such as migrating, backing up, and restoring a SQL Server database to an Azure database. As you progress, you will study how you can save costs and manage and scale multiple SQL databases using elastic pools. You will also implement a disaster recovery solution using standard and active geo-replication. Finally, you will explore the monitoring and tuning of databases, the key features of databases, and the phenomenon of app modernization. By the end of this book, you will have mastered the key aspects of an Azure SQL database and Azure SQL managed instance, including migration, backup restorations, performance optimization, high availability, and disaster recovery. ## What you will learn * Understanding Azure SQL database configuration and pricing options * Provisioning a new SQL database or migrating an existing on-premises SQL Server database to an Azure SQL database * Backing up and restoring an Azure SQL database * Securing and scaling an Azure SQL database * Monitoring and tuning an Azure SQL database * Implementing high availability and disaster recovery with an Azure SQL database * Managing, maintaining, and securing managed instances This book is designed to benefit database administrators, database developers, or application developers who are interested in developing new applications or migrating existing ones with Azure SQL database. 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. ## Table of Contents 1. Introduction to Azure SQL managed databases 2. Service tiers 3. Migration 4. Backups 5. Restoration 6. Security 7. Scalability 8. Elastic and instance pools 9. High availability and disaster recovery 10. Monitoring and tuning 11. Database features 12. App modernization