Professional Azure SQL Managed Database Administration - Third Edition

Professional Azure SQL Managed Database Administration - Third Edition

by Ahmad Osama, Shashikant Shakya
Released March 2021
Publisher(s): Packt Publishing
ISBN: 9781801076524

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

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

  1. Professional Azure SQL Managed Database Administration – Third Edition
  2. Preface
    1. About Professional Azure SQL Managed Database Administration, Third Edition
      1. About the authors
      2. About the reviewers
      3. Learning objectives
      4. Audience
      5. Approach
      6. Hardware and software requirements
      7. Conventions
      8. Downloading resources
    2. Acknowledgement
  3. 1. Introduction to Azure SQL managed databases
    1. Who manages what?
    2. The Azure SQL Database architecture
      1. The Client Layer
      2. The Service Layer
      3. The Platform Layer
      4. The Infrastructure Layer
      5. The Azure SQL Database request flow
    3. Provisioning an Azure SQL Database
      1. Connecting and querying the SQL Database from the Azure portal
      2. Connecting to and querying the SQL Database from SQL Server Management Studio
      3. Deleting resources
    4. Introduction to Azure SQL Managed Instance
    5. Connecting to Azure SQL Managed Instance
      1. Virtual cluster connectivity architecture
      2. Network requirements
    6. Differences between SQL Database, SQL Managed Instance, and SQL Server
      1. Backup and restore
      2. Recovery model
      3. SQL Server Agent
      4. Change Data Capture
      5. Auditing
      6. Mirroring
      7. Table partitioning
      8. Replication
      9. Multi-part names
      10. SQL Server Browser
      11. FileStream
      12. Common Language Runtime (SQL CLR)
      13. Resource Governor
      14. Global temporary tables
      15. Log shipping
      16. SQL Trace and Profiler
      17. Trace flags
      18. System stored procedures
      19. The USE statement
      20. Exercise: Provisioning an Azure SQL Managed Instance using the Azure portal
    7. Activity: Provisioning Azure SQL Server and SQL Database using PowerShell
      1. Exercise: Provisioning an Azure SQL Managed Instance
    8. Summary
  4. 2. Service tiers
    1. The DTU model
    2. DTU service tiers
      1. The vCore model
    3. vCore service tiers
      1. The General Purpose service tier
      2. Azure Premium Storage characteristics
      3. The Business Critical service tier
      4. The Hyperscale service tier
      5. vCore hardware generations
    4. Determining an appropriate performance tier
      1. DMA SKU recommendation
      2. Azure SQL Database compute tiers
      3. Scaling up the Azure SQL Database service tier
      4. Changing a service tier
    5. Exercise: Provisioning a Hyperscale SQL database using PowerShell
    6. Choosing between vCore and DTU-based purchasing options
      1. Licensing
      2. Flexibility
    7. Summary
  5. 3. Migration
    1. Migration methodology
      1. Determining the migration benefits
      2. Selecting a service model
      3. Selecting a service tier
      4. Selecting the primary region and disaster recovery region
      5. Determining compatibility issues
      6. Selecting a migration tool
    2. Choosing between Azure SQL Database and SQL Managed Instance
      1. Features
      2. Migration
      3. Time to develop and market
    3. Tools for determining compatibility issues
      1. Data Migration Assistant
      2. SQL Server Data Tools (SSDT) for Visual Studio
      3. SQL Server Management Studio (SSMS)
      4. SQLPackage.exe
      5. Azure Database Migration Services
    4. Choosing a migration tool and performing migration
      1. Activity: Migrating an on-premises SQL database to Azure SQL Database using DMA
      2. Activity: Migrating an SQL Server database on an Azure virtual machine to an Azure SQL database using Azure DMS
      3. Activity: Migrating an on-premises SQL Server database to Azure SQL Database using SSMS
      4. Activity: Migrating an SQL Server database to an Azure SQL database using transactional replication
      5. Activity: Migrating an on-premises SQL Server to Azure SQL Managed Instance using the native backup and restore method (offline approach)
      6. Activity: Migrating an SQL Server on an Azure Virtual Machine to SQL Managed Instance using Azure DMS (online approach)
    5. Summary
  6. 4. Backups
    1. Automatic backups
      1. Backup storage
      2. Backup retention period
    2. Optimize backup storage costs for Azure SQL Database and Azure SQL Managed Instance
      1. Choose the right backup storage type
      2. Optimize the database backup retention period
      3. Maximize your free backup storage space
      4. Configure LTR backups
      5. Use Azure Policy
      6. Configure long-term backup retention for Azure SQL Database and Azure SQL Managed Instance
      7. Long-term retention configuration on Azure SQL Managed Instance
    3. Activity: Configure LTR Backups for Azure SQL Managed Instance using PowerShell
    4. Manual Backups for Azure SQL Database
      1. DACPAC and BACPAC
      2. Backing up an Azure SQL Database Using SQL Server Management Studio (SSMS)
      3. Manual versus Automated Backups
    5. Activity: Perform Manual Backups Using PowerShell
      1. Perform native COPY_ONLY backup on Azure SQL Managed Instance
      2. Perform a manual COPY_ONLY backup using T-SQL commands
    6. Summary
  7. 5. Restoration
    1. Restore types
      1. Point-in-time restore
      2. Performing a PITR on an Azure SQL Database using the Azure portal
      3. Performing a PITR for an SQL Managed Instance using the Azure portal
      4. Long-term database restore
      5. Performing an LTDR on an Azure SQL Database using the Azure portal
      6. Performing an LTDR for SQL Managed Instance using PowerShell
    2. Restoring deleted databases
      1. Restoring a deleted database on Azure SQL Database using the Azure portal
      2. Restoring a deleted database on SQL Managed Instance using the Azure portal
    3. Geo-restoring databases
      1. Performing a geo-restore on an SQL Database using the Azure portal
      2. Performing a geo-restore on an SQL Managed Instance using the Azure portal
    4. Importing a database (Azure SQL Database only)
    5. Activity: Performing a PITR for an Azure SQL Database with PowerShell
    6. Activity: Performing a geo-restore of an Azure SQL Database with PowerShell
    7. Activity: Performing Point-In-Time restore for SQL Managed Instance with PowerShell
      1. Part 1: Restoring a database to a point in time using PowerShell on one managed instance
      2. Part 2: Performing a cross-instance point-in-time restore from an existing database
    8. Activity: Geo-restoring a database hosted on SQL Managed Instance using the Az PowerShell module
    9. Activity: Restoring a deleted database on SQL Managed Instance using PowerShell
    10. Summary
  8. 6. Security
    1. Network security
      1. Firewall rules
    2. Managing server-level firewall rules using the Azure portal
      1. Managing server-level firewall rules using Transact-SQL
      2. Managing database-level firewall rules using Transact-SQL
      3. Service endpoints
      4. Configuring service endpoints for SQL Database
      5. Private endpoint
    3. Authentication
      1. SQL authentication
      2. Azure AD authentication
      3. Azure AD
      4. Active Directory - Password
      5. Using Active Directory - Password to authenticate to a SQL database
      6. SQL Database authentication structure
      7. SQL Database and SQL Managed Instance authentication considerations
    4. Authorization
      1. Server-level administrative roles
      2. Non-administrative users
      3. Creating contained database users for Azure AD authentication
      4. Groups and roles
      5. Row-level security
      6. Dynamic data masking
      7. Data Discovery & Classification
      8. Exercise: Configuring Data Discovery & Classification for SQL Database
      9. Auditing
      10. Exercise: Configuring SQL Database auditing
      11. Exercise: Configuring auditing for SQL Managed Instance
    5. Activity: Audit COPY_ONLY backup events on SQL Managed Instance using audit logs
      1. Steps to configure an audit for backup and restore events
    6. Transparent Data Encryption
    7. Azure Defender for SQL
    8. Securing data traffic
      1. Enforcing a minimal TLS version for SQL Database and SQL Managed Instance
    9. Activity: Setting a minimum TLS version using the Azure portal and PowerShell for SQL Managed Instance
      1. Using the Azure portal
      2. Using PowerShell
    10. Configuring and securing public endpoints in SQL Managed Instance
      1. Securing SQL Managed Instance public endpoints
      2. Locking traffic flow down using NSG or firewall rules
    11. Activity: Implementing RLS
    12. Activity: Implementing DDM
    13. Activity: Implementing Azure Defender for SQL to detect SQL injection and brute-force attacks
    14. Summary
  9. 7. Scalability
    1. Vertical scaling
      1. Scale-up or scale-down service tiers
      2. Vertical partitioning
    2. Activity: Creating alerts
    3. Horizontal scaling
      1. Shard map manager
      2. Sharding data models
      3. Activity: Creating shards
      4. Activity: Splitting data between shards
      5. Activity: Using elastic database queries
    4. Scaling a managed instance
      1. Duration of scale-up/down operations
      2. Activity: Scaling up SQL Managed Instance using the Azure portal
      3. Activity: Scaling a managed instance using the Az.sql PowerShell module
    5. Alternate ways of scaling SQL Managed Instance
      1. Activity: Connecting to the SQL Managed Instance internal read replica using SSMS
    6. Summary
  10. 8. Elastic and instance pools
    1. Introducing elastic database pools in SQL Database
      1. When should you consider elastic database pools?
      2. Sizing an elastic database pool
      3. Creating an elastic database pool and adding toystore shards to the elastic database pool
      4. Geo-replication considerations for elastic database pools
      5. Auto-failover group considerations for elastic database pools
    2. Activity: Exploring elastic database pools
    3. Elastic database jobs
      1. Elastic job agent
      2. Job database
      3. Target group
      4. Jobs
      5. Use cases
      6. Exercise: Configuring an elastic database job using T-SQL
    4. Introducing instance pools in SQL Managed Instance
      1. Key differences between an instance pool and a single managed instance
      2. Architecture differences between an instance pool and a single SQL managed instance
      3. Resource limits
      4. Public preview limitations
      5. Performance and security considerations for instance pools
    5. Deploying an instance pool using PowerShell commands
      1. Activity: Deploying and managing a managed instance in an instance pool
    6. Summary
  11. 9. High availability and disaster recovery
    1. High availability
      1. The basic, standard, and general-purpose service tier locally redundant availability model
      2. General-purpose service tier zone-redundant configuration
      3. The premium/business-critical tier locally redundant availability model
      4. The premium/business critical service tier zone-redundant configuration
    2. Accelerated database recovery (ADR)
      1. The standard database recovery process
      2. The ADR process
      3. Activity: Evaluating ADR
    3. Disaster recovery
      1. Active geo-replication
      2. Auto-failover groups
      3. Activity: Configuring active geo-replication and performing manual failover using the Azure portal
      4. Activity: Configuring an Azure SQL Database auto-failover group using Azure portal
      5. Activity: Configuring active geo-replication for Azure SQL Database using PowerShell
      6. Activity: Configuring auto-failover groups for Azure SQL Database using PowerShell
      7. Activity: Configuring an auto-failover group for SQL Managed Instance
    4. Summary
  12. 10. Monitoring and tuning
    1. Monitoring an Azure SQL Database and SQL Managed Instance using the Azure portal
      1. Monitoring database metrics
      2. Alert rules, database size, and diagnostic settings
      3. Diagnostic settings and logs
      4. Intelligent Performance
      5. Query Performance Insight
    2. Analyzing diagnostic logs using Azure SQL Analytics
      1. Creating a Log Analytics workspace
      2. Creating an Azure SQL Analytics solution
      3. Generating a workload and reviewing insights
    3. Activity: Monitoring Azure SQL Database with Log Analytics and Power BI
    4. Monitoring queries using the Query Performance Insight pane
    5. Monitoring an Azure SQL Database and SQL Managed Instance using DMVs
      1. Monitoring database metrics
      2. Monitoring connections
      3. Monitoring query performance
      4. Monitoring blocking
      5. Extended events
      6. Examining queries using extended events
    6. Tuning an Azure SQL database
      1. Automatic tuning
      2. In-memory technologies
      3. In-memory OLTP
      4. Memory-optimized tables
      5. Natively compiled procedures
      6. Columnstore indexes
      7. Monitoring cost
      8. Activity: Exploring the in-memory OLTP feature
      9. Monitoring and tuning an Azure SQL Managed Instance
      10. General Purpose instance I/O characteristics
      11. Monitoring the first run with the default file configuration of the TPC-C database
    7. Summary
  13. 11. Database features
    1. Azure SQL Data Sync
      1. Activity: Configuring Data Sync between two Azure SQL databases using PowerShell
    2. Online and resumable DDL operations
    3. SQL Graph queries and improvements
      1. Graph database integrity using edge constraints
    4. Machine Learning Services
      1. Differences between Machine Learning Services in SQL Server and Azure SQL Managed Instance
      2. Activity: Run basic Python scripts
      3. Activity: Using Machine Learning Services in Azure SQL Managed Instance to forecast monthly sales for the toystore database
    5. Distributed transactions in Azure SQL Managed Instance
      1. Server Trust Group
      2. Activity: Creating a Server Trust Group using the Azure portal
      3. Activity: Running distributed transactions using T-SQL
    6. Summary
  14. 12. App modernization
    1. Migrating an SQL Server workload to SQL Managed Instance
      1. Backup and restore
      2. SQL installation and patches
      3. Scaling
      4. High availability and disaster recovery
      5. Newly introduced features
      6. Support for hosting SSRS catalog databases
      7. Azure Machine Learning
      8. Distributed transaction support
    2. SQL Database serverless
      1. Serverless use cases
    3. Creating a serverless database
      1. Auto-scaling in serverless
      2. Cache Reclamation
      3. Auto-pausing in serverless
      4. Auto-resuming in serverless
      5. SQL Database serverless billing
      6. Demonstration of auto-scaling and compute billing in serverless
      7. Serverless vs. provisioned compute
    4. Scaling to the Hyperscale service tier
      1. Considering moving to the Hyperscale service tier
      2. Activity: Updating an existing SQL database to the Hyperscale service tier using the Azure portal
      3. Activity: Updating an existing SQL database to the Hyperscale service tier using PowerShell commands
      4. Read scale-out an SQL Hyperscale database
    5. Summary
  15. Index

Product information

  • Title: Professional Azure SQL Managed Database Administration - Third Edition
  • Author(s): Ahmad Osama, Shashikant Shakya
  • Release date: March 2021
  • Publisher(s): Packt Publishing
  • ISBN: 9781801076524