vi
Distributed Replay Controller................................................................................................. 26
Distributed Replay Client....................................................................................................... 26
SQL Client Connectivity SDK.................................................................................................. 26
Master Data Services............................................................................................................ 27
Summary.................................................................................................................................... 27
Chapter 2: GUI Installation...................................................................................... 29
Installation Center....................................................................................................................... 29
The Planning Tab................................................................................................................... 29
The Installation Tab................................................................................................................ 31
The Maintenance Tab............................................................................................................ 32
The Tools Tab......................................................................................................................... 34
The Resources Tab................................................................................................................ 35
The Advanced Tab.................................................................................................................. 36
The Options Tab..................................................................................................................... 39
Installing a Stand-Alone Database Engine Instance................................................................... 39
Preparation Steps.................................................................................................................. 40
The Feature Selection Page................................................................................................... 45
The Instance Configuration Page........................................................................................... 48
Selecting Service Accounts................................................................................................... 50
Choosing the Collation........................................................................................................... 53
Provisioning Instance Security.............................................................................................. 59
Configuring the Instance....................................................................................................... 61
Configuring Distributed Replay.............................................................................................. 67
Completing the Installation.................................................................................................... 68
Summary.................................................................................................................................... 70
Chapter 3: Server Core Installation........................................................................ 73
Installing an Instance.................................................................................................................. 73
Required Parameters............................................................................................................. 74
Role Parameter...................................................................................................................... 77
Basic Installation................................................................................................................... 77
Table of Contents

vii
Smoke Tests.......................................................................................................................... 79
Troubleshooting the Installation............................................................................................ 82
Optional Parameters.............................................................................................................. 85
Product Update...................................................................................................................... 89
Using a Config File...................................................................................................................... 91
Automatic Installation Routines.................................................................................................. 95
Enhancing the Installation Routine........................................................................................ 96
Production Readiness............................................................................................................ 98
Summary.................................................................................................................................. 100
Chapter 4: Installation on Heterogeneous Operating Systems............................. 101
Installing SQL Server on Linux.................................................................................................. 101
Installing SQL Server Manually............................................................................................ 102
Configuring SQL Server....................................................................................................... 105
Unattended Installation....................................................................................................... 108
Installing SQL Server in a Docker Container............................................................................. 111
Running a Microsoft-Supplied Docker Image...................................................................... 111
Creating a Simple Docker Image for SQL Server................................................................. 113
Creating a Configurable Docker Image for SQL Server........................................................ 119
Kubernetes Support............................................................................................................. 125
Summary.................................................................................................................................. 125
Chapter 5: Configuring the Instance..................................................................... 127
Instance Configuration.............................................................................................................. 127
Using sp_configure.............................................................................................................. 127
Processor and Memory Configuration................................................................................. 129
Trace Flags.......................................................................................................................... 141
Ports and Firewalls................................................................................................................... 146
Process of Communication.................................................................................................. 146
Ports Required by SQL Server............................................................................................. 148
Configuring the Port That the Instance Will Listen On......................................................... 149
Table of Contents

viii
System Databases.................................................................................................................... 152
mssqlsystemresource (Resource)....................................................................................... 152
MSDB................................................................................................................................... 153
Master................................................................................................................................. 153
Model................................................................................................................................... 154
TempDB............................................................................................................................... 154
Buffer Pool Extension................................................................................................................ 159
Hybrid Buffer Pool..................................................................................................................... 161
Summary.................................................................................................................................. 162
Part II: Database Administration................................................................163
Chapter 6: Database Configuration....................................................................... 165
Data Storage............................................................................................................................. 165
Filegroups............................................................................................................................ 166
FILESTREAM Filegroups...................................................................................................... 170
Memory-Optimized Filegroups............................................................................................ 177
Strategies for Structured Filegroups................................................................................... 180
Strategies for Memory-Optimized Filegroups..................................................................... 182
File and Filegroup Maintenance................................................................................................ 183
Adding Files......................................................................................................................... 183
Expanding Files................................................................................................................... 186
Shrinking Files..................................................................................................................... 187
Database Scoped Configurations.............................................................................................. 190
Log Maintenance...................................................................................................................... 191
Recovery Model................................................................................................................... 192
Log File Count...................................................................................................................... 193
Shrinking the Log................................................................................................................ 194
Log Fragmentation.............................................................................................................. 195
Summary.................................................................................................................................. 202
Table of Contents

ix
Chapter 7: Table Optimizations............................................................................. 205
Table Partitioning...................................................................................................................... 205
Partitioning Concepts.......................................................................................................... 206
Implementing Partitioning................................................................................................... 209
Monitoring Partitioned Tables.............................................................................................. 216
Sliding Windows.................................................................................................................. 218
Partition Elimination............................................................................................................ 221
Table Compression.................................................................................................................... 223
Row Compression................................................................................................................ 224
Page Compression............................................................................................................... 225
Columnstore Compression.................................................................................................. 227
Implementing Compression................................................................................................. 228
Memory-Optimized Tables........................................................................................................ 232
Durability............................................................................................................................. 233
Creating and Managing Memory-Optimized Tables............................................................. 233
Performance Profile............................................................................................................. 236
Table Memory Optimization Advisor.................................................................................... 240
Natively Compiled Objects................................................................................................... 241
Summary.................................................................................................................................. 244
Chapter 8: Indexes and Statistics......................................................................... 245
Clustered Indexes..................................................................................................................... 245
Tables Without a Clustered Index........................................................................................ 246
Tables with a Clustered Index.............................................................................................. 247
Clustering the Primary Key.................................................................................................. 248
Administering Clustered Indexes......................................................................................... 249
Nonclustered Indexes............................................................................................................... 253
Covering Indexes................................................................................................................. 254
Administering Nonclustered Indexes................................................................................... 255
Filtered Indexes................................................................................................................... 260
Table of Contents

x
Indexes for Specialized Application.......................................................................................... 261
Columnstore Indexes........................................................................................................... 261
Clustered Columnstore Indexes........................................................................................... 263
Nonclustered Columnstore Indexes..................................................................................... 265
In-Memory Indexes.............................................................................................................. 266
Maintaining Indexes.................................................................................................................. 272
Missing Indexes................................................................................................................... 273
Index Fragmentation............................................................................................................ 275
Resumable Index Operations............................................................................................... 280
Partitioned Indexes.............................................................................................................. 283
Statistics................................................................................................................................... 284
Filtered Statistics................................................................................................................. 286
Incremental Statistics.......................................................................................................... 287
Managing Statistics.................................................................................................................. 287
Summary.................................................................................................................................. 290
Chapter 9: Database Consistency......................................................................... 293
Consistency Errors.................................................................................................................... 293
Understand Consistency Errors........................................................................................... 293
Detecting Consistency Errors.............................................................................................. 295
System Database Corruption............................................................................................... 304
DBCC CHECKDB......................................................................................................................... 308
Checking for Errors.............................................................................................................. 308
Fixing Errors........................................................................................................................ 313
Emergency Mode................................................................................................................. 315
Other DBCC Commands for Corruption................................................................................ 318
Consistency Checks on VLDBs.................................................................................................. 323
DBCC CHECKDB with PHYSICAL_ONLY................................................................................ 324
Backing Up WITH CHECKSUM and DBCC CHECKALLOC....................................................... 324
Splitting the Workload......................................................................................................... 324
Offloading to a Secondary Server........................................................................................ 325
Summary.................................................................................................................................. 325
Table of Contents

xi
Part III: Security, Resilience, and Scaling Workloads.................................327
Chapter 10: SQL Server Security Model................................................................ 329
Security Hierarchy..................................................................................................................... 329
Implementing Instance-Level Security..................................................................................... 332
Server Roles........................................................................................................................ 334
Logins.................................................................................................................................. 337
Granting Permissions.......................................................................................................... 340
Implementing Database-Level Security.................................................................................... 341
Database Roles.................................................................................................................... 341
Schemas.............................................................................................................................. 345
Creating and Managing Contained Users............................................................................ 348
Implementing Object-Level Security......................................................................................... 351
Server Audit.............................................................................................................................. 352
Creating a Server Audit....................................................................................................... 353
Creating a Server Audit Specification.................................................................................. 356
Enabling and Invoking Audits.............................................................................................. 358
Database Audit Specifications............................................................................................. 359
Auditing the Audit................................................................................................................ 364
Security Reports....................................................................................................................... 365
SQL Data Discovery and Classification................................................................................ 365
Vulnerability Assessment.................................................................................................... 367
Summary.................................................................................................................................. 369
Chapter 11: Encryption......................................................................................... 371
Encryption Hierarchy................................................................................................................. 371
Encryption Concepts............................................................................................................ 371
SQL Server Encryption Concepts......................................................................................... 372
Transparent Data Encryption..................................................................................................... 377
Implementing TDE............................................................................................................... 378
Managing TDE...................................................................................................................... 384
Table of Contents

xii
Managing Cell-Level Encryption............................................................................................... 386
Accessing Encrypted Data................................................................................................... 391
Always Encrypted..................................................................................................................... 392
Implementing Always Encrypted......................................................................................... 394
Administering Keys.............................................................................................................. 403
Summary.................................................................................................................................. 407
Chapter 12: Backups and Restores...................................................................... 409
Backup Fundamentals.............................................................................................................. 409
Recovery Models................................................................................................................. 409
Changing the Recovery Model............................................................................................. 412
Backup Types....................................................................................................................... 415
Backup Media...................................................................................................................... 416
Backup Strategies..................................................................................................................... 418
Full Backup Only.................................................................................................................. 419
Full and Transaction Log Backups....................................................................................... 419
Full, Differential, and Transaction Log Backups................................................................... 420
Filegroup Backups............................................................................................................... 420
Partial Backup..................................................................................................................... 421
Backing Up a Database............................................................................................................. 421
Backing Up in SQL Server Management Studio.................................................................. 421
Backing Up via T-SQL.......................................................................................................... 424
Restoring a Database................................................................................................................ 431
Restoring in SQL Server Management Studio..................................................................... 431
Restoring via T-SQL............................................................................................................. 436
Restoring to a Point in Time...................................................................................................... 441
Restoring Files and Pages........................................................................................................ 445
Restoring a File................................................................................................................... 446
Restoring a Page................................................................................................................. 448
Piecemeal Restores.................................................................................................................. 451
Summary.................................................................................................................................. 454
Table of Contents

xiii
Chapter 13: High Availability and Disaster Recovery Concepts........................... 457
Availability Concepts................................................................................................................. 458
Level of Availability.............................................................................................................. 458
Recovery Point Objective and Recovery Time Objective...................................................... 461
Cost of Downtime................................................................................................................ 462
Classification of Standby Servers........................................................................................ 463
High Availability and Recovery Technologies............................................................................ 464
AlwaysOn Failover Clustering.............................................................................................. 464
AlwaysOn Availability Groups.............................................................................................. 471
Log Shipping........................................................................................................................ 477
Combining Technologies...................................................................................................... 480
Summary.................................................................................................................................. 483
Chapter 14: Implementing AlwaysOn Availability Groups.................................... 485
Implementing AlwaysOn Availability Groups............................................................................. 486
Configuring SQL Server....................................................................................................... 492
Creating the Availability Group............................................................................................ 494
Availability Groups on Linux...................................................................................................... 508
Distributed Availability Groups.................................................................................................. 515
Managing AlwaysOn Availability Groups................................................................................... 518
Failover................................................................................................................................ 518
Synchronizing Uncontained Objects.................................................................................... 524
Monitoring........................................................................................................................... 525
Other Administrative Considerations................................................................................... 528
Summary.................................................................................................................................. 530
Chapter 15: Implementing Log Shipping.............................................................. 533
Implementing Log Shipping for DR........................................................................................... 533
GUI Configuration................................................................................................................. 536
T-SQL Configuration............................................................................................................. 546
Table of Contents

xiv
Log Shipping Maintenance....................................................................................................... 557
Failing Over Log Shipping.................................................................................................... 557
Switching Roles................................................................................................................... 558
Monitoring........................................................................................................................... 564
Summary.................................................................................................................................. 566
Chapter 16: Scaling Workloads............................................................................ 569
Database Snapshots................................................................................................................. 569
Implementing Database Snapshots..................................................................................... 572
Recovering Data from a Snapshot....................................................................................... 576
Replication................................................................................................................................ 577
Replication Concepts........................................................................................................... 577
Types of Replication............................................................................................................ 579
Implementing Transactional Replication.............................................................................. 582
Adding AlwaysOn Readable Secondary Replicas...................................................................... 607
Benefits and Considerations................................................................................................ 608
Implementing Readable Secondaries.................................................................................. 608
Summary.................................................................................................................................. 618
Part IV: Performance and Maintenance......................................................621
Chapter 17: SQL Server Metadata......................................................................... 623
Introducing Metadata Objects................................................................................................... 623
Server-Level and Instance-Level Metadata.............................................................................. 626
Exposing Registry Values.................................................................................................... 627
Exposing Service Details..................................................................................................... 628
Analyzing Buffer Cache Usage............................................................................................ 629
Metadata for Capacity Planning................................................................................................ 631
Exposing File Stats.............................................................................................................. 631
Using File Stats for Capacity Analysis................................................................................. 635
Metadata for Troubleshooting and Performance Tuning........................................................... 639
Retrieving Perfmon Counters.............................................................................................. 639
Analyzing Waits................................................................................................................... 644
Table of Contents

xv
Database Metadata................................................................................................................... 647
Metadata-Driven Automation.................................................................................................... 652
Dynamically Cycling Database Snapshots........................................................................... 652
Rebuilding Only Fragmented Indexes.................................................................................. 655
Summary.................................................................................................................................. 657
Chapter 18: Locking and Blocking........................................................................ 659
Understanding Locking............................................................................................................. 659
Lock Granularity.................................................................................................................. 659
Locking Behaviors for Online Maintenance......................................................................... 661
Lock Compatibility............................................................................................................... 666
Lock Partitioning.................................................................................................................. 667
Understanding Deadlocks......................................................................................................... 668
How Deadlocks Occur......................................................................................................... 668
Minimizing Deadlocks......................................................................................................... 670
Understanding Transactions...................................................................................................... 670
Transactional Properties...................................................................................................... 671
Transaction with In-Memory OLTP............................................................................................ 681
Isolation Levels.................................................................................................................... 682
Cross-Container Transactions.............................................................................................. 684
Retry Logic.......................................................................................................................... 686
Observing Transactions, Locks, and Deadlocks........................................................................ 687
Observing Transactions....................................................................................................... 687
Observing Locks and Contention......................................................................................... 691
Observing Deadlocks........................................................................................................... 695
Summary.................................................................................................................................. 696
Chapter 19: Extended Events................................................................................ 699
Extended Events Concepts........................................................................................................ 699
Packages............................................................................................................................. 700
Events.................................................................................................................................. 700
Targets................................................................................................................................. 701
Table of Contents

xvi
Actions................................................................................................................................. 702
Predicates............................................................................................................................ 702
Types and Maps................................................................................................................... 702
Sessions.............................................................................................................................. 704
Creating an Event Session........................................................................................................ 704
Using the New Session Dialog Box...................................................................................... 707
Using T-SQL......................................................................................................................... 713
Viewing the Collected Data....................................................................................................... 717
Analyzing Data with Data Viewer......................................................................................... 717
Analyzing Data with T-SQL.................................................................................................. 723
Correlating Extended Events with  Operating System Data...................................................... 727
Correlating Events with Perfmon Data................................................................................ 727
Integrating Event Sessions with Operating System–Level Events...................................... 729
Summary.................................................................................................................................. 736
Chapter 20: Query Store....................................................................................... 739
Enabling and Configuring Query Store...................................................................................... 739
Working with Query Store Data................................................................................................. 746
SSMS Reports...................................................................................................................... 747
Query Store T-SQL Objects.................................................................................................. 751
Resolving Issues with Query Store........................................................................................... 756
Summary.................................................................................................................................. 757
Chapter 21: Distributed Replay............................................................................. 759
Distributed Replay Concepts..................................................................................................... 760
Distributed Replay Components.......................................................................................... 760
Distributed Replay Architecture........................................................................................... 761
Configuring the Environment.................................................................................................... 761
Configuring the Controller................................................................................................... 762
Configuring Clients.............................................................................................................. 763
Configuring the Replay........................................................................................................ 765
Table of Contents

xvii
Working with Distributed Replay............................................................................................... 768
Synchronizing the Target..................................................................................................... 772
Creating a Trace................................................................................................................... 773
Replaying the Trace............................................................................................................. 803
Summary.................................................................................................................................. 807
Chapter 22: Automating Maintenance Routines................................................... 809
SQL Server Agent...................................................................................................................... 809
SQL Server Agent Concepts................................................................................................. 810
SQL Server Agent Security.................................................................................................. 814
Creating SQL Server Agent Jobs.......................................................................................... 817
Monitoring and Managing Jobs........................................................................................... 842
Creating Alerts..................................................................................................................... 845
Multiserver Jobs....................................................................................................................... 848
Configuring the MSX and TSX Servers................................................................................ 848
Creating Master Jobs.......................................................................................................... 854
Managing Target Servers..................................................................................................... 856
Summary.................................................................................................................................. 860
Chapter 23: Policy-Based Management................................................................ 863
PBM Concepts........................................................................................................................... 863
Facets.................................................................................................................................. 863
Conditions............................................................................................................................ 864
Targets................................................................................................................................. 864
Policies................................................................................................................................ 865
Evaluation Modes................................................................................................................ 865
Central Management Servers................................................................................................... 866
Creating Policies....................................................................................................................... 874
Creating Simple Policies...................................................................................................... 874
Creating an Advanced Policy............................................................................................... 883
Table of Contents

xviii
Managing Policies..................................................................................................................... 885
Importing and Exporting Policies......................................................................................... 886
Enterprise Management with Policies................................................................................. 887
Evaluating Policies with PowerShell................................................................................... 889
Summary.................................................................................................................................. 890
Chapter 24: Resource Governor............................................................................ 893
Resource Governor Concepts.................................................................................................... 893
Resource Pool...................................................................................................................... 893
Workload Group................................................................................................................... 895
Classifier Function............................................................................................................... 895
Implementing Resource Governor............................................................................................. 896
Creating Resource Pools..................................................................................................... 896
Creating Workload Groups................................................................................................... 900
Creating a Classifier Function............................................................................................. 903
Testing the Classifier Function............................................................................................ 908
Monitoring Resource Governor................................................................................................. 909
Monitoring with Performance Monitor................................................................................ 909
Monitoring with DMVs......................................................................................................... 913
Summary.................................................................................................................................. 921
Index..................................................................................................................... 923
Table of Contents