SQL Server 2022 Revealed: A Hybrid Data Platform Powered by Security, Performance, and Availability

Table of Contents....5 About the Author....13 About the Technical Reviewer....14 Acknowledgments....15 Foreword....17 Introduction....19 Chapter 1: Project Dallas Becomes SQL Server 2022....22 Project Dallas....22 Becoming SQL Server 2022....24 Announcing SQL Server 2022....28 Private to Public Preview....32 The Path to General Availability....35 Introducing SQL Server 2022....35 Built on a Foundation....36 Wheel of Power....37 Cloud Connected....38 Built-In Query Intelligence....39 Industry-Proven Database Engine....39 Data Virtualization and Object Storage....40 Enhancing T-SQL for Developers....40 Getting Started with SQL Server 2022....41 How to Get SQL Server 2022....41 Installing SQL Server 2022....41 Learn All the Features and Editions....42 Learn About Pricing and Licensing....42 Get Training on SQL Server 2022....42 Go Deeper with Our Blog Series....42 Download Book Code and Samples....42 A Cloud-Connected, Intelligent, and Industry-Proven Data Platform....43 Chapter 2: Install and Upgrade....45 How to Install SQL Server 2022....45 Prerequisites....46 What Is Different for SQL Server 2022?....46 Azure Extension for SQL Server....47 Feature Differences....47 Removal of R, Python, and Java....49 Removal of Polybase Hadoop Connectivity with Java....50 Removal of Machine Learning Server....50 Removal of Distributed Replay....51 Memory Recommendations....51 Other Installation Methods....51 Setting Up the Azure Extension for SQL Server....52 What You Should Know First....53 Providing Values for the Feature Setup....56 Checking the Azure Extension for SQL Server....56 Login or Service Principal....56 Connect to Azure After Setup....60 Removing the Azure Extension for SQL Server....61 Deploying on Other Platforms....61 Side-by-Side and Multi-instance Installations....62 How to Upgrade to SQL Server 2022....62 The Importance of dbcompat....62 Configuration....63 Easy to Install and Upgrade....63 Chapter 3: Connect Your Database to the Cloud....64 The Hybrid SQL Server....64 What Is Hybrid?....65 SQL Server Hybrid Over the Years....65 The SQL Server 2022 Hybrid Lineup....66 Azure SQL Managed Instance....67 Azure Synapse Analytics....67 Azure Active Directory Authentication....67 Microsoft Purview....67 Microsoft Defender for SQL....67 Azure Arc Agents and Azure Extension for SQL Server....68 Managed Disaster Recovery with Azure SQL Managed Instance....68 Project Chimera and DAG....69 Distributed Availability Groups....69 Project Chimera....70 The Link Feature for Azure SQL Managed Instance....71 How It Works....71 Creating and Using the Link....71 Failing Over to Azure SQL Managed Instance....74 Using the Link Feature for Offline Disaster Recovery....74 Why Managed Disaster Recovery?....75 SQL Server and Database Version Compatibility....75 Prerequisites....76 Preparing the Environment....78 Creating the Link to Replicate the Database....78 See Changes Replicated....85 Failover to Managed Instance....87 Restoring a Database Back to SQL Server....89 Keep in Mind These Details....90 The Future for the Link Feature for Azure SQL Managed Instance....91 Azure Synapse Link for SQL Server....92 What Is Synapse Link for SQL Server?....93 How Does Synapse Link Work?....93 Try Out Synapse Link for SQL Server....96 Prerequisites....96 Set Up the Exercise....97 Synchronizing Data with Synapse Link....101 Near-Real-Time Analytics with Changes from SQL Server....124 More Details About Synapse Link....127 Configuration Choices....127 Transaction Consistency....130 Monitoring Synapse Link....131 Limits and Restrictions....131 Synapse Link Could Change Analytics for You....132 Azure Active Directory (AAD) Authentication....132 How Does AAD Authentication Work?....133 Setting Up and Using AAD Authentication....134 Prerequisites....134 Set Up AAD with SQL Server 2022....136 Using AAD with SQL Server....140 Microsoft Purview Policy Management....143 How Do Purview Access Policies Work?....144 Using Purview Access Policies....147 Prerequisites....147 Set Up Microsoft Purview Access Policies....148 Using Microsoft Purview Access Policies....152 Connecting SQL to the World....163 Chapter 4: Built-In Query Intelligence....164 Built-In Query Intelligence in SQL Server 2022....166 The New Query Store....168 On by Default....169 Query Store Hints....171 How Do You Use Query Store Hints?....172 When Should I Use a Query Store Hint?....172 How Is This Different Than Plan Guides?....173 Query Store Support for Secondary Replicas....173 How to Do You Configure It?....174 How Does It Work?....174 How Do You Use It?....177 Store for IQP....177 IQP Nextgen Defaults....178 Approximate Percentile....178 Optimized Plan Forcing....179 The Background....180 How Does It Work?....180 See It in Action....181 Considerations for Using Optimized Plan Forcing....187 IQP Nextgen with dbcompat 140....188 Memory Grant Percentiles....191 Memory Grant Feedback Persistence....191 Prerequisites....191 Follow These Steps for the Exercise....192 The Intelligent Query Processor....202 Chapter 5: Built-In Query Intelligence Gets Even Better....204 Parameter-Sensitive Plan (PSP) Optimization....204 What Is a Parameter-Sensitive Plan?....205 Learn PSP Through a Scenario....206 How It All Started....207 How Does PSP Optimization Work?....208 Lets See PSP Optimization in Action....211 Prerequisites....211 Follow These Steps for the Exercise....211 What Other Details About PSP Optimization Should I Know?....227 Check the Latest Information....228 Known Limitations....228 Use the Diagnostics....229 PSP Optimization Is a Powerful Innovation....230 Cardinality Estimation (CE) Model Feedback....230 What Is the CE Model Problem?....231 How Does CE Feedback Work?....232 Try Out an Exercise....233 Prerequisites....233 Follow These Steps for the Exercise....234 Limits and More Details About CE Feedback....243 Degree of Parallelism (DOP) Feedback....244 Why Did We Build DOP Feedback?....245 What Is DOP Feedback?....246 How Does DOP Feedback Work?....246 Lets Try DOP Feedback....249 What Else Should I Know About DOP Feedback?....259 An Engine That Works for You....261 Chapter 6: The Meat and Potatoes of SQL Server....263 The Core Engine Is the Meat and Potatoes of SQL Server....263 Security....264 Ledger for SQL Server....264 Background of Ledger for SQL Server....265 How Does It Work....266 Exercise to Use Ledger for SQL Server....269 What Else Should You Know?....282 Encryption Enhancements....283 Always Encrypted Enhancements....284 Crypto Enhancements....285 Strict Connected Encryption....286 Security Permission Enhancements....287 New Fixed Server-Level Roles....287 Dynamic Data Masking Enhancements....289 Performance and Scalability....290 Columnstore and Batch Mode Improvements....291 Ordered Clustered Columnstore Index....291 Columnstore String Improvements....291 Vector Extension to Improve Batch Mode....291 Columnstore Segment Elimination Enhancements....293 Scalability Improvements....293 Buffer Pool Parallel Scan....293 Purvis List....294 Hands-Free tempdb....296 What Is the Challenge?....296 Solutions Over the Years....297 Why Now Hands-Free?....298 Try It Yourself....299 More Concurrency Improvements....306 Shrink Database Concurrency....306 Auto-update Stats Concurrency....306 Availability....307 Contained Availability Groups....307 How Does It Work?....308 Lets Try It Out....310 Items to Consider....320 Other AG Enhancements....320 AG Reliability and Supportability....320 Distributed Availability Group (DAG) Enhancements....321 Recovery Enhancements....322 Accelerated Database Recovery (ADR) Enhancements....322 Parallel Redo Enhancements....323 BackupRestore Enhancements....324 Cross-Platform SNAPSHOT Backup....324 How Does It Work?....326 Intel QuickAssist (QAT) Backup Compression....327 Backup Metadata....329 Multi-write Replication....329 Other Engine Stuff....330 XML Compression....330 In-Memory OLTP Memory Management....331 Auto-drop Statistics....331 Resumable Add Table Constraints....331 New Wait Types....332 New Extended Events....332 An Industry-Proven Engine....332 Chapter 7: Data Virtualization and Object Storage....334 Data Virtualization in SQL Server 2022....335 REST, Azure Storage, and S3....335 Project Gravity Becomes Polybase v3....336 Polybase v3 File Formats....338 Parquet....338 Delta....339 Using the New Polybase v3....339 Install and Configure Polybase....340 Set Up Credentials and Data Sources....340 Query Files Directly with OPENROWSET( )....341 Create an External Table....341 Try Out Polybase v3....341 Prerequisites....342 Set Up minio for the Exercise....343 Learn to Use REST API to Access Parquet Files on S3....346 Learn How to Use Delta Files....357 What Else Do You Need to Know....361 Backup and Restore with S3 Compatible Object Storage....361 How Does It Work?....362 Lets Look at an Example....363 Migration from AWS....365 SQL Server Is a Data Hub....367 Chapter 8: New Application Scenarios with T-SQL....368 JSON Functions....369 Prerequisites for Exercises....370 JSON_ARRAY and ISJSON....370 JSON_OBJECT and JSON_PATH_EXISTS....374 T-SQL Surface Area....375 Prerequisites....376 DATETRUNC....376 WINDOW Clause....378 GREATEST and LEAST....380 IS [NOT] DISTINCT FROM....383 STRING_SPLIT....386 TRIM Function Extensions....387 Bit T-SQL Functions....389 Getting Packed Bits....389 Packing Bits into a Value....394 Time Series....396 Prerequisites....397 DATE_BUCKET....397 GENERATE_SERIES....399 Gap Filling with FIRST_VALUE and LAST_VALUE....402 T-SQL Is Alive and Well....405 Chapter 9: SQL Server 2022 on Linux, Containers, and Kubernetes....406 SQL Server 2022 on Linux....407 Whats New for SQL Server 2022....407 New Capabilities Specific to Linux....408 Deploying SQL Server 2022 on Linux....409 What Else Should I Know?....410 Ansible with RHEL....411 How to Connect and Use SQL Server 2022 on Linux....411 ssh vs. rdp....411 Connecting with Our Tools....412 Configuring with mssql-conf....412 Active Directory Authentication with adutil....413 Azure Extension for SQL Server....413 Optimizing SQL Server 2022 on Linux....413 HADR for SQL Server 2022 on Linux....414 Failover Clustering....415 Always On Availability Groups (AGs)....415 HPE Serviceguard....415 BACKUPRESTORE....416 SQL Server 2022 Containers....416 Why Containers?....416 Using SQL Server 2022 Containers....418 Running a SQL Server Container....418 Connecting to a SQL Server Container....420 Building a Customized Container Image....421 The Container Switch Method....421 SQL Server 2022 on Kubernetes....422 Why k8s....423 Deploying SQL Server on k8s....423 What Are the Major Components of k8s?....423 How Do I Get k8s?....426 What Are the Deployment Steps?....426 Connecting and Using SQL Server on k8s....427 High Availability with k8s....427 Basic HA with SQL and k8s....427 Always On Availability Groups and k8s....428 Helm Charts....429 The World of Linux, Containers, and k8s....429 Chapter 10: SQL Server 2022 on Azure Virtual Machines....430 What Is SQL Server on Azure Virtual Machines?....430 Planning for Deployment....431 The SQL Server IaaS Agent Extension....435 What Is the SQL Server IaaS Agent Extension?....435 IaaS Agent Extension Modes....436 Full....436 Lightweight....436 Installing the Agent Extension....436 Details of the Agent Extension....437 Deploying SQL Server on an Azure Virtual Machine....438 Prerequisites....438 Steps to Deploy....438 Other Deployment Methods....448 Exploring and Connecting to the SQL Server Virtual Machine....449 Exploring Your Azure Virtual Machine in the Portal....449 Connecting to the Azure Virtual Machine....453 Exploring a SQL Virtual Machine in the Portal....455 Connecting to SQL Server on an Azure Virtual Machine....456 Migrating to SQL Server on an Azure Virtual Machine....457 Optimizing Performance....458 Virtual Machine Size....458 Storage Performance....459 SQL Best Practices Assessment....460 High Availability....462 Built-In Fault Tolerance with Azure....462 Failover Cluster Instance (FCI)....463 Always On Availability Groups....464 Disaster Recovery....466 Storage Fault Tolerance....466 Backup Database Options....466 Using Azure Backup....467 Monitoring....467 Azure Monitor....468 Virtual Machine Metrics and Logs....468 Azure Insights....469 Azure Is the Best Cloud for SQL Server....469 Chapter 11: SQL Edge to Cloud....471 Develop Once, Deploy Anywhere....471 Azure SQL Edge....473 When to Use Azure SQL Edge....474 SQL Server....474 When to Use SQL Server....475 Azure Arc–Enabled SQL Server....476 Linux, Containers, and Kubernetes....476 When to Use SQL Server on Linux....476 When to Use SQL Server Containers....477 When to Use SQL Server on Kubernetes....478 SQL Server on Azure Virtual Machines....479 When to Use SQL Server on Azure Virtual Machines....480 Azure SQL Managed Instance....481 When to Use Azure SQL Managed Instance....482 Azure SQL Database....483 When to Use Azure SQL Database....485 Azure Arc–Enabled SQL Managed Instance....486 When to Use Azure Arc–Enabled SQL Managed Instance....488 SQL Is Everywhere You Need It....488 Index....490