Pro SQL Server 2022 Administration: A guide for the modern DBA

Table of Contents
About the Author
About the Technical Reviewer
Acknowledgments
Part I: Installation and Configuration
	Chapter 1: Planning the Deployment
		Editions and License Models
		Hardware Considerations
			Specifying Strategic Minimum Requirements
			Storage
				Locally Attached Storage
					RAID 0
					RAID 1
					RAID 5
					RAID 10
				File Placement
					Solid-State Drives (SSDs)
				Working with a SAN
				Disk Block Size
				Cloud Storage
		Operating Systems Considerations
			Configuring the Operating System
				Setting the Power Plan
				Optimizing for Background Services
				Assigning User Rights
					Initializing the Instant File
					Locking Pages in Memory
					SQL Audit to the Event Log
		Selecting Features
			Instance Features
				Database Engine Service
				Analysis Services
			Shared Features
				Data Quality Client
				Integration Services
				SQL Client Connectivity SDK
				Master Data Services
				SQL Server Extension for Azure
		Summary
	Chapter 2: GUI Installation
		Obtaining SQL Server Media
		Installation Center
			The Planning Tab
			The Installation Tab
			The Maintenance Tab
			The Tools Tab
			The Resources Tab
			The Advanced Tab
			The Options Tab
		Installing a Stand-Alone Database Engine Instance
			Preparation Steps
			The Feature Selection Page
			The Instance Configuration Page
			Selecting Service Accounts
			Choosing the Collation
			Provisioning Instance Security
			Configuring the Instance
			Configuring SQL Server Extension for Azure
			Completing the Installation
		Summary
	Chapter 3: Server Core Installation
		Installing an Instance
			Required Parameters
				IACCEPTSQLSERVERLICENSETERMS Switch
				Additional License Terms Parameters
				ACTION Parameter
				FEATURES Parameter
			Role Parameter
			Basic Installation
			Smoke Tests
			Troubleshooting the Installation
				Summary.txt
				Detail.txt
				SystemConfigurationCheck_Report.htm
				Other Log Files
			Optional Parameters
			Product Update
		Using a Config File
		Automatic Installation Routines
			Enhancing the Installation Routine
			Production Readiness
		Summary
	Chapter 4: Installation on Heterogeneous Operating Systems
		Installing SQL Server on Linux
			Installing SQL Server Manually
			Configuring SQL Server
			Unattended Installation
		Installing and Managing SQL Server in a Docker Container
			Running a Microsoft-Supplied Docker Image
			Working with Containers
		Creating Custom SQL Server Containers
			Creating a Simple Docker Image for SQL Server
			Create a Configurable Docker Image for SQL Server
		Summary
	Chapter 5: Configuring the Instance
		Instance Configuration
			Using sp_configure
			Processor and Memory Configuration
				Processor Affinity
				MAXDOP
				Min and Max Server Memory
			Trace Flags
				Trace Flag 3042
				Trace Flag 3226
				Trace Flag 3625
		Ports and Firewalls
			Process of Communication
			Ports Required by SQL Server
			Configuring the Port That the Instance Will Listen On
		System Databases
			mssqlsystemresource (Resource)
			MSDB
			Master
			Model
			TempDB
			Optimizing TempDB
		Buffer Pool Extension
		Hybrid Buffer Pool
		Summary
Part II: Database Administration
	Chapter 6: Database Configuration
		Data Storage
			Filegroups
			FILESTREAM Filegroups
			Memory-Optimized Filegroups
			Strategies for Structured Filegroups
				Performance Strategies
				Backup and Restore Strategies
				Storage-Tiering Strategies
			Strategies for Memory-Optimized Filegroups
		File and Filegroup Maintenance
			Adding Files
			Expanding Files
			Shrinking Files
		Database Scoped Configurations
		Log Maintenance
			Recovery Model
			Log File Count
			Shrinking the Log
			Log Fragmentation
		Summary
	Chapter 7: Table Optimizations
		Table Partitioning
			Partitioning Concepts
				Partitioning Key
				Partition Function
				Partition Scheme
				Index Alignment
				Partitioning Hierarchy
			Implementing Partitioning
				Creating the Partitioning Objects
				Creating a New Partitioned Table
				Partitioning an Existing Table
			Monitoring Partitioned Tables
				$PARTITION Function
			Sliding Windows
			Partition Elimination
		Table Compression
			Row Compression
			Page Compression
				Prefix Compression
				Dictionary Compression
				Page Compression Structure
			Columnstore Compression
			XML Compression
			Implementing Compression
				Selecting the Compression Level
				Compressing Tables and Partitions
				Data Compression Wizard
				Maintaining Compression on Heaps
				Maintaining Compressed Partitions
		Memory-Optimized Tables
			Durability
			Creating and Managing Memory-Optimized Tables
			Performance Profile
			Table Memory Optimization Advisor
			Natively Compiled Objects
				Natively Compiled Tables
				Natively Compiled Stored Procedures
		Summary
	Chapter 8: Indexes and Statistics
		Clustered Indexes
			Tables Without a Clustered Index
			Tables with a Clustered Index
			Clustering the Primary Key
			Administering Clustered Indexes
		Nonclustered Indexes
			Covering Indexes
			Administering Nonclustered Indexes
			Filtered Indexes
		Indexes for Specialized Application
			Columnstore Indexes
			Clustered Columnstore Indexes
			Nonclustered Columnstore Indexes
			In-Memory Indexes
				In-Memory Nonclustered Hash Indexes
				In-Memory Nonclustered Indexes
		Maintaining Indexes
			Missing Indexes
			Index Fragmentation
				Detecting Fragmentation
				Removing Fragmentation
			Resumable Index Operations
			Partitioned Indexes
		Statistics
			Filtered Statistics
			Incremental Statistics
			Managing Statistics
			Managing Cardinality
		Summary
	Chapter 9: Database Consistency
		Consistency Errors
			Understand Consistency Errors
				605 Error
				823 Error
				824 Error
				5180 Error
				7105 Error
			Detecting Consistency Errors
				Page Verify Option
				Suspect Pages
				Consistency Issues for Memory-Optimized Tables
			System Database Corruption
				Corruption of the Master Database
				Corruption of the Resource Database or Binaries
		DBCC CHECKDB
			Checking for Errors
			Fixing Errors
			Emergency Mode
			Other DBCC Commands for Corruption
				DBCC CHECKCATALOG
				DBCC CHECKALLOC
				DBCC CHECKTABLE
				DBCC CHECKFILEGROUP
				DBCC CHECKIDENT
				DBCC CHECKCONSTRAINTS
		Consistency Checks on VLDBs
			DBCC CHECKDB with PHYSICAL_ONLY
			Backing Up WITH CHECKSUM and DBCC CHECKALLOC
			Splitting the Workload
			Offloading to a Secondary Server
		Summary
Part III: Security, Resilience, and Scaling Workloads
	Chapter 10: SQL Server Security Model
		Security Hierarchy
			Implementing Instance-Level Security
				Server Roles
				Logins
				Granting Permissions
		Implementing Database-Level Security
			Database Roles
			Schemas
			Creating and Managing Contained Users
		Implementing Object-Level Security
		Summary
	Chapter 11: Auditing and Ledger
		Ledger
			Append-Only Ledger Tables
			Ledger Database
			Database Ledger and Digest Management
		Server Audit
			Creating a Server Audit
			Creating a Server Audit Specification
			Enabling and Invoking Audits
			Updateable Ledger Tables
			Database Audit Specifications
			Auditing the Audit
		Summary
	Chapter 12: Encryption
		Encryption Hierarchy
			Encryption Concepts
				Symmetric Keys
				Asymmetric Keys
				Certificates
				Windows Data Protection API
			SQL Server Encryption Concepts
				Master Keys
				Hierarchy
		Transparent Data Encryption
			Implementing TDE
			Managing TDE
			Migrating an Encrypted Database
		Managing Cell-Level Encryption
			Accessing Encrypted Data
		Always Encrypted
			Implementing Always Encrypted
			Administering Keys
		Summary
	Chapter 13: Backups and Restores
		Backup Fundamentals
			Recovery Models
				SIMPLE Recovery Model
				FULL Recovery Model
				BULK LOGGED Recovery Model
			Changing the Recovery Model
			Backup Types
				Full Backup
				Differential Backup
				Log Backup
			Backup Media
				Backup Device
				Media Sets
				Backup Sets
		Backup Strategies
			Full Backup Only
			Full and Transaction Log Backups
			Full, Differential, and Transaction Log Backups
			Filegroup Backups
			Partial Backup
		Backing Up a Database
			Backing Up in SQL Server Management Studio
			Backing Up via T-SQL
		Restoring a Database
			Restoring in SQL Server Management Studio
			Restoring via T-SQL
		Restoring to a Point in Time
		Restoring Files and Pages
			Restoring a File
			Restoring a Page
		Piecemeal Restores
		Accelerated Database Recovery
		Summary
	Chapter 14: High Availability and Disaster Recovery Concepts
		Availability Concepts
			Level of Availability
				Service-Level Agreements and Service-Level Objectives
				Proactive Maintenance
			Recovery Point Objective and Recovery Time Objective
			Cost of Downtime
			Classification of Standby Servers
		High Availability and Recovery Technologies
			AlwaysOn Failover Clustering
				Active/Active Configuration
				Three-Plus Node Configurations
				Quorum
			AlwaysOn Availability Groups
				Automatic Page Repair
			Log Shipping
				Recovery Modes
				Remote Monitor Server
				Failover
			Combining Technologies
		Summary
	Chapter 15: Implementing AlwaysOn Availability Groups
		Implementing AlwaysOn Availability Groups
			Configuring SQL Server
			Creating the Availability Group
				Using the New Availability Group Wizard
				Using the New Availability Group Dialog Box
		Availability Groups On Linux
		Contained Availability Group
		Distributed Availability Groups
		Managing AlwaysOn Availability Groups
			Failover
				Synchronous Failover
				Asynchronous Failover
			Synchronizing Uncontained Objects
			Monitoring
				AlwaysOn Dashboard
				AlwaysOn Health Trace
			Other Administrative Considerations
		Summary
	Chapter 16: Implementing Log Shipping
		Implementing Log Shipping for DR
			GUI Configuration
			T-SQL Configuration
		Log Shipping Maintenance
			Failing Over Log Shipping
			Switching Roles
			Monitoring
		Summary
	Chapter 17: Scaling Workloads
		Database Snapshots
			Implementing Database Snapshots
			Recovering Data from a Snapshot
		Replication
			Replication Concepts
			Types of Replication
				Snapshot
				Transactional
				Merge
			Implementing Transactional Replication
				Implementing the Distributor
				Implementing the Publication
				Implementing the Subscriber
				Modifying the PAL
		AlwaysOn Readable Secondary Replicas
			Benefits and Considerations
			Implementing Readable Secondaries
		Summary
Part IV: Performance and Maintenance
	Chapter 18: SQL Server Metadata
		Introducing Metadata Objects
		Server-Level and Instance-Level Metadata
			Exposing Registry Values
			Exposing Service Details
			Analyzing Buffer Cache Usage
		Metadata for Capacity Planning
			Exposing File Stats
			Using File Stats for Capacity Analysis
		Metadata for Troubleshooting and Performance Tuning
			Retrieving Perfmon Counters
			Analyzing Waits
		Database Metadata
		Metadata-Driven Automation
			Dynamically Cycling Database Snapshots
			Rebuilding Only Fragmented Indexes
		Summary
	Chapter 19: Locking and Blocking
		Understanding Locking
			Lock Granularity
			Locking Behaviors for Online Maintenance
			Lock Compatibility
			Lock Partitioning
		Understanding Deadlocks
			How Deadlocks Occur
			Minimizing Deadlocks
		Understanding Transactions
			Transactional Properties
				Atomic
				Consistent
				Isolated
					Transactional Anomalies
						Dirty Reads
						Nonrepeatable Read
						Phantom Read
					Isolation Levels
						Pessimistic Isolation Levels
						Optimistic Isolation Levels
				Durable
		Transaction with In-Memory OLTP
			Isolation Levels
				Read Committed
				Read Committed Snapshot
				Snapshot
				Repeatable Read
				Serializable
			Cross-Container Transactions
			Retry Logic
		Observing Transactions, Locks, and Deadlocks
			Observing Transactions
			Observing Locks and Contention
			Observing Deadlocks
		Summary
	Chapter 20: Extended Events
		Extended Events Concepts
			Packages
			Events
			Targets
			Actions
			Predicates
			Types and Maps
			Sessions
		Creating an Event Session
			Using the New Session Dialog Box
			Using T-SQL
		Viewing the Collected Data
			Analyzing Data with Data Viewer
			Analyzing Data with T-SQL
		Correlating Extended Events with Operating System Data
			Correlating Events with Perfmon Data
			Integrating Event Sessions with Operating System–Level Events
		Summary
	Chapter 21: Monitoring and Managing a Hybrid Environment
		Hybrid Management
			Environmental Health and SQL Best Practice Assessment
			Microsoft Defender
		Summary
	Chapter 22: Query Store
		Enabling and Configuring Query Store
		Working with Query Store Data
			Query Store Reports
			Query Store T-SQL Objects
		Resolving Issues with Query Store
		Query Store Hints
		Summary
	Chapter 23: Automating Maintenance Routines
		SQL Server Agent
			SQL Server Agent Concepts
				Schedules
				Operators
				Jobs
				Alerts
			SQL Server Agent Security
				SQL Server Agent Database Roles
				SQL Server Agent Proxy Accounts
			Creating SQL Server Agent Jobs
				Creating a Simple SQL Server Agent Job
				Creating a Complex SQL Server Agent Job
					Creating the Credential
					Creating the Proxy
					Creating the Schedule
					Configuring Database Mail
					Creating the Operator
					Creating the Job
			Monitoring and Managing Jobs
				Executing Jobs
				Viewing Job History
			Creating Alerts
		Multiserver Jobs
			Configuring the MSX and TSX Servers
			Creating Master Jobs
			Managing Target Servers
		Summary
	Chapter 24: Policy-Based Management
		PBM Concepts
			Facets
			Conditions
			Targets
			Policies
			Evaluation Modes
		Central Management Servers
		Creating Policies
			Creating Simple Policies
				Creating a Policy That You Can Manually Evaluate
				Manually Evaluating a Policy
				Creating a Policy That Prevents Unwanted Activity
			Creating an Advanced Policy
		Managing Policies
			Importing and Exporting Policies
			Enterprise Management with Policies
			Evaluating Policies with PowerShell
		Summary
	Chapter 25: Resource Governor
		Resource Governor Concepts
			Resource Pool
			Workload Group
			Classifier Function
		Implementing Resource Governor
			Creating Resource Pools
			Creating Workload Groups
			Creating a Classifier Function
			Testing the Classifier Function
		Monitoring Resource Governor
			Monitoring with Performance Monitor
			Monitoring with DMVs
		Summary
Index