Azure SQL Revealed: A Guide to the Cloud for SQL Server Professionals

✍ Scribed by Bob Ward

• Publisher: Apress
• Year: 2020
• Tongue: English
• Leaves: 519
• Edition: 1
• Category: Library

✦ Synopsis

Intermediate-Advanced user level

✦ Table of Contents

Table of Contents
About the Author
About the Technical Reviewer
Foreword
Acknowledgments
Introduction
Chapter 1: SQL Server Rises to the Clouds
CloudDB
The Red Dog
The Azure Services Platform
The Road to SQL Azure
SQL Data Services
SQL Azure Is Born
The SAWA Project
The Virtual Machine Initiative
Becoming Azure SQL Database
The Sterling (SAWAv2) Project
New Editions, the DTU, and Previews
Intelligent Performance and the MDCS
Advanced Data Security and the ILDC Team
A Pane for the Future Called Ibiza
A New Engineering Model for Azure
Bending Azure SQL Database
Lifting Customers to the Cloud
Project Socrates Goes Hyper
Azure SQL Today
Chapter 2: What Is Azure SQL?
The Azure Ecosystem
Azure Accounts and Subscriptions
The Azure Portal
The Azure Marketplace
Azure API and CLI
Azure Resource Manager (ARM)
Azure Monitor
Azure Regions and Datacenters
Azure Service-Level Agreement (SLA)
What Is the Azure SQL?
IaaS vs. PaaS
The Azure SQL Lineup
Azure SQL Managed Instance
Managed Instance Capabilities
Managed Instance Options and Limits
General Purpose
Business Critical
Managed Instance Pools
Managed Instance vs. SQL Server on Azure Virtual Machine
Customers Using Managed Instance
Azure SQL Database
Azure SQL Database Capabilities
Azure SQL Database Options and Limits
DTU vs. vCore
General Purpose
Serverless
Business Critical
Hyperscale
Elastic Pool Databases
Azure SQL Database vs. Azure SQL Managed Instance
Customers Using Azure SQL Database
Interfaces for Azure SQL
Azure Portal
az CLI
PowerShell
REST API
TDS and T-SQL
SQL CLI
SQL Server Management Studio (SSMS)
Azure Data Studio (ADS)
Visual Studio Tools
Summary
Chapter 3: SQL Server on Azure Virtual Machine
Deploying
Pricing
SQL Server Gallery Images
Resource Group, Region, and Availability
Resource Group
Virtual Machine Name
Region
Availability Options
Image
Virtual Machine Sizes
Account, Port, and OS Licensing
Making Configuration Choices As Part of Deploy
OS Disks
Networking
Management
Advanced
SQL Server Settings
Tags
Deploy!
Navigating in the Portal
Connecting to Your VM
Exploring the SQL Server Installation
What Is Installed
What Is Configured
Deploy on Your Own
Using a CLI and ARM Template
Reserved Instances and Dedicated Hosts
Migrate Using Azure Migrate
Restoring a Database
Using Data Migration Assistant (DMA)
Using Azure Migrate Server Migration
Deploying SQL Server on Linux with Azure Virtual Machine
Deploying SQL Server Containers
SQL Virtual Machine Resource Provider
Configuration
Stopping vs. Deallocating
Resizing
Security
RBAC
Advanced Data Security
Other Config Options
Maximizing Storage Performance
Performance Monitoring
Azure Metrics
Logs
Insights
Networking
HADR
Azure Storage
Backups
Always On Failover Cluster Instance
Always On Availability Groups
Go Further with Azure Availability
SQL Server and Linux Availability
Summary
Chapter 4: Deploying Azure SQL
Pre-deployment Planning
New Deployment or Migration
Making Deployment Choices
Deployment Method
Deployment Option
Region
Purchasing Model
Service Tier (SLO)
Hardware
Sizes
Price
Consider Resource Limits
Deploying Azure SQL Managed Instance
Deployment and Options
Basics
Networking
Additional Settings
Tags
Deploy!
Deploying with a CLI
Implementation Details
Connecting and Verifying Deployment
Connect to a Managed Instance
Verify the Deployment
Migrating to Azure SQL Managed Instance
Assessment and Planning
Migration
Application Changes
Post Migration
Deploying an Azure SQL Database
Deployment and Options
Basics
Networking
Additional Settings
Tags
Deploy It!
Deploying Business Critical
Deploying Serverless
Deploying Hyperscale
Deploying with a CLI
Implementation Details
Dedicated Rings and Instances
The Logical Server
Storage, Compute, and Gateways
Serverless
Hyperscale
Resource Governance
Connecting and Verifying Deployment
Connecting to Azure SQL Database
Verifying Deployment
Migrating to Azure SQL Database
Summary
Chapter 5: Configuring Azure SQL
Configuring Azure SQL Managed Instance
sp_configure
Trace Flags
Tempdb
Master and Model
Configuring Edition
Networking Configuration
Configuring Databases
Configuring Azure SQL Database
Creating New Databases
Altering Databases
Network Configuration
Configuration Restrictions
Azure SQL Managed Instance Restrictions
Start and Stop Services
Instant File Initialization
Locked Pages
FILESTREAM and Availability Groups
Server Collation
Startup Parameters
ERRORLOG Configuration
Error Reporting and Customer Feedback
ALTER SERVER CONFIGURATION
“Mixed Mode” Security
Logon Auditing
Server Proxy Account
Database Restrictions
Azure SQL Database Restrictions
Azure SQL Space Management
Azure SQL Managed Instance Space Management
Azure SQL Database Space Management
Loading Data
Keep These in Mind
bcp
BULK INSERT and OPENROWSET
SQL Server Integration Services (SSIS)
Azure SSIS
BACPAC
Database Copy
RESTORE to Managed Instance
Spark Connector
Azure Data Factory (ADF)
SQL Data Sync
Replication Subscriber
Updating Azure SQL
Maintenance of Azure SQL
New Features and Capabilities in Azure SQL
Summary
Chapter 6: Securing Azure SQL
Security Capabilities and Tasks
Security Capabilities
Active Directory Authentication
Azure RBAC and Locks
Auditing
Data Encryption
Dynamic Data Masking
Advanced Data Security
Security Tasks
Set Up and Configure Network Security
Set Up and Configure Authentication and Authorization
Set Up and Configure Data Protection
Monitor Security
Go Bigger with Advanced Data Security
Network Security
Azure SQL Managed Instance Network Security
Azure SQL Database Network Security
Using the Public Endpoint
Using Private Link
Authentication and Access
Azure Role-Based Access Control (RBAC)
Authentication for Azure SQL Managed Instance
Azure Active Directory Authentication
Authentication for Azure SQL Database
Using Contained Users
Azure Active Directory Authentication
Set Up and Configure Access
Protecting Your Data
Encrypting Connections
Transparent Data Encryption (TDE)
Bring Your Own Key (BYOK)
Always Encrypted
Dynamic Data Masking (DDM)
Monitoring Security
Monitoring the Azure Ecosystem
Auditing Azure SQL Managed Instance
Tracking Logins
SQL Server Audit
Auditing Azure SQL Database
Tracking Connections
SQL Database Auditing
Advanced Data Security
Data Classification
Vulnerability Assessment
Advanced Threat Protection (ATP)
Azure Security Center
Summary
Chapter 7: Monitoring and Tuning Performance for Azure SQL
Performance Capabilities
Max Capacities
Indexes
In-Memory OLTP
Partitions
SQL Server 2019 Enhancements
Intelligent Performance
Configuring and Maintaining for Performance
Tempdb
Database Configuration
Files and Filegroups
Max Degree of Parallelism
Resource Governor
Maintaining Indexes
Maintaining Statistics
Monitoring and Troubleshooting Performance
Monitoring Tools and Capabilities
Azure Monitor
Dynamic Management Views (DMV)
Extended Events (XEvent)
Lightweight Query Profiling
Query Plan Debugging
Query Store
Performance Visualization in Azure Portal
Dive into DMVs and Extended Events
DMVs Deep Dive
XEvent at Your Service
Performance Scenarios
Running vs. Waiting
Running
Azure SQL Specific Performance Scenarios
Log Governance
Worker Limits
Business Critical (BC) HADR Waits
Hyperscale Scenarios
Accelerating and Tuning Performance
Scaling CPU Capacity
I/O Performance
Increasing Memory or Workers
Improving Application Latency
Tune Like It Is SQL Server
Intelligent Performance
Intelligent Query Processing
Automatic Plan Correction
Automatic Tuning
Summary
Chapter 8: Availability for Azure SQL
HADR Capabilities
Automatic Backups and Point-In-Time restore
Built-In High Availability
Azure Redundancy
Geo-replication and Auto-failover Groups
Database Availability and Consistency
SQL Server Replication
Backup and Restore
Automatic Backups
Backup Retention
Backup Storage Consumption and Costs
Point-In-Time restore
Long-Term Retention Backups
Geo-restore of Databases
Restore Backups from Deleted Databases
Restore in Azure SQL Managed Instance
Built-In High Availability
General Purpose High Availability
Business Critical High Availability
Hyperscale High Availability
Go Further with Azure
Zone Redundancy
Geo-replication
Auto-failover Group
Azure SQL SLA
Database Availability and Consistency
Database Availability
Accelerated Database Recovery (ADR)
Database Consistency
Monitoring Availability
Instance, Server, and Database Availability
Backup and Restore History
Region, Data Center, and Service Availability
Replica Status
Failover Reasons
System Center Management Pack for Azure SQL
Summary
Chapter 9: Completing Your Knowledge of Azure SQL
Surface Area of Azure SQL
Linked Servers and Cross-Database Queries
External Tables
Database Mail
Service Broker
Full-Text Search
Machine Learning Services
What Is Missing?
Distributed Transactions (DTC)
xp_cmdshell
Job Management
SQL Server Agent
Elastic Jobs
Azure Automation
Supporting Azure SQL
Handling Errors
Stack Dumps
Troubleshooting Resources in the Azure Portal
UserVoice
Azure SQL Best Practices
Security Playbook
Best Practices for Performance
Azure Advisor
Stay in Touch with Our Team
Summary
Chapter 10: Go Big with the Cloud
Integration with Azure Services
Power Platform
Power BI
Power Apps
Power Automate
Azure Search
Stream Analytics
Azure Architectures and Solutions
Azure Synapse
Azure Arc
Summary
Index