Pro SQL Server 2019 Administration: A Guide for the Modern DBA Paperback

Table of Contents About the Author About the Technical Reviewer Acknowledgments Introduction 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 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 Database Engine Service Analysis Services Machine Learning Server Data Quality Client Client Connectivity Tools Integration Services Client Tools Backward Compatibility Client Tools SDK Distributed Replay Controller Distributed Replay Client SQL Client Connectivity SDK Master Data Services Summary Chapter 2: GUI Installation 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 Distributed Replay Completing the Installation Summary Chapter 3: Server Core Installation Installing an Instance Required Parameters IACCEPTSQLSERVERLICENSETERMS Switch 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 SQL Server in a Docker Container Running a Microsoft-Supplied Docker Image Creating a Simple Docker Image for SQL Server Creating a Configurable Docker Image for SQL Server Kubernetes Support 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 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 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 Server Audit Creating a Server Audit Creating a Server Audit Specification Enabling and Invoking Audits Database Audit Specifications Auditing the Audit Security Reports SQL Data Discovery and Classification Vulnerability Assessment Summary Chapter 11: 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 12: 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 Summary Chapter 13: 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 14: 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 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 15: 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 16: 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 Adding AlwaysOn Readable Secondary Replicas Benefits and Considerations Implementing Readable Secondaries Summary Part IV: Performance and Maintenance Chapter 17: 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 18: 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 19: 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 20: Query Store Enabling and Configuring Query Store Working with Query Store Data SSMS Reports Query Store T-SQL Objects Resolving Issues with Query Store Summary Chapter 21: Distributed Replay Distributed Replay Concepts Distributed Replay Components Distributed Replay Architecture Configuring the Environment Configuring the Controller Configuring Clients Configuring the Replay Working with Distributed Replay Synchronizing the Target Creating a Trace Replaying the Trace Converting the Trace File Preprocessing the Trace Data Starting the Replay Summary Chapter 22: 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 23: 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 24: 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 Use this comprehensive guide for the SQL Server DBA, covering all that practicing database administrators need to know to get their daily work done. Updated for SQL Server 2019, this edition includes coverage of new features such as Memory-optimized TempDB Metadata, and Always Encrypted with Secure Enclaves. Other new content includes coverage of Query Store, resumable index operations, installation on Linux, and containerized SQL. Pro SQL Server 2019 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 is secure and highly available. Finally, readers will learn how to perform advanced maintenance and tuning techniques. This book teaches you to make the most of new SQL Server 2019 functionality, including Data Discovery and Classification. The book promotes best-practice installation, shows how to configure for scalability and high workloads, and demonstrates the gamut of database-level maintenance tasks such as index maintenance, database consistency checks, and table optimizations. What You Will Learn Install and configure SQL Server on Windows through the GUI and with PowerShell Install and configure SQL Server on Linux and in Containers Optimize tables through in-memory OLTP, table partitioning, and the creation of indexes Secure and encrypt data to protect against embarrassing data breaches Ensure 24x7x365 access through high-availability and disaster recovery features Back up your data to ensure against loss, and recover data when needed Perform routine maintenance tasks such as database consistency checks Troubleshoot and solve performance problems in SQL queries and 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 Query Store, Extended Events, Distributed Replay, and Policy-Based Management, or those who need to install SQL Server in a variety of environments.