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

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 bookcovers 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 areassuch 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