Cover image for Professional Azure SQL Managed Database Administration - Third Edition

Professional Azure SQL Managed Database Administration - Third Edition

Release Date: 2021/03/01
ISBN: 9781801076524
Topic:
0%
16
Chapters
0-1
Hours read
0k
Total Words
    Start Reading Now    
       Add to Wishlist       
View table of contents

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