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