Professional Azure SQL Database Administration电子书

Preface

About the Book

About the Author

Objectives

Audience

Approach

Hardware Requirements

Software Requirements

Conventions

Installation and Setup

Installing the Code Bundle

Additional Resources

Chapter 1

Microsoft Azure SQL Database Primer

Introduction

The Azure SQL Database Architecture

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

Differences between Azure SQL Database and SQL Server

Unsupported Features

Introduction to Managed Instance

Purchasing Model

Connecting to SQL Managed Instance

Exercise: Provisioning a SQL-Managed Instance Using the Azure Portal

Activity: Provisioning an Azure SQL Server and SQL Database Using PowerShell

Exercise: Provisioning a Managed Instance

Summary

Chapter 2

Migrating a SQL Server Database to an Azure SQL Database

Introduction

Finding the Migration Benefits

Finding the Blockers

Selecting a Service Model

Selecting a Service Tier

Selecting the Main Region and Disaster Recovery Region

Selecting a Migration Tool

Choosing Between Azure SQL Database and SQL Database Managed Instance

Features

Migration

Time to Develop and Market

Azure SQL Database Service Tiers

DTU Pricing Models

Database Transaction Units

vCore Pricing Model

Hardware Generations

vCore Service Tiers

Azure Hybrid Benefit

Hyperscale Service Tier

Exercise: Provisioning a Hyperscale SQL Database Using PowerShell

Scaling up the Azure SQL Database Service Tier

Changing a Service Tier

Choosing Between a vCore Pricing Model and DTU-based Service Tiers

Licensing

Flexibility

Determining an Appropriate DTU Based Service Tier

Azure SQL Database DTU Calculator

DTU Calculator Work Flow

Finding an Initial Service Tier for the Database to be Migrated Using the DTU Calculator

Overall Recommendation

Recommendation Based on CPU Utilization

Recommendation Based on IOPs Utilization

Recommendation Based on Log Utilization

Determining Compatibility Issues

Data Migration Assistant

SQL Server Data Tools (SSDT) for Visual Studio

SQL Server Management Studio (SSMS)

SQLPackage.exe

SQL Azure Migration Wizard

Azure Database Migration Services

Exercise: Migrating a SQL Server Database to Azure SQL Database Using Azure DMS

Determining the Migration Method

Migrating an On-Premises SQL Server Database to Azure SQL Database

Activity: Using DMA

Activity: Performing Transactional Replication

Summary

Chapter 3

Backing Up an Azure SQL Database

Introduction

Automatic Backups

Backup Storage

Backup Retention Period

Configuring Long-Term Backup Retention for Azure SQL Database

Manual Backups

Backing up an Azure SQL Database Using SQL Server Management Studio (SSMS)

DACPAC and BACPAC

Manual versus Automated Backups

Activity: Performing Backups

Summary

Chapter 4

Restoring an Azure SQL Database

Introduction

Restore Types

Point-In-Time Restore

Long-Term Database Restore

Restoring Deleted Databases

Geo-Restore Database

Importing a Database

Activity: Performing PITR

Activity: Performing Geo-Restore

Summary

Chapter 5

Securing an Azure SQL Database

Introduction

Access Control

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

Authentication

SQL Authentication

Azure Active Directory Authentication

Using Active Directory – Password to Authenticate to an Azure SQL Database

Azure SQL Database Authentication Structure

Azure SQL Database Authentication Considerations

Authorization

Server-Level Administrative Roles

Database Creators

Login Manager

Non-Administrative Users

Creating Contained Database Users for Azure AD Authentication

Groups and Roles

Row-level Security

Dynamic Data Masking

Advanced Data Security

Exercise: Exploring Advanced Data Security Features

Auditing

Exercise: Configuring SQL Database Auditing

Activity: Implementing Row-level Security

Activity: Implementing Dynamic Data Masking

Activity: Implementing Advanced Data Security to Detect SQL Injection

Summary

Chapter 6

Scaling out an Azure SQL Database

Introduction

Vertical Scaling

Scale-up or Scale-down Service Tiers

Using T-SQL to Change the Service Tier

Vertical Partitioning

Horizontal Scaling

Shard Map Manager

Activity: Creating Alerts

Activity: Creating Shards

Activity: Splitting Data Between Shards

Activity: Using Elastic Queries

Summary

Chapter 7

Azure SQL Database Elastic Pools

Introducing Elastic Pools

When Should You Consider Elastic Pools?

Sizing an Elastic Pool

Comparing the Pricing

Sizing Best Practices

Create an Elastic Pool and Add Toystore Shards to the Elastic Pool

Elastic Database Jobs

Elastic Job Agent

Job Database

Target Group

Job

Use Cases

Exercise: Configuring an Elastic Database Job Using T-SQL

Activity: Exploring Elastic Pools

Summary

Chapter 8

High Availability and Disaster Recovery

High Availability

Accelerated Database Recovery (ADR)

Zone Redundant Configuration

Disaster Recovery

Standard Geo-Replication

Active Geo-Replication

Synchronous Replication

Auto-Failover Groups

Configuring Active Geo-Replication and Performing Manual Failover

Configuring the Auto-Failover Group

Activity: Configuring Active Geo-Replication

Activity: Configuring Auto-Failover Groups

Activity: Evaluating Accelerated Database Recovery

Summary

Chapter 9

Monitoring and Tuning Azure SQL Database

Introduction

Monitoring an Azure SQL Database Using the Azure Portal

Monitoring Database Metrics

Alert Rules, Database Size, and Diagnostic Settings

Database Data Storage

Diagnostic Settings

Query Performance Insight

Creating Alerts

Activity: Monitoring Azure SQL Database with Log Analytics and Power BI

Monitoring Queries Using the Query Performance Insight Blade

Monitoring an Azure SQL Database Using DMVs

Monitoring Database Metrics

Monitoring Connections

Monitoring Query Performance

Monitoring Blocking

Extended Events

Examining Queries

Tuning an Azure SQL Database

Automatic Tuning

In-Memory Technologies

In-Memory OLTP

Activity: Exploring the In-Memory OLTP Feature

Summary

Chapter 10

Database Features

Introduction

Azure SQL Data Sync

Activity: Configuring Data Sync between Two Azure SQL Databases Using PowerShell

Online and Resumable Data Definition Language (DDL) Operations

SQL Graph Queries and Improvements

Edge Constraints

Machine Learning Services

Differences between Azure SQL Database Machine Learning Services and SQL Server Machine Learning Services

Activity: Using Machine Learning Services in an Azure SQL Database to Forecast Monthly Sales for the toystore Database

Summary