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

Know how to use the new capabilities and cloud integrations in SQL Server 2022. This book covers the many innovative integrations with the Azure Cloud that make SQL Server 2022 the most cloud-connected edition ever. The book covers cutting-edge features such as the blockchain-based Ledger for creating a tamper-evident record of changes to data over time that you can rely on to be correct and reliable. You'll learn about built-in Query Intelligence capabilities to help you to upgrade with confidence that your applications will perform at least as fast after the upgrade than before. In fact, you'll probably see an increase in performance from the upgrade, with no code changes needed. Also covered are innovations such as contained availability groups and data virtualization with S3 object storage. New cloud integrations covered in this book include Microsoft Azure Purview and the use of Azure SQL for high availability and disaster recovery. The book covers Azure Synapse Link with its built-in capabilities to take changes and put them into Synapse automatically. Anyone building their career around SQL Server will want this book for the valuable information it provides on building SQL skills from edge to the cloud. ​ What You Will Learn Know how to use all of the new capabilities and cloud integrations in SQL Server 2022 Connect to Azure for disaster recovery, near real-time analytics, and security Leverage the Ledger to create a tamper-evident record of data changes over time Upgrade from prior releases and achieve faster and more consistent performance with no code changes Access data and storage in different and new formats, such as Parquet and S3, without moving the data and using your existing T-SQL skills Explore new application scenarios using innovations with T-SQL in areas such as JSON and time series Who This Book Is For SQL Server professionals who want to upgrade their skills to the latest edition of SQL Server; those wishing to take advantage of new integrations with Microsoft Azure Purview (governance), Azure Synapse (analytics), and Azure SQL (HA and DR); and those in need of the increased performance and security offered by Query Intelligence and the new Ledger Table of Contents About the Author About the Technical Reviewer Acknowledgments Foreword Introduction Chapter 1: Project Dallas Becomes SQL Server 2022 Project Dallas Becoming SQL Server 2022 Announcing SQL Server 2022 Private to Public Preview The Path to General Availability Introducing SQL Server 2022 Built on a Foundation Wheel of Power Cloud Connected Built-In Query Intelligence Industry-Proven Database Engine Data Virtualization and Object Storage Enhancing T-SQL for Developers Getting Started with SQL Server 2022 How to Get SQL Server 2022 Installing SQL Server 2022 Learn All the Features and Editions Learn About Pricing and Licensing Get Training on SQL Server 2022 Go Deeper with Our Blog Series Download Book Code and Samples A Cloud-Connected, Intelligent, and Industry-Proven Data Platform Chapter 2: Install and Upgrade How to Install SQL Server 2022 Prerequisites What Is Different for SQL Server 2022? Azure Extension for SQL Server Feature Differences Removal of R, Python, and Java Removal of Polybase Hadoop Connectivity with Java Removal of Machine Learning Server Removal of Distributed Replay Memory Recommendations Other Installation Methods Setting Up the Azure Extension for SQL Server What You Should Know First Providing Values for the Feature Setup Checking the Azure Extension for SQL Server Login or Service Principal Connect to Azure After Setup Removing the Azure Extension for SQL Server Deploying on Other Platforms Side-by-Side and Multi-instance Installations How to Upgrade to SQL Server 2022 The Importance of dbcompat Configuration Easy to Install and Upgrade Chapter 3: Connect Your Database to the Cloud The Hybrid SQL Server What Is Hybrid? SQL Server Hybrid Over the Years The SQL Server 2022 Hybrid Lineup Azure SQL Managed Instance Azure Synapse Analytics Azure Active Directory Authentication Microsoft Purview Microsoft Defender for SQL Azure Arc Agents and Azure Extension for SQL Server Managed Disaster Recovery with Azure SQL Managed Instance Project Chimera and DAG Distributed Availability Groups Project Chimera The Link Feature for Azure SQL Managed Instance How It Works Creating and Using the Link Failing Over to Azure SQL Managed Instance Using the Link Feature for Offline Disaster Recovery Why Managed Disaster Recovery? SQL Server and Database Version Compatibility Prerequisites Preparing the Environment Creating the Link to Replicate the Database See Changes Replicated Failover to Managed Instance Restoring a Database Back to SQL Server Keep in Mind These Details The Future for the Link Feature for Azure SQL Managed Instance Azure Synapse Link for SQL Server What Is Synapse Link for SQL Server? How Does Synapse Link Work? Try Out Synapse Link for SQL Server Prerequisites Set Up the Exercise Synchronizing Data with Synapse Link Near-Real-Time Analytics with Changes from SQL Server More Details About Synapse Link Configuration Choices Transaction Consistency Monitoring Synapse Link Limits and Restrictions Synapse Link Could Change Analytics for You Azure Active Directory (AAD) Authentication How Does AAD Authentication Work? Setting Up and Using AAD Authentication Prerequisites Set Up AAD with SQL Server 2022 Using AAD with SQL Server Microsoft Purview Policy Management How Do Purview Access Policies Work? Using Purview Access Policies Prerequisites Set Up Microsoft Purview Access Policies Using Microsoft Purview Access Policies Connecting SQL to the World Chapter 4: Built-In Query Intelligence Built-In Query Intelligence in SQL Server 2022 The New Query Store On by Default Query Store Hints How Do You Use Query Store Hints? When Should I Use a Query Store Hint? How Is This Different Than Plan Guides? Query Store Support for Secondary Replicas How to Do You Configure It? How Does It Work? How Do You Use It? Store for IQP IQP Nextgen Defaults Approximate Percentile Optimized Plan Forcing The Background How Does It Work? See It in Action Considerations for Using Optimized Plan Forcing IQP Nextgen with dbcompat 140+ Memory Grant Percentiles Memory Grant Feedback Persistence Prerequisites Follow These Steps for the Exercise The Intelligent Query Processor Chapter 5: Built-In Query Intelligence Gets Even Better Parameter-Sensitive Plan (PSP) Optimization What Is a Parameter-Sensitive Plan? Learn PSP Through a Scenario How It All Started How Does PSP Optimization Work? Let’s See PSP Optimization in Action Prerequisites Follow These Steps for the Exercise What Other Details About PSP Optimization Should I Know? Check the Latest Information Known Limitations Use the Diagnostics PSP Optimization Is a Powerful Innovation Cardinality Estimation (CE) Model Feedback What Is the CE Model Problem? How Does CE Feedback Work? Try Out an Exercise Prerequisites Follow These Steps for the Exercise Limits and More Details About CE Feedback Degree of Parallelism (DOP) Feedback Why Did We Build DOP Feedback? What Is DOP Feedback? How Does DOP Feedback Work? Let’s Try DOP Feedback What Else Should I Know About DOP Feedback? An Engine That Works for You Chapter 6: The Meat and Potatoes of SQL Server The Core Engine Is the Meat and Potatoes of SQL Server Security Ledger for SQL Server Background of Ledger for SQL Server How Does It Work Exercise to Use Ledger for SQL Server What Else Should You Know? Encryption Enhancements Always Encrypted Enhancements Crypto Enhancements Strict Connected Encryption Security Permission Enhancements New Fixed Server-Level Roles Dynamic Data Masking Enhancements Performance and Scalability Columnstore and Batch Mode Improvements Ordered Clustered Columnstore Index Columnstore String Improvements Vector Extension to Improve Batch Mode Columnstore Segment Elimination Enhancements Scalability Improvements Buffer Pool Parallel Scan Purvi’s List “Hands-Free” tempdb What Is the Challenge? Solutions Over the Years Why Now “Hands-Free”? Try It Yourself More Concurrency Improvements Shrink Database Concurrency Auto-update Stats Concurrency Availability Contained Availability Groups How Does It Work? Let’s Try It Out Items to Consider Other AG Enhancements AG Reliability and Supportability Distributed Availability Group (DAG) Enhancements Recovery Enhancements Accelerated Database Recovery (ADR) Enhancements Parallel Redo Enhancements Backup/Restore Enhancements Cross-Platform SNAPSHOT Backup How Does It Work? Intel QuickAssist (QAT) Backup Compression Backup Metadata Multi-write Replication Other Engine “Stuff” XML Compression In-Memory OLTP Memory Management Auto-drop Statistics Resumable Add Table Constraints New Wait Types New Extended Events An Industry-Proven Engine Chapter 7: Data Virtualization and Object Storage Data Virtualization in SQL Server 2022 REST, Azure Storage, and S3 Project Gravity Becomes Polybase v3 Polybase v3 File Formats Parquet Delta Using the New Polybase v3 Install and Configure Polybase Set Up Credentials and Data Sources Query Files Directly with OPENROWSET( ) Create an External Table Try Out Polybase v3 Prerequisites Set Up minio for the Exercise Learn to Use REST API to Access Parquet Files on S3 Learn How to Use Delta Files What Else Do You Need to Know Backup and Restore with S3 Compatible Object Storage How Does It Work? Let’s Look at an Example Migration from AWS SQL Server Is a Data Hub Chapter 8: New Application Scenarios with T-SQL JSON Functions Prerequisites for Exercises JSON_ARRAY and ISJSON JSON_OBJECT and JSON_PATH_EXISTS T-SQL Surface Area Prerequisites DATETRUNC WINDOW Clause GREATEST and LEAST IS [NOT] DISTINCT FROM STRING_SPLIT TRIM Function Extensions Bit T-SQL Functions Getting Packed Bits Packing Bits into a Value Time Series Prerequisites DATE_BUCKET GENERATE_SERIES Gap Filling with FIRST_VALUE and LAST_VALUE T-SQL Is Alive and Well Chapter 9: SQL Server 2022 on Linux, Containers, and Kubernetes SQL Server 2022 on Linux What’s New for SQL Server 2022 New Capabilities Specific to Linux Deploying SQL Server 2022 on Linux What Else Should I Know? Ansible with RHEL How to Connect and Use SQL Server 2022 on Linux ssh vs. rdp Connecting with Our Tools Configuring with mssql-conf Active Directory Authentication with adutil Azure Extension for SQL Server Optimizing SQL Server 2022 on Linux HADR for SQL Server 2022 on Linux Failover Clustering Always On Availability Groups (AGs) HPE Serviceguard BACKUP/RESTORE SQL Server 2022 Containers Why Containers? Using SQL Server 2022 Containers Running a SQL Server Container Connecting to a SQL Server Container Building a Customized Container Image The Container Switch Method SQL Server 2022 on Kubernetes Why k8s Deploying SQL Server on k8s What Are the Major Components of k8s? How Do I Get k8s? What Are the Deployment Steps? Connecting and Using SQL Server on k8s High Availability with k8s Basic HA with SQL and k8s Always On Availability Groups and k8s Helm Charts The World of Linux, Containers, and k8s Chapter 10: SQL Server 2022 on Azure Virtual Machines What Is SQL Server on Azure Virtual Machines? Planning for Deployment The SQL Server IaaS Agent Extension What Is the SQL Server IaaS Agent Extension? IaaS Agent Extension Modes Full Lightweight Installing the Agent Extension Details of the Agent Extension Deploying SQL Server on an Azure Virtual Machine Prerequisites Steps to Deploy Other Deployment Methods Exploring and Connecting to the SQL Server Virtual Machine Exploring Your Azure Virtual Machine in the Portal Connecting to the Azure Virtual Machine Exploring a SQL Virtual Machine in the Portal Connecting to SQL Server on an Azure Virtual Machine Migrating to SQL Server on an Azure Virtual Machine Optimizing Performance Virtual Machine Size Storage Performance SQL Best Practices Assessment High Availability Built-In Fault Tolerance with Azure Failover Cluster Instance (FCI) Always On Availability Groups Disaster Recovery Storage Fault Tolerance Backup Database Options Using Azure Backup Monitoring Azure Monitor Virtual Machine Metrics and Logs Azure Insights Azure Is the Best Cloud for SQL Server Chapter 11: SQL Edge to Cloud Develop Once, Deploy Anywhere Azure SQL Edge When to Use Azure SQL Edge SQL Server When to Use SQL Server Azure Arc–Enabled SQL Server Linux, Containers, and Kubernetes When to Use SQL Server on Linux When to Use SQL Server Containers When to Use SQL Server on Kubernetes SQL Server on Azure Virtual Machines When to Use SQL Server on Azure Virtual Machines Azure SQL Managed Instance When to Use Azure SQL Managed Instance Azure SQL Database When to Use Azure SQL Database Azure Arc–Enabled SQL Managed Instance When to Use Azure Arc–Enabled SQL Managed Instance SQL Is Everywhere You Need It Index