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

Table of Contents....5 About the Author....16 About the Technical Reviewer....17 Acknowledgments....18 Foreword....20 Introduction....23 Chapter 1: SQL Server Rises to the Clouds....26 CloudDB....27 The Red Dog....28 The Azure Services Platform....29 The Road to SQL Azure....33 SQL Data Services....33 SQL Azure Is Born....37 The SAWA Project....40 The Virtual Machine Initiative....40 Becoming Azure SQL Database....42 The Sterling (SAWAv2) Project....44 New Editions, the DTU, and Previews....49 Intelligent Performance and the MDCS....50 Advanced Data Security and the ILDC Team....51 A Pane for the Future Called Ibiza....52 A New Engineering Model for Azure....53 Bending Azure SQL Database....54 Lifting Customers to the Cloud....55 Project Socrates Goes Hyper....56 Azure SQL Today....58 Summary....59 Chapter 2: What Is Azure SQL?....60 The Azure Ecosystem....61 Azure Accounts, Tenants, and Subscriptions....61 The Azure Portal....63 The Azure Marketplace....66 Azure API and CLI....67 Azure Resource Manager (ARM)....68 Azure Monitor....71 Azure Regions and Data Centers....72 Azure Trust, Privacy, and Compliance....73 Azure Service-Level Agreement (SLA)....73 What Is Azure SQL?....74 IaaS vs. PaaS....74 The Azure SQL Lineup....74 SQL Virtual Machines....76 Managed Instances....76 Databases....76 Azure SQL Managed Instance....76 Managed Instance Capabilities....77 Managed Instance Options and Limits....79 General Purpose....80 Business Critical....81 Managed Instance Pools....81 Managed Instance vs. SQL Server on Azure Virtual Machine....82 Customers Using Managed Instance....83 Azure SQL Database....83 Azure SQL Database Capabilities....83 Azure SQL Database Options and Limits....85 Elastic Pool or Single Database....86 DTU vs. vCore....86 General Purpose Service Tier....87 Serverless....87 Business Critical....88 Hyperscale....88 Elastic Pool Databases....90 Azure Database Fleet Manager....91 Azure SQL Database vs. Azure SQL Managed Instance....92 Customers Using Azure SQL Database....93 Interfaces for Azure SQL....93 Azure Portal....93 az CLI....97 PowerShell....97 REST API....98 TDS and T-SQL....98 SQL CLI....99 SQL Server Management Studio (SSMS)....99 Visual Studio Tools....100 Beyond RDBMS....101 Summary....102 Chapter 3: SQL Server on Azure Virtual Machine....103 Deploying....103 Pricing....104 SQL Server Gallery Images....104 Resource Group, Region, and Availability....108 Resource Group....108 Virtual Machine Name....108 Region....108 Availability Options....108 Security Type....109 Image....109 VM Architecture....109 Spot Instance....110 Virtual Machine Sizes....111 Accounts, Port, and OS Licensing....114 Making Configuration Choices As Part of Deploy....115 OS Disks....116 Networking....117 Management....118 Monitoring....120 Advanced....122 SQL Server Settings....124 Tags....132 Deploy!....132 Navigating an Azure Virtual Machine in the Portal....135 Connecting to Your VM....137 Exploring the SQL Server Installation....139 What Is Installed....139 What Is Configured....140 Deploy on Your Own....141 Using a CLI with ARM Templates, Bison, and Terraform....142 Reserved Instances, Dedicated Hosts, and Capacity Reservations....143 Migration to Azure....144 Azure Arc Migration Assessment....144 Restoring a Database....144 Azure Migrate and Azure Database Migration Service....145 Using Azure Migrate Server Migration....146 Deploying SQL Server on Linux with Azure Virtual Machine....146 Deploying SQL Server Containers....147 SQL Server IaaS Agent Extension....147 Settings....149 Configure....149 Storage....149 Updates....150 Backups....151 High Availability....151 SQL Best Practices Assessment....151 Security....151 Security Configure....151 Microsoft Defender for Cloud....152 Configuring an Azure Virtual Machine....153 Stopping vs. Deallocating....153 Resizing....153 Security (RBAC)....154 Other Config Options....154 Maximizing Storage Performance....155 Best Practices....155 Premium SSD v2....156 Learn More....158 Performance Monitoring....158 Azure Metrics....159 Insights....160 Networking....161 HADR....161 Azure Storage....162 Backups....162 Go Further with Azure Availability....163 Always On Failover Cluster Instance....164 Always On Availability Groups....165 SQL Server and Linux High Availability....167 Microsoft Innovations and Azure Boost....167 Summary....168 Chapter 4: Deploying Azure SQL....169 Pre-deployment Planning....170 New Deployment or Migration....170 Making Deployment Choices....171 Deploying for Free....172 The Azure SQL Managed Instance Free Trial....172 The Azure SQL Database Free Offer....172 Deployment Methods....173 Deployment Option....175 Region....175 Purchasing Model....176 Service Tier (SLO)....176 Hardware....178 vCore and Storage Sizes....179 Price....179 Consider Resource Limits....180 Deploying Azure SQL Managed Instance....181 Walking Through a Deployment....181 Basics....182 Networking....187 Virtual Network....189 Connection Type....189 Public Endpoint....189 Security....190 Additional Settings....192 Tags....194 Deploy!....195 Deploying with a CLI....195 Implementation Details....196 Connecting and Verifying Deployment....198 Connect to a Managed Instance....199 bwsqlminextgen.b243ea7f888c.database.windows.net....200 Verify the Deployment....201 Examining the ERRORLOG....201 Verification Queries....202 Migrating to Azure SQL Managed Instance....205 Deploying an Azure SQL Database....206 Deployment and Options....207 Basics....207 Service Tier....213 Compute Tier....213 Hardware Configuration....213 vCores....213 HA Replicas and Zone Redundancy....213 Other Choices....213 Networking....214 Security....216 Additional Settings....218 Tags....220 Deploy It!....220 Deploying Serverless....221 Deploying an Elastic Pool....224 Deploying with a CLI....226 Implementation Details....227 Dedicated Rings and Instances....228 The Logical Server....228 Storage, Compute, and Gateways....229 Serverless....229 Hyperscale....230 Resource Governance....232 SQL Server Resource Governor....232 Engine enhancements....232 Windows Job Objects....233 File Source Resource Manager (FSRM)....233 Connecting and Verifying Deployment....233 Connecting to Azure SQL Database....233 Verifying Deployment....238 Migrating to Azure SQL Database....240 Summary....241 Chapter 5: Configuring Azure SQL....243 Configuring Azure SQL Managed Instance....244 sp_configure....244 Trace Flags....244 Tempdb....245 Master and Model....246 Configuring Edition....246 Compute and Storage....246 Networking Configuration....247 Maintenance....247 Start and Stop....248 Configuring Databases....249 Configuring Azure SQL Database....251 Creating New Databases....251 Altering Databases....253 Network Configuration....254 Configuring Serverless....255 Configuring Elastic Pools....257 Configuration Restrictions....257 Azure SQL Managed Instance Restrictions....258 Start and Stop Services....258 Instant File Initialization....258 Locked Pages....258 FILESTREAM and Availability Groups....259 Server Collation....259 Startup Parameters....259 ERRORLOG Configuration....259 Error Reporting and Customer Feedback....260 ALTER SERVER CONFIGURATION....260 “Mixed Mode” Security....260 Logon Auditing....261 Server Proxy Account....261 Database Restrictions....261 Azure SQL Database Restrictions....261 Azure SQL Space Management....262 Azure SQL Managed Instance Space Management....262 Azure SQL Database Space Management....263 Loading Data....264 Keep These in Mind....264 bcp....265 BULK INSERT and OPENROWSET....266 SQL Server Integration Services (SSIS)....267 Azure SSIS....268 BACPAC....269 Database Copy....270 RESTORE to Managed Instance....271 Spark Connector....272 Azure Data Factory (ADF)....272 Updating Azure SQL....272 Maintenance of Azure SQL....273 Update Policy for Azure SQL Managed Instance....274 New Features and Capabilities in Azure SQL....275 Summary....275 Chapter 6: Securing Azure SQL....277 Network Security....279 Azure SQL Managed Instance Network Security....279 Public Endpoint....280 VNet-Local Endpoint....280 Private Endpoint....281 Other Considerations....281 Azure SQL Database Network Security....282 Using the Public Endpoint....282 Using Private Link....285 Authentication and Access....289 Azure Role-Based Access Control (RBAC)....289 SQL DB Contributor....290 SQL Server Contributor....290 SQL Security Manager....290 SQL Managed Instance Contributor....290 Authentication for Azure SQL Managed Instance....290 Microsoft Entra Authentication....291 Windows Authentication....291 Authentication for Azure SQL Database....292 Using Contained Users....293 Microsoft Entra Authentication....293 Managed Identities....295 Set Up and Configure Access....301 Microsoft Purview....301 Data Protection....302 Encrypting Connections....302 Transparent Data Encryption (TDE)....303 Bring Your Own Key (BYOK)....304 Always Encrypted....306 Dynamic Data Masking (DDM)....307 SQL Ledger....308 Security Management....310 Monitoring Activities Outside of SQL....310 Auditing Azure SQL Managed Instance....311 Tracking Logins....312 SQL Server Audit....312 Auditing Azure SQL Database....313 Tracking Connections....313 SQL Database Auditing....314 Microsoft Defender for SQL....318 Vulnerability Assessment....322 SQL Threat Protection....324 Data Classification....326 Summary....328 Chapter 7: Monitoring and Tuning Performance for Azure SQL....329 Performance Capabilities....331 Max Capacities....331 Indexes....333 In-Memory OLTP....333 Partitions....334 SQL Server 2022 Enhancements....335 Intelligent Performance....336 Configuring and Maintaining for Performance....336 Tempdb....336 Database Configuration....337 Files and Filegroups....337 Max Degree of Parallelism....338 Resource Governor....339 Maintaining Indexes....339 Maintaining Statistics....340 Monitoring and Troubleshooting Performance....340 Monitoring Tools and Capabilities....340 Azure Monitor....341 Dynamic Management Views (DMVs)....341 Extended Events (XEvent)....341 Lightweight Query Profiling....342 Query Plan Debugging....342 Query Store....342 Performance Visualization in Azure Portal....343 Database Watcher....343 Microsoft Copilot Skills in Azure SQL Database....344 Dive into DMVs and Extended Events....349 DMVs Deep Dive....349 Azure SQL Managed Instance....350 Azure SQL Database....350 DMVs You Will Need....350 DMVs for Deep Troubleshooting....351 XEvent at Your Service....352 Extended Events for Azure SQL Managed Instance....352 Extended Events for Azure SQL Database....352 Performance Scenarios....354 Running vs. Waiting....355 Azure Portal/PowerShell/Alerts....355 sys.dm_db_resource_stats....355 sys.server_resource_stats....356 Using Copilot to Get Started....356 Running....358 Query Store....358 sys.dm_exec_requests....358 sys.dm_exec_query_stats....358 sys.dm_exec_procedure_stats....359 Microsoft Copilot Skills in Azure SQL Database....359 Database Watcher....362 Waiting....363 sys.dm_os_wait_stats....364 sys.dm_exec_requests....365 sys.dm_os_waiting_tasks....365 Query Store....365 Microsoft Copilot Skills in Azure SQL Database and Database Watcher....365 Performance Example....368 Azure SQL–Specific Performance Scenarios....380 Log Governance....380 Worker Limits....380 Business Critical (BC) HADR Waits....381 Hyperscale Scenarios....381 Accelerating and Tuning Performance....382 Scaling CPU Capacity....382 Tuning I/O Performance....388 Increasing Memory or Workers....388 Improving Application Latency....389 Tune like It Is SQL Server....390 Intelligent Performance....390 Intelligent Query Processing....390 Automatic Plan Correction....393 Automatic Tuning....394 Summary....401 Chapter 8: Availability for Azure SQL....403 HADR Capabilities....405 Automatic Backups and Point-in-Time Restore....405 Built-In Availability....405 Azure Redundancy for High Availability....406 Geo-replication and Failover Groups....406 Database Availability and Consistency....406 Disaster Recovery with Managed Instance Link....407 Free Passive DR Replicas....407 SQL Server Replication....407 Backup and Restore....408 Automatic Backups....408 Locally Redundant Storage (LRS)....409 Zone-Redundant Storage (ZRS)....409 Geo-redundant Storage (GRS)....409 Geo-Zone-Redundant Storage (GZRS)....409 Backup Retention....410 Automated Backups....412 Backup Storage Consumption and Costs....412 Point-in-Time Restore (PITR)....414 Geo-restore of Databases....419 Restore Backups from Deleted Databases....419 Restore in Azure SQL Managed Instance....420 Built-In Availability....421 General Purpose Availability....422 Business Critical Availability....423 Hyperscale Availability....427 Extend HADR with Azure....430 Zone Redundancy....431 Geo-replication....435 Failover Group....440 Disaster Recovery with Managed Instance Link....446 Offline Disaster Recovery....447 Online Disaster Recovery....448 Azure SQL SLA....448 Database Availability and Consistency....449 Database Availability....450 Accelerated Database Recovery (ADR)....450 Database Consistency....450 Monitoring Availability....451 Instance, Server, and Database Availability....452 Backup and Restore History....453 Region, Data Center, and Service Availability....456 Availability Metrics....458 Replica Status....461 Failover Reasons....463 Summary....464 Chapter 9: Extending Your Knowledge of Azure SQL....466 Surface Area of Azure SQL....467 Linked Servers and Cross-Database Queries....467 External Tables....468 Database Mail....471 Service Broker....472 Full-Text Search....472 Machine Learning Services....473 Distributed Transactions (DTC)....474 xp_cmdshell....474 Job Management....475 SQL Server Agent....475 Elastic Jobs....477 Azure Automation....479 Supporting Azure SQL....479 Handling Errors....479 Stack Dumps....481 Troubleshooting Resources in the Azure Portal....482 UserVoice....490 Azure SQL Best Practices....490 Security Playbook....490 Best Practices for Performance....491 Azure Advisor....491 Keep Up to Date with New Features....493 Stay in Touch with Our Team....493 Summary....495 Chapter 10: Beyond RDBMS....496 “It’s in the Box?”....497 JSON....498 JSON_ARRAYAGG....500 JSON_OBJECTAGG....501 RegEx....501 Spatial and Graph....503 Columnstore and Memory Optimized....505 Ledger....505 Query Intelligence....506 REST API Integration....506 Data API Builder and GraphQL....509 DevOps, GitHub, and Containers....510 Database Projects....511 GitHub Actions....511 Containers....512 SQL and AI....513 GenAI and SQL....515 Use AI Services....517 Use T-SQL for Hybrid Search....519 Use Frameworks....522 What About Chat History?....523 Where Do You Store Your Text Data?....523 Resources....524 Natural Language to SQL Query....524 SQL and Microsoft Fabric....529 Azure Arc....533 Futures....534 Summary....535