Professional Azure SQL Database Administration - Second Edition

Professional Azure SQL Database Administration - Second Edition

by Ahmad Osama
Released July 2019
Publisher(s): Packt Publishing
ISBN: 9781789802542

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

Leverage the features of Azure SQL database and become an expert in data management

Key Features

  • Explore ways to create shards and elastic pools to scale Azure SQL databases
  • Automate common management tasks with PowerShell
  • Implement over 40 practical activities and exercises to reinforce your learning

Book Description

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 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 it is 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 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.

What you will learn

  • 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
  • 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

Who this book is for

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, 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.

Table of contents

  1. Preface
    1. About the Book
      1. About the Author
      2. Objectives
      3. Audience
      4. Approach
      5. Hardware Requirements
      6. Software Requirements
      7. Conventions
      8. Installation and Setup
      9. Installing the Code Bundle
      10. Additional Resources
  2. Chapter 1
  3. Microsoft Azure SQL Database Primer
    1. Introduction
    2. The Azure SQL Database Architecture
      1. 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. Differences between Azure SQL Database and SQL Server
      1. Unsupported Features
    5. Introduction to Managed Instance
      1. Purchasing Model
    6. Connecting to SQL Managed Instance
      1. Exercise: Provisioning a SQL-Managed Instance Using the Azure Portal
      2. Activity: Provisioning an Azure SQL Server and SQL Database Using PowerShell
      3. Exercise: Provisioning a Managed Instance
    7. Summary
  4. Chapter 2
  5. Migrating a SQL Server Database to an Azure SQL Database
    1. Introduction
      1. Finding the Migration Benefits
      2. Finding the Blockers
      3. Selecting a Service Model
      4. Selecting a Service Tier
      5. Selecting the Main Region and Disaster Recovery Region
      6. Selecting a Migration Tool
      7. Choosing Between Azure SQL Database and SQL Database Managed Instance
      8. Features
      9. Migration
      10. Time to Develop and Market
      11. Azure SQL Database Service Tiers
    2. DTU Pricing Models
      1. Database Transaction Units
      2. vCore Pricing Model
      3. Hardware Generations
      4. vCore Service Tiers
      5. Azure Hybrid Benefit
      6. Hyperscale Service Tier
      7. Exercise: Provisioning a Hyperscale SQL Database Using PowerShell
      8. Scaling up the Azure SQL Database Service Tier
      9. Changing a Service Tier
    3. Choosing Between a vCore Pricing Model and DTU-based Service Tiers
      1. Licensing
      2. Flexibility
      3. Determining an Appropriate DTU Based Service Tier
      4. Azure SQL Database DTU Calculator
      5. DTU Calculator Work Flow
      6. Finding an Initial Service Tier for the Database to be Migrated Using the DTU Calculator
      7. Overall Recommendation
      8. Recommendation Based on CPU Utilization
      9. Recommendation Based on IOPs Utilization
      10. Recommendation Based on Log Utilization
      11. Determining Compatibility Issues
      12. Data Migration Assistant
      13. SQL Server Data Tools (SSDT) for Visual Studio
      14. SQL Server Management Studio (SSMS)
      15. SQLPackage.exe
      16. SQL Azure Migration Wizard
    4. Azure Database Migration Services
      1. Exercise: Migrating a SQL Server Database to Azure SQL Database Using Azure DMS
      2. Determining the Migration Method
      3. Migrating an On-Premises SQL Server Database to Azure SQL Database
      4. Activity: Using DMA
      5. Activity: Performing Transactional Replication
    5. Summary
  6. Chapter 3
  7. Backing Up an Azure SQL Database
    1. Introduction
    2. Automatic Backups
      1. Backup Storage
      2. Backup Retention Period
      3. Configuring Long-Term Backup Retention for Azure SQL Database
    3. Manual Backups
      1. Backing up an Azure SQL Database Using SQL Server Management Studio (SSMS)
      2. DACPAC and BACPAC
      3. Manual versus Automated Backups
      4. Activity: Performing Backups
    4. Summary
  8. Chapter 4
  9. Restoring an Azure SQL Database
    1. Introduction
    2. Restore Types
      1. Point-In-Time Restore
      2. Long-Term Database Restore
      3. Restoring Deleted Databases
      4. Geo-Restore Database
      5. Importing a Database
      6. Activity: Performing PITR
      7. Activity: Performing Geo-Restore
    3. Summary
  10. Chapter 5
  11. Securing an Azure SQL Database
    1. Introduction
    2. Access Control
      1. Firewall Rules
      2. Managing Server-Level Firewall Rules Using the Azure Portal
      3. Managing Server-Level Firewall Rules Using Transact-SQL
      4. Managing Database-level Firewall Rules Using Transact-SQL
    3. Authentication
      1. SQL Authentication
      2. Azure Active Directory Authentication
      3. Using Active Directory – Password to Authenticate to an Azure SQL Database
    4. Azure SQL Database Authentication Structure
      1. Azure SQL Database Authentication Considerations
    5. Authorization
      1. Server-Level Administrative Roles
      2. Database Creators
      3. Login Manager
      4. Non-Administrative Users
    6. Creating Contained Database Users for Azure AD Authentication
      1. Groups and Roles
      2. Row-level Security
      3. Dynamic Data Masking
    7. Advanced Data Security
      1. Exercise: Exploring Advanced Data Security Features
    8. Auditing
      1. Exercise: Configuring SQL Database Auditing
      2. Activity: Implementing Row-level Security
      3. Activity: Implementing Dynamic Data Masking
      4. Activity: Implementing Advanced Data Security to Detect SQL Injection
    9. Summary
  12. Chapter 6
  13. Scaling out an Azure SQL Database
    1. Introduction
    2. Vertical Scaling
      1. Scale-up or Scale-down Service Tiers
      2. Using T-SQL to Change the Service Tier
      3. Vertical Partitioning
      4. Horizontal Scaling
      5. Shard Map Manager
      6. Activity: Creating Alerts
      7. Activity: Creating Shards
      8. Activity: Splitting Data Between Shards
      9. Activity: Using Elastic Queries
    3. Summary
  14. Chapter 7
  15. Azure SQL Database Elastic Pools
    1. Introducing Elastic Pools
      1. When Should You Consider Elastic Pools?
      2. Sizing an Elastic Pool
      3. Comparing the Pricing
      4. Sizing Best Practices
      5. Create an Elastic Pool and Add Toystore Shards to the Elastic Pool
    2. Elastic Database Jobs
      1. Elastic Job Agent
      2. Job Database
      3. Target Group
      4. Job
      5. Use Cases
      6. Exercise: Configuring an Elastic Database Job Using T-SQL
      7. Activity: Exploring Elastic Pools
    3. Summary
  16. Chapter 8
  17. High Availability and Disaster Recovery
    1. High Availability
    2. Accelerated Database Recovery (ADR)
      1. Zone Redundant Configuration
    3. Disaster Recovery
      1. Standard Geo-Replication
      2. Active Geo-Replication
    4. Synchronous Replication
      1. Auto-Failover Groups
      2. Configuring Active Geo-Replication and Performing Manual Failover
      3. Configuring the Auto-Failover Group
      4. Activity: Configuring Active Geo-Replication
      5. Activity: Configuring Auto-Failover Groups
      6. Activity: Evaluating Accelerated Database Recovery
    5. Summary
  18. Chapter 9
  19. Monitoring and Tuning Azure SQL Database
    1. Introduction
    2. Monitoring an Azure SQL Database Using the Azure Portal
      1. Monitoring Database Metrics
      2. Alert Rules, Database Size, and Diagnostic Settings
      3. Database Data Storage
      4. Diagnostic Settings
      5. Query Performance Insight
      6. Creating Alerts
      7. Activity: Monitoring Azure SQL Database with Log Analytics and Power BI
    3. Monitoring Queries Using the Query Performance Insight Blade
    4. Monitoring an Azure SQL Database Using DMVs
      1. Monitoring Database Metrics
      2. Monitoring Connections
      3. Monitoring Query Performance
      4. Monitoring Blocking
      5. Extended Events
      6. Examining Queries
    5. Tuning an Azure SQL Database
      1. Automatic Tuning
      2. In-Memory Technologies
      3. In-Memory OLTP
      4. Activity: Exploring the In-Memory OLTP Feature
    6. Summary
  20. Chapter 10
  21. Database Features
    1. Introduction
      1. Azure SQL Data Sync
      2. Activity: Configuring Data Sync between Two Azure SQL Databases Using PowerShell
      3. Online and Resumable Data Definition Language (DDL) Operations
    2. SQL Graph Queries and Improvements
      1. Edge Constraints
      2. Machine Learning Services
      3. Differences between Azure SQL Database Machine Learning Services and SQL Server Machine Learning Services
      4. Activity: Using Machine Learning Services in an Azure SQL Database to Forecast Monthly Sales for the toystore Database
    3. Summary

Product information

  • Title: Professional Azure SQL Database Administration - Second Edition
  • Author(s): Ahmad Osama
  • Release date: July 2019
  • Publisher(s): Packt Publishing
  • ISBN: 9781789802542