دانلود کتاب Professional Azure SQL Managed Database Administration: Efficiently manage and modernize data in the cloud using Azure SQL, 3rd Edition

فهرست مطالب :

Cover
FM
Table of Contents
Preface
Acknowledgement
Chapter 1: Introduction to Azure SQL managed databases
Who manages what?
The Azure SQL Database architecture
The Client Layer
The Service Layer
The Platform Layer
The Infrastructure Layer
The 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
Introduction to Azure SQL Managed Instance
Connecting to Azure SQL Managed Instance
Virtual cluster connectivity architecture
Network requirements
Differences between SQL Database, SQL Managed Instance, and SQL Server
Backup and restore
Recovery model
SQL Server Agent
Change Data Capture
Auditing
Mirroring
Table partitioning
Replication
Multi-part names
SQL Server Browser
FileStream
Common Language Runtime (SQL CLR)
Resource Governor
Global temporary tables
Log shipping
SQL Trace and Profiler
Trace flags
System stored procedures
The USE statement
Exercise: Provisioning an Azure SQL Managed Instance using the Azure portal
Activity: Provisioning Azure SQL Server and SQL Database using PowerShell
Exercise: Provisioning an Azure SQL Managed Instance
Summary
Chapter 2: Service tiers
The DTU model
DTU service tiers
The vCore model
vCore service tiers
The General Purpose service tier
Azure Premium Storage characteristics
The Business Critical service tier
The Hyperscale service tier
vCore hardware generations
Determining an appropriate performance tier
DMA SKU recommendation
Azure SQL Database compute tiers
Scaling up the Azure SQL Database service tier
Changing a service tier
Exercise: Provisioning a Hyperscale SQL database using PowerShell
Choosing between vCore and DTU-based purchasing options
Licensing
Flexibility
Summary
Chapter 3: Migration
Migration methodology
Determining the migration benefits
Selecting a service model
Selecting a service tier
Selecting the primary region and disaster recovery region
Determining compatibility issues
Selecting a migration tool
Choosing between Azure SQL Database and SQL Managed Instance
Features
Migration
Time to develop and market
Tools for determining compatibility issues
Data Migration Assistant
SQL Server Data Tools (SSDT) for Visual Studio
SQL Server Management Studio (SSMS)
SQLPackage.exe
Azure Database Migration Services
Choosing a migration tool and performing migration
Activity: Migrating an on-premises SQL database to Azure SQL Database using DMA
Activity: Migrating an SQL Server database on an Azure virtual machine to an Azure SQL database using Azure DMS
Activity: Migrating an on-premises SQL Server database to Azure SQL Database using SSMS
Activity: Migrating an SQL Server database to an Azure SQL database using transactional replication
Activity: Migrating an on-premises SQL Server to Azure SQL Managed Instance using the native backup and restore method (offline approach)
Activity: Migrating an SQL Server on an Azure Virtual Machine to SQL Managed Instance using Azure DMS (online approach)
Summary
Chapter 4: Backups
Automatic backups
Backup storage
Backup retention period
Optimize backup storage costs for Azure SQL Database and Azure SQL Managed Instance
Choose the right backup storage type
Optimize the database backup retention period
Maximize your free backup storage space
Configure LTR backups
Use Azure Policy
Configure long-term backup retention for Azure SQL Database and Azure SQL Managed Instance
Long-term retention configuration on Azure SQL Managed Instance
Activity: Configure LTR Backups for Azure SQL Managed Instance using PowerShell
Manual Backups for Azure SQL Database
DACPAC and BACPAC
Backing up an Azure SQL Database Using SQL Server Management Studio (SSMS)
Manual versus Automated Backups
Activity: Perform Manual Backups Using PowerShell
Perform native COPY_ONLY backup on Azure SQL Managed Instance
Perform a manual COPY_ONLY backup using T-SQL commands
Summary
Chapter 5: Restoration
Restore types
Point-in-time restore
Performing a PITR on an Azure SQL Database using the Azure portal
Performing a PITR for an SQL Managed Instance using the Azure portal
Long-term database restore
Performing an LTDR on an Azure SQL Database using the Azure portal
Performing an LTDR for SQL Managed Instance using PowerShell
Restoring deleted databases
Restoring a deleted database on Azure SQL Database using the Azure portal
Restoring a deleted database on SQL Managed Instance using the Azure portal
Geo-restoring databases
Performing a geo-restore on an SQL Database using the Azure portal
Performing a geo-restore on an SQL Managed Instance using the Azure portal
Importing a database (Azure SQL Database only)
Activity: Performing a PITR for an Azure SQL Database with PowerShell
Activity: Performing a geo-restore of an Azure SQL Database with PowerShell
Activity: Performing Point-In-Time restore for SQL Managed Instance with PowerShell
Part 1: Restoring a database to a point in time using PowerShell on one managed instance
Part 2: Performing a cross-instance point-in-time restore from an existing database
Activity: Geo-restoring a database hosted on SQL Managed Instance using the Az PowerShell module
Activity: Restoring a deleted database on SQL Managed Instance using PowerShell
Summary
Chapter 6: Security
Network security
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
Service endpoints
Configuring service endpoints for SQL Database
Private endpoint
Authentication
SQL authentication
Azure AD authentication
Azure AD
Active Directory - Password
Using Active Directory - Password to authenticate to a SQL database
SQL Database authentication structure
SQL Database and SQL Managed Instance authentication considerations
Authorization
Server-level administrative roles
Non-administrative users
Creating contained database users for Azure AD authentication
Groups and roles
Row-level security
Dynamic data masking
Data Discovery & Classification
Exercise: Configuring Data Discovery & Classification for SQL Database
Auditing
Exercise: Configuring SQL Database auditing
Exercise: Configuring auditing for SQL Managed Instance
Activity: Audit COPY_ONLY backup events on SQL Managed Instance using audit logs
Steps to configure an audit for backup and restore events
Transparent Data Encryption
Azure Defender for SQL
Securing data traffic
Enforcing a minimal TLS version for SQL Database and SQL Managed Instance
Activity: Setting a minimum TLS version using the Azure portal and PowerShell for SQL Managed Instance
Using the Azure portal
Using PowerShell
Configuring and securing public endpoints in SQL Managed Instance
Securing SQL Managed Instance public endpoints
Locking traffic flow down using NSG or firewall rules
Activity: Implementing RLS
Activity: Implementing DDM
Activity: Implementing Azure Defender for SQL to detect SQL injection and brute-force attacks
Summary
Chapter 7: Scalability
Vertical scaling
Scale-up or scale-down service tiers
Vertical partitioning
Activity: Creating alerts
Horizontal scaling
Shard map manager
Sharding data models
Activity: Creating shards
Activity: Splitting data between shards
Activity: Using elastic database queries
Scaling a managed instance
Duration of scale-up/down operations
Activity: Scaling up SQL Managed Instance using the Azure portal
Activity: Scaling a managed instance using the Az.sql PowerShell module
Alternate ways of scaling SQL Managed Instance
Activity: Connecting to the SQL Managed Instance internal read replica using SSMS
Summary
Chapter 8: Elastic and instance pools
Introducing elastic database pools in SQL Database
When should you consider elastic database pools?
Sizing an elastic database pool
Creating an elastic database pool and adding toystore shards to the elastic database pool
Geo-replication considerations for elastic database pools
Auto-failover group considerations for elastic database pools
Activity: Exploring elastic database pools
Elastic database jobs
Elastic job agent
Job database
Target group
Jobs
Use cases
Exercise: Configuring an elastic database job using T-SQL
Introducing instance pools in SQL Managed Instance
Key differences between an instance pool and a single managed instance
Architecture differences between an instance pool and a single SQL managed instance
Resource limits
Public preview limitations
Performance and security considerations for instance pools
Deploying an instance pool using PowerShell commands
Activity: Deploying and managing a managed instance in an instance pool
Summary
Chapter 9: High availability and disaster recovery
High availability
The basic, standard, and general-purpose service tier locally redundant availability model
General-purpose service tier zone-redundant configuration
The premium/business-critical tier locally redundant availability model
The premium/business critical service tier zone-redundant configuration
Accelerated database recovery (ADR)
The standard database recovery process
The ADR process
Activity: Evaluating ADR
Disaster recovery
Active geo-replication
Auto-failover groups
Activity: Configuring active geo-replication and performing manual failover using the Azure portal
Activity: Configuring an Azure SQL Database auto-failover group using Azure portal
Activity: Configuring active geo-replication for Azure SQL Database using PowerShell
Activity: Configuring auto-failover groups for Azure SQL Database using PowerShell
Activity: Configuring an auto-failover group for SQL Managed Instance
Summary
Chapter 10: Monitoring and tuning
Monitoring an Azure SQL Database and SQL Managed Instance using the Azure portal
Monitoring database metrics
Alert rules, database size, and diagnostic settings
Diagnostic settings and logs
Intelligent Performance
Query Performance Insight
Analyzing diagnostic logs using Azure SQL Analytics
Creating a Log Analytics workspace
Creating an Azure SQL Analytics solution
Generating a workload and reviewing insights
Activity: Monitoring Azure SQL Database with Log Analytics and Power BI
Monitoring queries using the Query Performance Insight pane
Monitoring an Azure SQL Database and SQL Managed Instance using DMVs
Monitoring database metrics
Monitoring connections
Monitoring query performance
Monitoring blocking
Extended events
Examining queries using extended events
Tuning an Azure SQL database
Automatic tuning
In-memory technologies
In-memory OLTP
Memory-optimized tables
Natively compiled procedures
Columnstore indexes
Monitoring cost
Activity: Exploring the in-memory OLTP feature
Monitoring and tuning an Azure SQL Managed Instance
General Purpose instance I/O characteristics
Monitoring the first run with the default file configuration of the TPC-C database
Summary
Chapter 11: Database features
Azure SQL Data Sync
Activity: Configuring Data Sync between two Azure SQL databases using PowerShell
Online and resumable DDL operations
SQL Graph queries and improvements
Graph database integrity using edge constraints
Machine Learning Services
Differences between Machine Learning Services in SQL Server and Azure SQL Managed Instance
Activity: Run basic Python scripts
Activity: Using Machine Learning Services in Azure SQL Managed Instance to forecast monthly sales for the toystore database
Distributed transactions in Azure SQL Managed Instance
Server Trust Group
Activity: Creating a Server Trust Group using the Azure portal
Activity: Running distributed transactions using T-SQL
Summary
Chapter 12: App modernization
Migrating an SQL Server workload to SQL Managed Instance
Backup and restore
SQL installation and patches
Scaling
High availability and disaster recovery
Newly introduced features
Support for hosting SSRS catalog databases
Azure Machine Learning
Distributed transaction support
SQL Database serverless
Serverless use cases
Creating a serverless database
Auto-scaling in serverless
Cache Reclamation
Auto-pausing in serverless
Auto-resuming in serverless
SQL Database serverless billing
Demonstration of auto-scaling and compute billing in serverless
Serverless vs. provisioned compute
Scaling to the Hyperscale service tier
Considering moving to the Hyperscale service tier
Activity: Updating an existing SQL database to the Hyperscale service tier using the Azure portal
Activity: Updating an existing SQL database to the Hyperscale service tier using PowerShell commands
Read scale-out an SQL Hyperscale database
Summary
Index