Professional Azure SQL Database Administration电子书

Professional Azure SQL Database Administration

Why Subscribe?

PacktPub.com

Contributors

About the Author

About the Reviewer

Packt is Searching for Authors Like You

Preface

What This Book Covers

What You Need For This Book

Hardware

Software

Who This Book is for

Conventions

Note

Tip

Download the example code files

Installation and Setup

Sign up for a Free Azure Account

Install SQL Server Management Studio

Installing Microsoft PowerShell 5.1

Installing Microsoft Azure PowerShell

Installing Microsoft RML Utilities

Installing Visual Studio Community Edition

Get in touch

Reviews

Chapter 1. Microsoft Azure SQL Database Primer

Note

Azure SQL Database Architecture

Note

Note

Azure SQL Database Request Flow

Note

Provisioning an Azure SQL Database

Note

Connecting and Querying the SQL Database from the Azure Portal

Connecting and Querying the SQL Database from SQL Server Management Studio

Note

Note

Deleting Resources

Note

Note

Differences between Azure SQL Database and SQL Server

Backup and Restore

Recovery Model

Note

SQL Server Agent

Change Data Capture

Auditing

Mirroring

Table Partitioning

Replication

Multi-Part Names

Unsupported Features

SQL Browser Service

File Stream

Common Language Runtime (SQL CLR)

Resource Governor

Global Temporary Tables

Log Shipping

SQL Trace and Profiler

Trace Flags

System Stored Procedures

USE Statement

Activity: Provisioning Azure SQL Server and SQL Database using PowerShell

Note

Note

Provisioning Azure SQL Database

Executing the PowerShell Script

Summary

Chapter 2. Migrating a SQL Server Database to an Azure SQL Database

Finding the Migration Benefits

Finding the Blockers

Selecting a Service Model

Selecting a Service Tier

Selecting the Main and Disaster Recovery Region

Selecting a Migration Tool

Azure SQL Database Service Tiers

Note

Basic Service Tier

Standard Service Tier

Premium Service Tier

Premium RS Service Tier

Note

Database Transaction Units

Scaling up the Azure SQL Database Service Tier

Changing a Service Tier

Determining an Appropriate Service Tier

Azure SQL Database DTU Calculator

Note

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

Note

Determining Compatibility Issues

Data Migration Assistant

SQL Server Data Tools for Visual Studio

SQL Server Management Studio

SQLPackage.exe

SQL Azure Migration Wizard

Determining the Migration Method

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

Note

Activity: Using Data Migration Assistant

Activity: Performing Transactional Replication

Summary

Chapter 3. Backing Up the Azure SQL Database

Automatic Backups

Note

Backup Storage

Backup Retention Period

Configuring Long-Term Backup Retention for Azure SQL Database

Manual Backups

Backing up the Azure SQL Database using SSMS

Note

DACPAC and BACPAC

Note

Manual versus Automated Backups

Activity: Perform Back-ups

Note

Summary

Chapter 4. Restoring an Azure SQL Database

Restore Types

Point-In-Time Restore

Long-Term Database Restore

Restoring Deleted Databases

Geo-Restore Database

Importing a Database

Activity: Perform PITR

Activity: Perform Geo-Restore

Summary

Chapter 5. Securing an Azure SQL Database

Access Control

Firewall Rules

Note

Managing the Server-Level Firewall Rules using the Azure Portal

Managing the Server-Level Firewall Rules using Transact-SQL

Note

Managing Database-Level Firewall Rules using Transact-SQL

Note

Note

Authentication

SQL Authentication

Azure Active Directory Authentication

Azure Active Directory

Active Directory – Password

Azure Directory – Integrated

Active Directory – Universal with MFA Support

Note

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

Note

Note

Note

Note

Azure SQL Database Authentication Structure

Note

Azure SQL Database Authentication Considerations

Authorization

Server-Level Administrative Roles

Database Creators

Non-Administrative Users

Creating Contained Database Users for Azure AD Authentication

Note

Groups and Roles

Row-Level Security

Dynamic Data Masking

Proactive Security

Auditing

Configure Auditing for Azure SQL Server

Threat Detection

Configure Threat Detection for Azure SQL Server

Activity: Implement Row-Level Security

Activity: Implement Dynamic Data Masking

Summary

Chapter 6. Scaling Out Azure SQL Database

Vertical Scaling

Scale Up or Scale Down Service Tiers

Note

Using T-SQL to Change the Service Tier

Note

Note

Note

Vertical Partitioning

Horizontal Scaling

Shard Map Manager

Data Dependent Routing

Multi-Tenant Data Models

Single Tenant (database-per-tenant)

Shared Database – Sharded

Activity: Creating Alerts

Note

Note

Activity: Creating Shards

Note

Note

Note

Note

Note

Note

Note

Activity: Split Data between Shard

Note

Note

Activity: Using Elastic Queries

Note

Note

Summary

Chapter 7. Elastic Pools

Introducing Elastic Pools

When Should You Consider Elastic Pools?

Sizing an Elastic Pool

Comparing the Pricing

Sizing Best Practices

Note

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

Elastic Jobs

Use Cases

Architecture

Elastic Job Workflow

Create an Elastic Job using the Azure Portal

Note

Activity: Exploring Elastic Pools

Note

Executing the PowerShell Script

Note

Summary

Chapter 8. High Availability and Disaster Recovery

High Availability

Disaster Recovery

Standard Geo-Replication

Database Failover

Active Geo-Replication

Database Failover

Synchronous Replication

Auto-Failover Groups

Auto-Failover Group Terms

Note

Configuring Active Geo-Replication and Performing a Manual Failover

Note

Note

Note

Configuring the Auto-Failover Group

Activity: Configure Active Geo-Replication

Note

Executing the PowerShell Script

Activity: Configure Auto-Failover Groups

Note

Executing the PowerShell Script

Summary

Chapter 9. Monitoring and Tuning Azure SQL Database

Monitoring an Azure SQL Database using the Azure Portal

Monitoring Database Metrics

Note

Note

Alert Rules, Database Size, and Diagnostic Settings

Alert Rules

Database Size

Diagnostic Settings

Query Performance Insight

Note

Monitor Queries using the Query Performance Insight Blade

Note

Monitoring an Azure SQL Database using DMVs

Monitoring Database Metrics

sys.resource_stats

Note

sys.dm_db_resource_stats

Monitoring Connections

Monitoring Query Performance

Note

sys.dm_exec_query_stats

Monitoring Blocking

Extended Events

Examining Queries

Note

Note

Note

Tuning an Azure SQL Database

Automatic Tuning

Create Index

Drop Indexes

Force Last Good Plan

In-Memory Technologies

In-Memory OLTP

Memory-Optimized Tables

Natively Compiled Procedures

Note

Columnstore Indexes

Note

Clustered Columnstore Index

Non-Clustered Columnstore Index

Note

Activity: Explore the In-Memory OLTP Feature

Summary

Index

A

B

C

D

E

F

G

H

I

L

M

N

O

P

Q

R

S

T

U

V

W