Azure SQL Revealed : The Next-Generation Cloud Database with AI and Microsoft Fabric

Access detailed content and examples on Azure SQL, a set of cloud services that allows for SQL Server to be deployed in the cloud. This book teaches the fundamentals of deployment, configuration, security, performance, and availability of Azure SQL from the perspective of these same tasks and capabilities in SQL Server. This distinct approach makes this book an ideal learning platform for readers familiar with SQL Server on-premises who want to migrate their skills toward providing cloud solutions to an enterprise market that is increasingly cloud-focused. If you know SQL Server, you will love this book. You will be able to take your existing knowledge of SQL Server and translate that knowledge into the world of cloud services from the Microsoft Azure platform, and in particular into Azure SQL. This book provides information never seen before about the history and architecture of Azure SQL. Author Bob Ward is a leading expert with access to and support from the Microsoft engineering team that built Azure SQL and related database cloud services. He presents powerful, behind-the-scenes insights into the workings of one of the most popular database cloud services in the industry. This book also brings you the latest innovations for Azure SQL including Azure Arc, Hyperscale, generative AI applications, Microsoft Copilots, and integration with the Microsoft Fabric. Table of Contents About the Author About the Technical Reviewer Acknowledgments Foreword 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 Summary Chapter 2: What Is Azure SQL? The Azure Ecosystem Azure Accounts, Tenants, and Subscriptions The Azure Portal The Azure Marketplace Azure API and CLI Azure Resource Manager (ARM) Azure Monitor Azure Regions and Data Centers Azure Trust, Privacy, and Compliance Azure Service-Level Agreement (SLA) What Is Azure SQL? IaaS vs. PaaS The Azure SQL Lineup SQL Virtual Machines Managed Instances Databases 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 Elastic Pool or Single Database DTU vs. vCore General Purpose Service Tier Serverless Business Critical Hyperscale Elastic Pool Databases Azure Database Fleet Manager 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) Visual Studio Tools Beyond RDBMS 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 Security Type Image VM Architecture Spot Instance Virtual Machine Sizes Accounts, Port, and OS Licensing Making Configuration Choices As Part of Deploy OS Disks Networking Management Monitoring Advanced SQL Server Settings Tags Deploy! Navigating an Azure Virtual Machine 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 with ARM Templates, Bison, and Terraform Reserved Instances, Dedicated Hosts, and Capacity Reservations Migration to Azure Azure Arc Migration Assessment Restoring a Database Azure Migrate and Azure Database Migration Service Using Azure Migrate Server Migration Deploying SQL Server on Linux with Azure Virtual Machine Deploying SQL Server Containers SQL Server IaaS Agent Extension Settings Configure Storage Updates Backups High Availability SQL Best Practices Assessment Security Security Configure Microsoft Defender for Cloud Configuring an Azure Virtual Machine Stopping vs. Deallocating Resizing Security (RBAC) Other Config Options Maximizing Storage Performance Best Practices Premium SSD v2 Learn More Performance Monitoring Azure Metrics Insights Networking HADR Azure Storage Backups Go Further with Azure Availability Always On Failover Cluster Instance Always On Availability Groups SQL Server and Linux High Availability Microsoft Innovations and Azure Boost Summary Chapter 4: Deploying Azure SQL Pre-deployment Planning New Deployment or Migration Making Deployment Choices Deploying for Free The Azure SQL Managed Instance Free Trial The Azure SQL Database Free Offer Deployment Methods Deployment Option Region Purchasing Model Service Tier (SLO) Hardware vCore and Storage Sizes Price Consider Resource Limits Deploying Azure SQL Managed Instance Walking Through a Deployment Basics Networking Virtual Network Connection Type Public Endpoint Security Additional Settings Tags Deploy! Deploying with a CLI Implementation Details Connecting and Verifying Deployment Connect to a Managed Instance bwsqlminextgen.b243ea7f888c.database.windows.net Verify the Deployment Examining the ERRORLOG Verification Queries Migrating to Azure SQL Managed Instance Deploying an Azure SQL Database Deployment and Options Basics Service Tier Compute Tier Hardware Configuration vCores HA Replicas and Zone Redundancy Other Choices Networking Security Additional Settings Tags Deploy It! Deploying Serverless Deploying an Elastic Pool Deploying with a CLI Implementation Details Dedicated Rings and Instances The Logical Server Storage, Compute, and Gateways Serverless Hyperscale Resource Governance SQL Server Resource Governor Engine enhancements Windows Job Objects File Source Resource Manager (FSRM) 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 Compute and Storage Networking Configuration Maintenance Start and Stop Configuring Databases Configuring Azure SQL Database Creating New Databases Altering Databases Network Configuration Configuring Serverless Configuring Elastic Pools 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) Updating Azure SQL Maintenance of Azure SQL Update Policy for Azure SQL Managed Instance New Features and Capabilities in Azure SQL Summary Chapter 6: Securing Azure SQL Network Security Azure SQL Managed Instance Network Security Public Endpoint VNet-Local Endpoint Private Endpoint Other Considerations Azure SQL Database Network Security Using the Public Endpoint Using Private Link Authentication and Access Azure Role-Based Access Control (RBAC) SQL DB Contributor SQL Server Contributor SQL Security Manager SQL Managed Instance Contributor Authentication for Azure SQL Managed Instance Microsoft Entra Authentication Windows Authentication Authentication for Azure SQL Database Using Contained Users Microsoft Entra Authentication Managed Identities Set Up and Configure Access Microsoft Purview Data Protection Encrypting Connections Transparent Data Encryption (TDE) Bring Your Own Key (BYOK) Always Encrypted Dynamic Data Masking (DDM) SQL Ledger Security Management Monitoring Activities Outside of SQL Auditing Azure SQL Managed Instance Tracking Logins SQL Server Audit Auditing Azure SQL Database Tracking Connections SQL Database Auditing Microsoft Defender for SQL Vulnerability Assessment SQL Threat Protection Data Classification Summary Chapter 7: Monitoring and Tuning Performance for Azure SQL Performance Capabilities Max Capacities Indexes In-Memory OLTP Partitions SQL Server 2022 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 (DMVs) Extended Events (XEvent) Lightweight Query Profiling Query Plan Debugging Query Store Performance Visualization in Azure Portal Database Watcher Microsoft Copilot Skills in Azure SQL Database Dive into DMVs and Extended Events DMVs Deep Dive Azure SQL Managed Instance Azure SQL Database DMVs You Will Need DMVs for Deep Troubleshooting XEvent at Your Service Extended Events for Azure SQL Managed Instance Extended Events for Azure SQL Database Performance Scenarios Running vs. Waiting Azure Portal/PowerShell/Alerts sys.dm_db_resource_stats sys.server_resource_stats Using Copilot to Get Started Running Query Store sys.dm_exec_requests sys.dm_exec_query_stats sys.dm_exec_procedure_stats Microsoft Copilot Skills in Azure SQL Database Database Watcher Waiting sys.dm_os_wait_stats sys.dm_exec_requests sys.dm_os_waiting_tasks Query Store Microsoft Copilot Skills in Azure SQL Database and Database Watcher Performance Example Azure SQL–Specific Performance Scenarios Log Governance Worker Limits Business Critical (BC) HADR Waits Hyperscale Scenarios Accelerating and Tuning Performance Scaling CPU Capacity Tuning 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 Availability Azure Redundancy for High Availability Geo-replication and Failover Groups Database Availability and Consistency Disaster Recovery with Managed Instance Link Free Passive DR Replicas SQL Server Replication Backup and Restore Automatic Backups Locally Redundant Storage (LRS) Zone-Redundant Storage (ZRS) Geo-redundant Storage (GRS) Geo-Zone-Redundant Storage (GZRS) Backup Retention Automated Backups Backup Storage Consumption and Costs Point-in-Time Restore (PITR) Geo-restore of Databases Restore Backups from Deleted Databases Restore in Azure SQL Managed Instance Built-In Availability General Purpose Availability Business Critical Availability Hyperscale Availability Extend HADR with Azure Zone Redundancy Geo-replication Failover Group Disaster Recovery with Managed Instance Link Offline Disaster Recovery Online Disaster Recovery 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 Availability Metrics Replica Status Failover Reasons Summary Chapter 9: Extending 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 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 Keep Up to Date with New Features Stay in Touch with Our Team Summary Chapter 10: Beyond RDBMS “It’s in the Box?” JSON JSON_ARRAYAGG JSON_OBJECTAGG RegEx Spatial and Graph Columnstore and Memory Optimized Ledger Query Intelligence REST API Integration Data API Builder and GraphQL DevOps, GitHub, and Containers Database Projects GitHub Actions Containers SQL and AI GenAI and SQL Use AI Services Use T-SQL for Hybrid Search Use Frameworks What About Chat History? Where Do You Store Your Text Data? Resources Natural Language to SQL Query SQL and Microsoft Fabric Azure Arc Futures Summary Index