Azure SQL Hyperscale Revealed: High-performance Scalable Solutions for Critical Data Workloads

Table of Contents....4 About the Authors....11 About the Technical Reviewers....12 Introduction....13 Chapter 1: The Journey to Hyperscale Architecture in Azure SQL....15 SQL on Azure....17 The Basics of Azure SQL....17 Azure SQL Platform as a Service....19 Deployment Models....21 Purchasing Models and Service Tiers....22 Availability Models and Redundancy....25 Standard Availability Model: Locally Redundant Availability....27 General Purpose Service Tier: Zone-Redundant Availability....28 Premium and Business Critical Service Tier: Locally Redundant Availability....30 Premium and Business Critical Service Tier: Zone-Redundant Availability....31 Protecting Against Regional Outages Using Failover Groups with Geo-redundant Availability....33 The Hyperscale Service Tier....34 Hyperscale Architecture Overview....36 Deploying Your First Hyperscale Database....37 Cleaning Up....46 Summary....47 Chapter 2: Azure SQL Hyperscale Architecture Concepts and Foundations....49 Hyperscale Azure SQL Scalability and Durability....50 Foundations of Azure SQL Hyperscale....52 The Buffer Pool Extension....53 The Resilient Buffer Pool Extension....53 Investigating the Size of RBPEX....53 Example 1: RBPEX on a Two-vCore Hyperscale Database....54 Example 2: RBPEX on a Four-vCore Hyperscale Database....56 Row Versioning–Based Isolation Level....57 Accelerated Database Recovery....60 Multitier Architecture Concepts in Hyperscale....61 Compute Nodes....62 Primary Compute Node....64 Secondary Compute Node....64 High-Availability Replicas....65 Named Replicas....66 Geo Replicas....69 Log Service....70 How the Log Service Works in Hyperscale....71 Log Service Rate Throttling....73 Page Servers....73 Azure Standard Storage....80 How Do the Tiers Work Together?....82 Summary....86 Chapter 3: Planning an Azure SQL DB Hyperscale Environment....87 Considerations When Planning for Hyperscale....87 The Azure SQL Database Logical Server....88 Considerations for Reliability....91 High-Availability Replicas....92 Named Replicas....96 Geo Replica....99 Backup....100 Considerations for Network Connectivity....104 Public Endpoint....105 Public Endpoint Access Rules....107 Public Endpoint Protection....109 Private Endpoints....110 Outbound Networking....113 Connection Policy....113 Connection Encryption....116 Understanding Network Connectivity Requirements....117 Common Considerations for Network Connectivity....118 Considerations for Security....118 Authentication....119 Microsoft Defender for SQL....122 Logical Server Identity....124 Transparent Data Encryption....126 Ledger....127 Considerations for Operational Excellence....129 Diagnostic Settings....130 Azure SQL Auditing....131 Summary....134 Chapter 4: Deploying a Highly Available Hyperscale Database into a Virtual Network....135 An Example Hyperscale Production Environment....136 The Starting Environment....139 The Starting Environment Deployment Script....141 Deploying the Starting Environment Using the Azure Cloud Shell....143 Creating a SQL Administrators Group....146 Deploying a Highly Available Hyperscale Database into a Virtual Network....149 Basic Configuration of the Database....151 Configuring Network Connectivity....156 The Final Configuration Tasks and Deployment....160 Deleting the Example Environment....165 Summary....167 Chapter 5: Administering a Hyperscale Database in a Virtual Network in the Azure Portal....169 Administering a Hyperscale Database in a Virtual Network....170 Deploying a Management VM and Azure Bastion....173 Using the Management VM with an Azure Bastion....177 Summary....180 Chapter 6: Configuring Transparent Data Encryption to Bring Your Own Key....181 Enabling Customer-Managed Key Transparent Data Encryption....182 Creating a User-Assigned Managed Identity....184 Granting the Key Vault Crypto Officer Role to a User....185 Generating a Key in the Azure Key Vault....188 Granting Access to the Key by the User-Assigned Managed Identity....190 Assigning the User-Assigned Managed Identity to the Logical Server....192 Enabling Customer-Managed TDE....194 Summary....195 Chapter 7: Enabling Geo-replication for Disaster Recovery....197 Deploying a Hyperscale Geo Replica....198 Creating a Logical Server in the Failover Region....199 Connecting a Logical Server to a Virtual Network with Private Link....201 Enabling the Customer-Managed Key TDE....206 Enabling Geo-replication of a Hyperscale Database....208 Summary....210 Chapter 8: Configuring Security Features and Enabling Diagnostic and Audit Logs....212 Enabling Microsoft Defender for SQL....213 Storing Diagnostic and Audit Logs....215 Sending Audit Logs to a Log Analytics Workspace....216 Sending Database Diagnostic Logs to Log Analytics....218 Summary....220 Chapter 9: Deploying Azure SQL DB Hyperscale Using PowerShell....221 Introduction to Infrastructure as Code....222 Imperative vs. Declarative Infrastructure as Code....223 Deploying Hyperscale Using Azure PowerShell....224 The Azure PowerShell Modules....224 Deploying the Starting Environment....226 The Complete Deployment PowerShell Script....226 Azure PowerShell Commands in Detail....228 Creating Helper Variables....228 Create the User-Assigned Managed Identity for the Hyperscale Database....230 Prepare the TDE Protector Key in the Key Vault....231 Create the Logical Server in the Primary Region....234 Connect the Primary Logical Server to the Virtual Network....236 Create the Hyperscale Database in the Primary Region....239 Configure Diagnostic and Audit Logs to Be Sent to a Log Analytics Workspace....241 Create the Logical Server in the Failover Region and Connect It to the VNet....243 Create the Replica Hyperscale Database in the Failover Region....244 Configure Diagnostic and Audit Logs....246 Remove the Key Vault Crypto Officer Role from the Key Vault....246 Summary....247 Chapter 10: Deploying Azure SQL DB Hyperscale Using Bash and Azure CLI....248 Deploying Hyperscale Using the Azure CLI....248 The Azure CLI....249 Supported Azure CLI Environments....249 Installing the Azure CLI....249 Deploying the Starting Environment....250 The Complete Deployment Bash Script....252 Azure CLI Commands in Detail....254 Creating Helper Variables....254 Create the User-Assigned Managed Identity for the Hyperscale Database....255 Prepare the TDE Protector Key in the Key Vault....256 Create the Logical Server in the Primary Region....259 Connect the Primary Logical Server to the Virtual Network....260 Create the Hyperscale Database in the Primary Region....262 Configure Diagnostic and Audit Logs to Be Sent to the Log Analytics Workspace....264 Create the Logical Server in the Failover Region and Connect It to the Virtual Network....267 Create the Replica Hyperscale Database in the Failover Region....268 Configure Diagnostic and Audit Logs....270 Remove the Key Vault Crypto Officer Role from the Key Vault....271 Summary....271 Chapter 11: Deploying Azure SQL DB Hyperscale Using Azure Bicep....273 About Azure Bicep....274 Deploying Using Azure Bicep....274 A Complete Azure Bicep Deployment....274 Deploying Using PowerShell....275 Deploying Using Bash....276 Hyperscale Resources in Azure Bicep....277 Creating the User-Assigned Managed Identity....278 Configuring the Key Vault for Customer-Managed Encryption....278 Creating the Azure SQL Logical Server....280 Connecting the Logical Server to the Virtual Network....281 Configuring Audit Logs to Be Sent to Log Analytics....283 Creating the Hyperscale Database....285 Configuring Diagnostic Logs to Be Sent to Log Analytics....286 Creating the Failover Resources and Replica Hyperscale Database....287 Putting It All Together....289 Summary....290 Chapter 12: Testing Hyperscale Database Performance Against Other Azure SQL Deployment Options....291 HammerDB....292 HammerDB TPROC-C Workload....293 HammerDB Step-by-Step....293 Schema Build....294 Driver Script Options....299 Virtual User Options....303 Autopilot Options....305 Run the Virtual Users and Execute the TPROC-C Workload....306 Schema Build Performance Metrics....307 Azure SQL Database General Purpose Service Tier (GP_Gen5_8)....308 Azure SQL Database Business Critical General Service Tier (BC_Gen5_8)....311 Azure SQL Database Hyperscale Service Tier (HS_Gen5_8)....314 Summary of Schema Build Performance Results....317 TPROC-C Workload Metrics....318 Azure SQL Database General Purpose Service Tier (GP_Gen5_8)....321 Azure SQL Database Business Critical Service Tier (BC_Gen5_8)....324 Azure SQL Database Hyperscale Service Tier (HS_Gen5_8)....327 Summary of TPROC-C Workload Performance Results....341 Summary....342 Chapter 13: Monitoring and Scaling....344 Monitoring Platform Metrics....345 Viewing Metrics with the Metrics Explorer....345 Streaming Metrics to a Log Analytics Workspace....347 Alerting on Platform Metrics....348 Monitoring and Tuning Database Performance....354 Monitoring Query Performance....354 Performance Recommendations....355 Automatically Tuning Database Performance....355 Gathering Insights from the Database....356 SQL Analytics....357 Scaling a Hyperscale Database....360 Manually Scaling Up a Hyperscale Database....361 Manually Scaling Out a Hyperscale Database....362 Autoscaling a Hyperscale Database....363 Summary....363 Chapter 14: Backup, Restore, and Disaster Recovery....364 Hyperscale Database Backups....364 Backup Retention Policy....365 Backup Storage Redundancy....368 Monitoring Backup Storage Consumption with Azure Monitor Metrics....370 Hyperscale Database Restores....371 Example 1: Restore to Same Region, LRS to LRS....372 Example 2: Restore to Same Region, LRS to GRS....374 Example 3: Restore to the Same Region, GRS to GRS....375 Example 4: Geo Restore to Different Region, GRS to LRS....375 Example 5: Geo-restore to Different Region, GRS to GRS....376 Comparing Restore Performance of Hyperscale to Traditional Azure SQL Databases....378 Disaster Recovery....380 Summary....382 Chapter 15: Security and Updating....384 Azure SQL Database Security Overview....384 Network Security....387 Public Network Access....387 Firewall and Virtual Network Rules....388 Connection Policy....389 Access Management....390 Logical Server Authentication....391 Role-Based Access Control....392 Permissions on the Database Engine....393 Row-Level Security....393 Auditing and Azure Threat Detection....393 Azure SQL Database Auditing....393 Microsoft Defender for Cloud....394 Information Protection....395 TLS Encryption in Transit....395 Encryption at Rest with Transparent Data Encryption....396 Always Encrypted: Encryption in Use....397 Data Masking....397 Updating and Maintenance Events....399 Summary....401 Chapter 16: Managing Costs....402 Azure Hybrid Benefit....402 Reserving Capacity....404 Purchasing Reserved Capacity....404 Estimating Reserved Capacity Benefits....405 Utilizing Reservations....405 Scaling In and Scaling Down Replicas....406 Summary....408 Chapter 17: Determining Whether Hyperscale Is Appropriate....409 Key Considerations for Hyperscale....409 Scalability....410 Reliability....413 Business Continuity....413 Cost....416 Summary....418 Chapter 18: Migrating to Hyperscale....419 Common Migration Methods....419 In-Place Conversion of an Azure SQL Database to Hyperscale....421 Copying an Existing Azure SQL Database....423 Changing an Existing Azure SQL Database to Hyperscale....423 Migrating to Hyperscale with Data Migration Assistant....425 Migrating with DMS....426 Accelerating a DMS Migration....431 Migrating to Hyperscale with the Azure Database Migration Service....432 Database Migration Service Network Connectivity....433 Registering the DMS Resource Provider....434 Creating a DMS Resource....436 Performing a Migration with DMS....438 Migrating with ADS with the Azure SQL Migration Extension....443 Migrating to Hyperscale Using Import Database....444 Migrating to Hyperscale Using a Data Sync and Cutover....446 Summary....447 Chapter 19: Reverse Migrating Away from Hyperscale....448 Reverse Migration Methods....449 Reverse Migration Using the Azure Portal....449 Monitor Reverse Migration Using the Azure Portal....451 Reverse Migration Using Transact SQL....452 Monitor Reverse Migration Using T-SQL....453 Reverse Migration Using Azure CLI....453 Monitor Reverse Migration Using the Azure CLI....454 Common Pitfalls of the Reverse Migration Process....455 Migrating to an Unsupported Service Tier....455 Database Not Eligible for Reverse Migration....456 Summary....456 Chapter 20: Conclusion....458 Capture.PNG....1