Professional Azure SQL Database Administration

Professional Azure SQL Database Administration

by Ahmad Osama
Released July 2018
Publisher(s): Packt Publishing
ISBN: 9781789538854

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

Discover how you can migrate a traditional on-premise SQL server database to a cloud-based solution with Microsoft Azure. Built with database administrators in mind, this book emulates different scenarios you might come across while working with large, complex SQL database migrations and provides solutions for effectively managing the migrated databases.

Key Features

  • Implement backup, restore, and recovery of Azure SQL databases
  • Create shards and elastic pools to scale Azure SQL databases
  • Automate common management tasks with PowerShell
  • Implement over 40 practical activities and exercises across 24 topics to reinforce your learning

Book Description

As the cloud version of SQL Server, Azure SQL Database differs in key ways when it comes to management, maintenance, and administration. It's important to know how to administer SQL Database to fully benefit from all of the features and functionality that it provides. This book addresses important aspects of an Azure SQL Database instance such as migration, backup restorations, pricing policies, security, scalability, monitoring, performance optimization, high availability, and disaster recovery. It is a complete guide for database administrators, and ideal for those who are planning to migrate from on premise SQL Server database to an Azure SQL Server database.

What you will learn

  • Learn how to provision a new database or migrate an existing on-premise solution
  • Understand how to backup, restore, secure, and scale your own Azure SQL Database
  • Optimize the performance by monitoring and tuning your cloud-based SQL instance
  • Implement high availability and disaster recovery procedures with SQL Database
  • Develop a roadmap for your own scalable cloud solution with Azure SQL Database

Who this book is for

This book is ideal for database administrators, database developers, or application developers who are interested in developing or migrating existing applications with Azure SQL Database. Prior experience of working with an on-premise SQL Server deployment and brief knowledge of PowerShell and C# are recommended prerequisites.

Table of contents

  1. Professional Azure SQL Database Administration
    1. Table of Contents
    2. Professional Azure SQL Database Administration
      1. Why Subscribe?
      2. PacktPub.com
    3. Contributors
      1. About the Author
      2. About the Reviewer
      3. Packt is Searching for Authors Like You
    4. Preface
      1. What This Book Covers
      2. What You Need For This Book
        1. Hardware
        2. Software
      3. Who This Book is for
        1. Conventions
      4. Download the example code files
      5. Installation and Setup
        1. Sign up for a Free Azure Account
        2. Install SQL Server Management Studio
        3. Installing Microsoft PowerShell 5.1
        4. Installing Microsoft Azure PowerShell
        5. Installing Microsoft RML Utilities
        6. Installing Visual Studio Community Edition
        7. Get in touch
        8. Reviews
    5. 1. Microsoft Azure SQL Database Primer
      1. Azure SQL Database Architecture
        1. Azure SQL Database Request Flow
        2. Provisioning an Azure SQL Database
        3. Connecting and Querying the SQL Database from the Azure Portal
        4. Connecting and Querying the SQL Database from SQL Server Management Studio
          1. Deleting Resources
          2. Differences between Azure SQL Database 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
          3. Unsupported Features
            1. SQL Browser Service
            2. File Stream
            3. Common Language Runtime (SQL CLR)
            4. Resource Governor
            5. Global Temporary Tables
            6. Log Shipping
            7. SQL Trace and Profiler
            8. Trace Flags
            9. System Stored Procedures
            10. USE Statement
        5. Activity: Provisioning Azure SQL Server and SQL Database using PowerShell
          1. Provisioning Azure SQL Database
          2. Executing the PowerShell Script
      2. Summary
    6. 2. Migrating a SQL Server Database to an Azure SQL Database
      1. Finding the Migration Benefits
      2. Finding the Blockers
      3. Selecting a Service Model
      4. Selecting a Service Tier
      5. Selecting the Main and Disaster Recovery Region
      6. Selecting a Migration Tool
      7. Azure SQL Database Service Tiers
        1. Basic Service Tier
        2. Standard Service Tier
        3. Premium Service Tier
        4. Premium RS Service Tier
        5. Database Transaction Units
        6. Scaling up the Azure SQL Database Service Tier
          1. Changing a Service Tier
      8. Determining an Appropriate Service Tier
        1. Azure SQL Database DTU Calculator
        2. Finding an Initial Service Tier for the Database to be Migrated using the DTU Calculator
      9. Determining Compatibility Issues
        1. Data Migration Assistant
        2. SQL Server Data Tools for Visual Studio
        3. SQL Server Management Studio
        4. SQLPackage.exe
        5. SQL Azure Migration Wizard
      10. Determining the Migration Method
        1. Migrating the On-Premises SQL Server Database to Azure SQL Database
        2. Activity: Using Data Migration Assistant
        3. Activity: Performing Transactional Replication
      11. Summary
    7. 3. Backing Up the Azure SQL Database
      1. Automatic Backups
        1. Backup Storage
          1. Backup Retention Period
        2. Configuring Long-Term Backup Retention for Azure SQL Database
      2. Manual Backups
        1. Backing up the Azure SQL Database using SSMS
        2. DACPAC and BACPAC
        3. Manual versus Automated Backups
        4. Activity: Perform Back-ups
      3. Summary
    8. 4. Restoring an Azure SQL Database
      1. Restore Types
        1. Point-In-Time Restore
        2. Long-Term Database Restore
          1. Restoring Deleted Databases
          2. Geo-Restore Database
        3. Importing a Database
        4. Activity: Perform PITR
        5. Activity: Perform Geo-Restore
      2. Summary
    9. 5. Securing an Azure SQL Database
      1. Access Control
        1. Firewall Rules
        2. Managing the Server-Level Firewall Rules using the Azure Portal
        3. Managing the Server-Level Firewall Rules using Transact-SQL
        4. Managing Database-Level Firewall Rules using Transact-SQL
        5. Authentication
          1. SQL Authentication
          2. Azure Active Directory Authentication
            1. Azure Active Directory
            2. Active Directory – Password
            3. Azure Directory – Integrated
            4. Active Directory – Universal with MFA Support
        6. Using Active Directory – Password to Authenticate to an Azure SQL Database
        7. Azure SQL Database Authentication Structure
          1. Azure SQL Database Authentication Considerations
        8. Authorization
          1. Server-Level Administrative Roles
          2. Database Creators
          3. Non-Administrative Users
        9. Creating Contained Database Users for Azure AD Authentication
        10. Groups and Roles
        11. Row-Level Security
        12. Dynamic Data Masking
      2. Proactive Security
        1. Auditing
        2. Configure Auditing for Azure SQL Server
          1. Threat Detection
        3. Configure Threat Detection for Azure SQL Server
        4. Activity: Implement Row-Level Security
        5. Activity: Implement Dynamic Data Masking
      3. Summary
    10. 6. Scaling Out Azure SQL Database
      1. Vertical Scaling
        1. Scale Up or Scale Down Service Tiers
        2. Using T-SQL to Change the Service Tier
        3. Vertical Partitioning
      2. Horizontal Scaling
        1. Shard Map Manager
          1. Data Dependent Routing
          2. Multi-Tenant Data Models
          3. Single Tenant (database-per-tenant)
          4. Shared Database – Sharded
        2. Activity: Creating Alerts
        3. Activity: Creating Shards
        4. Activity: Split Data between Shard
        5. Activity: Using Elastic Queries
      3. Summary
    11. 7. Elastic Pools
      1. Introducing Elastic Pools
        1. When Should You Consider Elastic Pools?
        2. Sizing an Elastic Pool
        3. Comparing the Pricing
          1. Sizing Best Practices
        4. Create an Elastic Pool and Add Toystore Store Shards to the Elastic Pool
      2. Elastic Jobs
        1. Use Cases
          1. Architecture
          2. Elastic Job Workflow
        2. Create an Elastic Job using the Azure Portal
        3. Activity: Exploring Elastic Pools
          1. Executing the PowerShell Script
      3. Summary
    12. 8. High Availability and Disaster Recovery
      1. High Availability
      2. Disaster Recovery
        1. Standard Geo-Replication
          1. Database Failover
        2. Active Geo-Replication
          1. Database Failover
        3. Synchronous Replication
          1. Auto-Failover Groups
          2. Auto-Failover Group Terms
        4. Configuring Active Geo-Replication and Performing a Manual Failover
        5. Configuring the Auto-Failover Group
        6. Activity: Configure Active Geo-Replication
          1. Executing the PowerShell Script
        7. Activity: Configure Auto-Failover Groups
          1. Executing the PowerShell Script
      3. Summary
    13. 9. Monitoring and Tuning Azure SQL Database
      1. Monitoring an Azure SQL Database using the Azure Portal
        1. Monitoring Database Metrics
        2. Alert Rules, Database Size, and Diagnostic Settings
          1. Alert Rules
          2. Database Size
          3. Diagnostic Settings
        3. Query Performance Insight
        4. Monitor Queries using the Query Performance Insight Blade
        5. Monitoring an Azure SQL Database using DMVs
          1. Monitoring Database Metrics
            1. sys.resource_stats
            2. sys.dm_db_resource_stats
          2. Monitoring Connections
          3. Monitoring Query Performance
            1. sys.dm_exec_query_stats
          4. Monitoring Blocking
          5. Extended Events
        6. Examining Queries
      2. Tuning an Azure SQL Database
        1. Automatic Tuning
          1. Create Index
          2. Drop Indexes
          3. Force Last Good Plan
        2. In-Memory Technologies
          1. In-Memory OLTP
            1. Memory-Optimized Tables
            2. Natively Compiled Procedures
          2. Columnstore Indexes
            1. Clustered Columnstore Index
            2. Non-Clustered Columnstore Index
        3. Activity: Explore the In-Memory OLTP Feature
      3. Summary
    14. Index

Product information

  • Title: Professional Azure SQL Database Administration
  • Author(s): Ahmad Osama
  • Release date: July 2018
  • Publisher(s): Packt Publishing
  • ISBN: 9781789538854