Pro SQL Server 2022 Administration: A Guide for the Modern DBA. 3 Ed

Table of Contents....5

About the Author....19

About the Technical Reviewer....20

Acknowledgments....21

Part I: Installation and Configuration....22

Chapter 1: Planning the Deployment....23

Editions and License Models....23

Hardware Considerations....26

Specifying Strategic Minimum Requirements....27

Storage....27

Locally Attached Storage....27

RAID 0....28

RAID 1....29

RAID 5....29

RAID 10....31

File Placement....33

Solid-State Drives (SSDs)....34

Working with a SAN....34

Disk Block Size....36

Cloud Storage....37

Operating Systems Considerations....38

Configuring the Operating System....39

Setting the Power Plan....40

Optimizing for Background Services....40

Assigning User Rights....41

Initializing the Instant File....41

Locking Pages in Memory....42

SQL Audit to the Event Log....43

Selecting Features....44

Instance Features....45

Database Engine Service....45

Analysis Services....46

Shared Features....47

Data Quality Client....47

Integration Services....47

SQL Client Connectivity SDK....48

Master Data Services....48

SQL Server Extension for Azure....48

Summary....49

Chapter 2: GUI Installation....50

Obtaining SQL Server Media....50

Installation Center....51

The Planning Tab....52

The Installation Tab....53

The Maintenance Tab....55

The Tools Tab....56

The Resources Tab....57

The Advanced Tab....58

The Options Tab....60

Installing a Stand-Alone Database Engine Instance....61

Preparation Steps....61

The Feature Selection Page....68

The Instance Configuration Page....69

Selecting Service Accounts....71

Choosing the Collation....74

Provisioning Instance Security....81

Configuring the Instance....83

Configuring SQL Server Extension for Azure....89

Completing the Installation....90

Summary....92

Chapter 3: Server Core Installation....93

Installing an Instance....93

Required Parameters....94

IACCEPTSQLSERVERLICENSETERMS Switch....94

Additional License Terms Parameters....95

ACTION Parameter....95

FEATURES Parameter....96

Role Parameter....97

Basic Installation....98

Smoke Tests....99

Troubleshooting the Installation....102

Summary.txt....103

Detail.txt....103

SystemConfigurationCheck_Report.htm....104

Other Log Files....104

Optional Parameters....105

Product Update....109

Using a Config File....111

Automatic Installation Routines....115

Enhancing the Installation Routine....117

Production Readiness....118

Summary....120

Chapter 4: Installation on Heterogeneous Operating Systems....122

Installing SQL Server on Linux....122

Installing SQL Server Manually....123

Configuring SQL Server....126

Unattended Installation....130

Installing and Managing SQL Server in a Docker Container....133

Running a Microsoft-Supplied Docker Image....134

Working with Containers....135

Creating Custom SQL Server Containers....137

Creating a Simple Docker Image for SQL Server....138

Create a Configurable Docker Image for SQL Server....143

Summary....150

Chapter 5: Configuring the Instance....151

Instance Configuration....151

Using sp_configure....151

Processor and Memory Configuration....153

Processor Affinity....153

MAXDOP....160

Min and Max Server Memory....162

Trace Flags....166

Trace Flag 3042....170

Trace Flag 3226....170

Trace Flag 3625....171

Ports and Firewalls....171

Process of Communication....171

Ports Required by SQL Server....172

Configuring the Port That the Instance Will Listen On....174

System Databases....177

mssqlsystemresource (Resource)....177

MSDB....177

Master....178

Model....179

TempDB....179

Optimizing TempDB....181

Buffer Pool Extension....184

Hybrid Buffer Pool....186

Summary....186

Part II: Database Administration....188

Chapter 6: Database Configuration....189

Data Storage....189

Filegroups....190

FILESTREAM Filegroups....194

Memory-Optimized Filegroups....201

Strategies for Structured Filegroups....204

Performance Strategies....204

Backup and Restore Strategies....205

Storage-Tiering Strategies....206

Strategies for Memory-Optimized Filegroups....207

File and Filegroup Maintenance....208

Adding Files....208

Expanding Files....211

Shrinking Files....212

Database Scoped Configurations....215

Log Maintenance....218

Recovery Model....220

Log File Count....221

Shrinking the Log....221

Log Fragmentation....222

Summary....230

Chapter 7: Table Optimizations....232

Table Partitioning....232

Partitioning Concepts....233

Partitioning Key....233

Partition Function....234

Partition Scheme....234

Index Alignment....234

Partitioning Hierarchy....235

Implementing Partitioning....236

Creating the Partitioning Objects....236

Creating a New Partitioned Table....237

Partitioning an Existing Table....239

Monitoring Partitioned Tables....243

$PARTITION Function....244

Sliding Windows....246

Partition Elimination....249

Table Compression....251

Row Compression....252

Page Compression....253

Prefix Compression....253

Dictionary Compression....254

Page Compression Structure....254

Columnstore Compression....255

XML Compression....256

Implementing Compression....257

Selecting the Compression Level....257

Compressing Tables and Partitions....259

Data Compression Wizard....259

Maintaining Compression on Heaps....260

Maintaining Compressed Partitions....261

Memory-Optimized Tables....261

Durability....262

Creating and Managing Memory-Optimized Tables....262

Performance Profile....265

Table Memory Optimization Advisor....269

Natively Compiled Objects....270

Natively Compiled Tables....271

Natively Compiled Stored Procedures....271

Summary....273

Chapter 8: Indexes and Statistics....275

Clustered Indexes....275

Tables Without a Clustered Index....276

Tables with a Clustered Index....277

Clustering the Primary Key....278

Administering Clustered Indexes....279

Nonclustered Indexes....283

Covering Indexes....284

Administering Nonclustered Indexes....285

Filtered Indexes....291

Indexes for Specialized Application....291

Columnstore Indexes....292

Clustered Columnstore Indexes....293

Nonclustered Columnstore Indexes....296

In-Memory Indexes....297

In-Memory Nonclustered Hash Indexes....297

In-Memory Nonclustered Indexes....303

Maintaining Indexes....303

Missing Indexes....304

Index Fragmentation....306

Detecting Fragmentation....307

Removing Fragmentation....309

Resumable Index Operations....311

Partitioned Indexes....314

Statistics....316

Filtered Statistics....318

Incremental Statistics....319

Managing Statistics....319

Managing Cardinality....322

Summary....324

Chapter 9: Database Consistency....326

Consistency Errors....326

Understand Consistency Errors....326

605 Error....326

823 Error....327

824 Error....327

5180 Error....327

7105 Error....328

Detecting Consistency Errors....328

Page Verify Option....328

Suspect Pages....332

Consistency Issues for Memory-Optimized Tables....337

System Database Corruption....338

Corruption of the Master Database....338

Corruption of the Resource Database or Binaries....339

DBCC CHECKDB....341

Checking for Errors....341

Fixing Errors....347

Emergency Mode....350

Other DBCC Commands for Corruption....352

DBCC CHECKCATALOG....352

DBCC CHECKALLOC....352

DBCC CHECKTABLE....353

DBCC CHECKFILEGROUP....353

DBCC CHECKIDENT....353

DBCC CHECKCONSTRAINTS....355

Consistency Checks on VLDBs....357

DBCC CHECKDB with PHYSICAL_ONLY....357

Backing Up WITH CHECKSUM and DBCC CHECKALLOC....358

Splitting the Workload....358

Offloading to a Secondary Server....359

Summary....359

Part III: Security, Resilience, and Scaling Workloads....361

Chapter 10: SQL Server Security Model....362

Security Hierarchy....362

Implementing Instance-Level Security....366

Server Roles....367

Logins....373

Granting Permissions....376

Implementing Database-Level Security....377

Database Roles....377

Schemas....381

Creating and Managing Contained Users....384

Implementing Object-Level Security....388

Summary....389

Chapter 11: Auditing and Ledger....390

Ledger....390

Append-Only Ledger Tables....391

Ledger Database....393

Database Ledger and Digest Management....396

Server Audit....401

Creating a Server Audit....401

Creating a Server Audit Specification....405

Enabling and Invoking Audits....407

Updateable Ledger Tables....408

Database Audit Specifications....416

Auditing the Audit....421

Summary....422

Chapter 12: Encryption....423

Encryption Hierarchy....423

Encryption Concepts....423

Symmetric Keys....423

Asymmetric Keys....424

Certificates....424

Windows Data Protection API....424

SQL Server Encryption Concepts....424

Master Keys....424

Hierarchy....427

Transparent Data Encryption....429

Implementing TDE....430

Managing TDE....436

Migrating an Encrypted Database....437

Managing Cell-Level Encryption....438

Accessing Encrypted Data....443

Always Encrypted....445

Implementing Always Encrypted....447

Administering Keys....454

Summary....459

Chapter 13: Backups and Restores....461

Backup Fundamentals....461

Recovery Models....461

SIMPLE Recovery Model....461

FULL Recovery Model....462

BULK LOGGED Recovery Model....463

Changing the Recovery Model....464

Backup Types....467

Full Backup....467

Differential Backup....467

Log Backup....467

Backup Media....468

Backup Device....469

Media Sets....470

Backup Sets....470

Backup Strategies....470

Full Backup Only....471

Full and Transaction Log Backups....471

Full, Differential, and Transaction Log Backups....472

Filegroup Backups....472

Partial Backup....473

Backing Up a Database....473

Backing Up in SQL Server Management Studio....473

Backing Up via T-SQL....476

Restoring a Database....483

Restoring in SQL Server Management Studio....483

Restoring via T-SQL....488

Restoring to a Point in Time....494

Restoring Files and Pages....498

Restoring a File....498

Restoring a Page....500

Piecemeal Restores....503

Accelerated Database Recovery....506

Summary....509

Chapter 14: High Availability and Disaster Recovery Concepts....512

Availability Concepts....513

Level of Availability....513

Service-Level Agreements and Service-Level Objectives....515

Proactive Maintenance....516

Recovery Point Objective and Recovery Time Objective....516

Cost of Downtime....518

Classification of Standby Servers....519

High Availability and Recovery Technologies....520

AlwaysOn Failover Clustering....520

Active/Active Configuration....522

Three-Plus Node Configurations....523

Quorum....525

AlwaysOn Availability Groups....527

Automatic Page Repair....532

Log Shipping....533

Recovery Modes....534

Remote Monitor Server....535

Failover....536

Combining Technologies....536

Summary....539

Chapter 15: Implementing AlwaysOn Availability Groups....540

Implementing AlwaysOn Availability Groups....541

Configuring SQL Server....547

Creating the Availability Group....549

Using the New Availability Group Wizard....549

Using the New Availability Group Dialog Box....559

Availability Groups On Linux....564

Contained Availability Group....571

Distributed Availability Groups....577

Managing AlwaysOn Availability Groups....580

Failover....580

Synchronous Failover....580

Asynchronous Failover....583

Synchronizing Uncontained Objects....586

Monitoring....587

AlwaysOn Dashboard....587

AlwaysOn Health Trace....589

Other Administrative Considerations....590

Summary....592

Chapter 16: Implementing Log Shipping....594

Implementing Log Shipping for DR....594

GUI Configuration....597

T-SQL Configuration....608

Log Shipping Maintenance....620

Failing Over Log Shipping....620

Switching Roles....621

Monitoring....627

Summary....630

Chapter 17: Scaling Workloads....632

Database Snapshots....632

Implementing Database Snapshots....635

Recovering Data from a Snapshot....639

Replication....640

Replication Concepts....640

Types of Replication....642

Snapshot....642

Transactional....643

Merge....644

Implementing Transactional Replication....645

Implementing the Distributor....645

Implementing the Publication....650

Implementing the Subscriber....660

Modifying the PAL....668

AlwaysOn Readable Secondary Replicas....670

Benefits and Considerations....670

Implementing Readable Secondaries....671

Summary....682

Part IV: Performance and Maintenance....684

Chapter 18: SQL Server Metadata....685

Introducing Metadata Objects....685

Server-Level and Instance-Level Metadata....689

Exposing Registry Values....689

Exposing Service Details....690

Analyzing Buffer Cache Usage....691

Metadata for Capacity Planning....693

Exposing File Stats....693

Using File Stats for Capacity Analysis....697

Metadata for Troubleshooting and Performance Tuning....701

Retrieving Perfmon Counters....701

Analyzing Waits....706

Database Metadata....710

Metadata-Driven Automation....714

Dynamically Cycling Database Snapshots....715

Rebuilding Only Fragmented Indexes....718

Summary....719

Chapter 19: Locking and Blocking....721

Understanding Locking....721

Lock Granularity....721

Locking Behaviors for Online Maintenance....723

Lock Compatibility....728

Lock Partitioning....729

Understanding Deadlocks....730

How Deadlocks Occur....730

Minimizing Deadlocks....732

Understanding Transactions....732

Transactional Properties....733

Atomic....733

Consistent....736

Isolated....737

Transactional Anomalies....737

Dirty Reads....737

Nonrepeatable Read....737

Phantom Read....738

Isolation Levels....739

Pessimistic Isolation Levels....739

Optimistic Isolation Levels....740

Durable....742

Transaction with In-Memory OLTP....744

Isolation Levels....744

Read Committed....744

Read Committed Snapshot....745

Snapshot....745

Repeatable Read....745

Serializable....746

Cross-Container Transactions....746

Retry Logic....748

Observing Transactions, Locks, and Deadlocks....749

Observing Transactions....749

Observing Locks and Contention....753

Observing Deadlocks....758

Summary....759

Chapter 20: Extended Events....760

Extended Events Concepts....760

Packages....760

Events....761

Targets....762

Actions....762

Predicates....763

Types and Maps....763

Sessions....765

Creating an Event Session....765

Using the New Session Dialog Box....768

Using T-SQL....775

Viewing the Collected Data....778

Analyzing Data with Data Viewer....779

Analyzing Data with T-SQL....784

Correlating Extended Events with Operating System Data....788

Correlating Events with Perfmon Data....788

Integrating Event Sessions with Operating System–Level Events....790

Summary....797

Chapter 21: Monitoring and Managing a Hybrid Environment....799

Hybrid Management....799

Environmental Health and SQL Best Practice Assessment....801

Microsoft Defender....815

Summary....824

Chapter 22: Query Store....826

Enabling and Configuring Query Store....826

Working with Query Store Data....832

Query Store Reports....834

Query Store T-SQL Objects....839

Resolving Issues with Query Store....844

Query Store Hints....845

Summary....848

Chapter 23: Automating Maintenance Routines....850

SQL Server Agent....850

SQL Server Agent Concepts....851

Schedules....851

Operators....852

Jobs....853

Alerts....855

SQL Server Agent Security....855

SQL Server Agent Database Roles....856

SQL Server Agent Proxy Accounts....858

Creating SQL Server Agent Jobs....858

Creating a Simple SQL Server Agent Job....862

Creating a Complex SQL Server Agent Job....868

Creating the Credential....868

Creating the Proxy....870

Creating the Schedule....871

Configuring Database Mail....873

Creating the Operator....880

Creating the Job....881

Monitoring and Managing Jobs....888

Executing Jobs....888

Viewing Job History....890

Creating Alerts....891

Multiserver Jobs....894

Configuring the MSX and TSX Servers....895

Creating Master Jobs....900

Managing Target Servers....902

Summary....906

Chapter 24: Policy-Based Management....908

PBM Concepts....908

Facets....908

Conditions....909

Targets....909

Policies....910

Evaluation Modes....910

Central Management Servers....912

Creating Policies....919

Creating Simple Policies....919

Creating a Policy That You Can Manually Evaluate....920

Manually Evaluating a Policy....923

Creating a Policy That Prevents Unwanted Activity....925

Creating an Advanced Policy....928

Managing Policies....931

Importing and Exporting Policies....932

Enterprise Management with Policies....933

Evaluating Policies with PowerShell....935

Summary....936

Chapter 25: Resource Governor....938

Resource Governor Concepts....938

Resource Pool....938

Workload Group....940

Classifier Function....941

Implementing Resource Governor....941

Creating Resource Pools....941

Creating Workload Groups....945

Creating a Classifier Function....949

Testing the Classifier Function....953

Monitoring Resource Governor....954

Monitoring with Performance Monitor....954

Monitoring with DMVs....958

Summary....967

Index....968

Get your daily work done efficiently using this comprehensive guide for SQL Server DBAs that covers all that a practicing database administrator needs to know. Updated for SQL Server 2022, this edition includes coverage of new features, such as Ledger, which provides an immutable record of table history to protect you against malicious data tampering, and integration with cloud providers to support hybrid cloud scenarios. You’ll also find new content on performance optimizations, such as query pan feedback, and security controls, such as new database roles, which are restructured for modern ways of working. Coverage also includes Query Store, installation on Linux, and the use of containerized SQL.

Pro SQL Server 2022 Administration takes DBAs on a journey that begins with planning their SQL Server deployment and runs through installing and configuring the instance, administering and optimizing database objects, and ensuring that data issecure and highly available. Readers will learn how to perform advanced maintenance and tuning techniques, and discover SQL Server's hybrid cloud functionality.

This book teaches you how to make the most of new SQL Server 2022 functionality, including integration for hybrid cloud scenarios. The book promotes best-practice installation, shows how to configure for scalability and high availability, and demonstrates the gamut of database-level maintenance tasks, such as index maintenance, database consistency checks, and table optimizations.

What You Will Learn

• Integrate SQL Server with Azure for hybrid cloud scenarios
• Audit changes and prevent malicious data changes with SQL Server’s Ledger
• Secure and encrypt data to protect against embarrassing data breaches
• Ensure 24 x 7 x 365 access through high availability and disaster recovery features in today’s hybrid world
• Use Azure tooling, including Arc, to gain insight into and manage your SQL Server enterprise
• Install and configure SQL Server on Windows, Linux, and in containers
• Perform routine maintenance tasks, such as backups and database consistency checks
• Optimize performance and undertake troubleshooting in the Database Engine

Who This Book Is For

SQL Server DBAs who manage on-premise installations of SQL Server. This book is also useful for DBAs who wish to learn advanced features, such as integration with Azure, Query Store, Extended Events, and Policy-Based Management, or those who need to install SQL Server in a variety of environments.