Table of Contents
Errata, updates, & book support
Chapter 1 Getting started with SQL Server tools
Installing SQL Server by using the Installation Center
Planning before an upgrade or installation
Installing or upgrading SQL Server
Tools and services installed with the SQL Server Database Engine
SQL Server Configuration Manager
Performance and reliability monitoring tools
Database Engine Tuning Advisor
Report Services Configuration Manager
SQL Server Management Studio (SSMS)
Installing SQL Server Management Studio
Upgrading SQL Server Management Studio
Features of SQL Server Management Studio
Additional tools in SQL Server Management Studio
Highlighted features in Azure Data Studio
Notebooks in Azure Data Studio
SQL Server Integration Services
A note on discontinued and deprecated features
Chapter 2 Introducing database server components
Caching data in the buffer pool
Caching plans in the procedure cache
Disable power saving everywhere
Connecting to SQL Server over the network
Added complexity with Virtual Local Area Networks
The versatility of log shipping
Read-scale availability groups
Distributed availability groups
Integrated authentication and Active Directory
Understanding virtualization and containers
Resource provisioning for virtual consumers
When processors are no longer processors
Chapter 3 Designing and implementing an on-premises database infrastructure
Introduction to SQL Server database architecture
Grouping data pages with extents
Contents and types of data pages
Verifying data pages by using a checksum
Recording changes in the transaction log
Flushing data to the storage subsystem with checkpoints
Inside the transaction log file
A faster recovery with Accelerated Database Recovery
Managing the temporary database
Managing system usage with Resource Governor
Configuring the operating system page file
Taking advantage of logical processors by using parallelism
Allocating CPU cores with an affinity mask
Container orchestration with Kubernetes
Kubernetes support for SQL Server
Deploying SQL Server in containers
Getting started with SQL Server on Kubernetes
Deploying SQL Server on Kubernetes
Chapter 4 Installing and configuring SQL Server instances and features
What to do before installing SQL Server
Important SQL Server volume settings
Planning for multiple SQL Server instances
Creating an Azure VM running SQL Server
Installing SQL Server on Windows
Automating SQL Server Setup by using configuration files
Post-installation server configuration
Post-installation configuration of other features
SSISDB initial configuration and setup
SQL Server Reporting Services initial configuration and setup
SQL Server Analysis Services initial configuration and setup
Chapter 5 Installing and configuring SQL Server on Linux
Differences between Windows and Linux
Linux distributions supported by SQL Server
Considerations for installing SQL Server on Linux
Installing SQL Server on Linux
Downloading and installing packages
Configuring SQL Server for Linux
Using mssql-conf to set up and configure SQL Server
Caveats of SQL Server on Linux
Missing SQL Server features on Linux
Chapter 6 Provisioning and configuring SQL Server databases
Adding databases to a SQL Server instance
Upgrading database compatibility levels
Other Considerations for migrating databases
Database properties and options
Moving user and system databases
Database actions: offline versus detach versus drop
Chapter 7 Understanding table features
User-defined data types and user-defined types
System-versioned temporal tables
Horizontally partitioned tables and indexes
Capturing modifications to data
Querying change tracking and change data capture
Comparing change tracking, change data capture, and temporal tables
Part III SQL Server management
Chapter 8 Maintaining and monitoring SQL Server
Detecting, preventing, and responding to database corruption
Setting the database’s page verify option
Repairing database data file corruption
Recovering from database transaction log file corruption
Database corruption in databases in Azure SQL Database
Maintaining indexes and statistics
Changing the Fill Factor property when beneficial
Monitoring index fragmentation
Understanding and finding autogrowth events
Monitoring activity by using DMOs
Observing sessions and requests
Understanding wait types and wait statistics
Monitoring with the SQL Assessment API
Using extended events to capture deadlocks
Using extended events to detect autogrowth events
Using extended events to detect page splits
Capturing performance metrics with DMOs and Data Collectors
Querying performance metrics by using DMVs
Capturing performance metrics by using Performance Monitor
Monitoring key performance metrics
Monitoring key performance metrics in Linux
Monitoring key performance metrics in Azure Data Portal
Protecting important workloads by using Resource Governor
Configuring the Resource Governor classifier function
Configuring Resource Governor pools and groups
Monitoring Resource Pools and Workload Groups
Understanding the new servicing model
Chapter 9 Automating SQL Server administration
Components of SQL Server automated administration
Basic “care and feeding” of SQL Server
Using SQL Server Maintenance Plans
Covering databases with the Maintenance Plan
Maintenance Plan report options
Building Maintenance Plans using the designer in SSMS
Backing up availability groups using a secondary replica
Strategies for administering multiple SQL Servers
Master and Target servers for SQL Agent jobs
SQL Server Agent event forwarding
Using PowerShell to automate SQL Server administration
Installing the PowerShell SQLServer module
Using PowerShell with SQL Server
Using PowerShell with availability groups
Chapter 10 Developing, deploying, and managing data recovery
Defining acceptable data loss: RPO
Defining acceptable downtime: RTO
Establishing and using a run book
Understanding different types of backups
An overview of SQL Server recovery models
Additional backup options and considerations
Availability and security of backup media
Creating and verifying backups
Restoring a database using a full backup
Restoring a database with differential and log backups
Restoring a database to a point in time
Restoring a database piecemeal
A sample recovery strategy for our DR scenario
Recovery strategies for hybrid and cloud environments
Chapter 11 Implementing high availability and disaster recovery
Overview of high availability and disaster recovery technologies in SQL Server
Comparing HA and DR technologies
Understanding types of replication
Understanding the capabilities of failover clustering
Understanding the capabilities of availability groups
Configuring failover cluster instances
Designing availability groups solutions
Comparing different cluster types
Creating WSFC for use with availability groups
Understanding the database mirroring endpoint
Improvements to availability groups recently
Choosing the correct secondary replica availability mode
Understanding the impact of secondary replicas on performance
Understanding failovers in availability groups
Seeding options when adding replicas
Additional actions after creating an availability group
Reading secondary database copies
Implementing a hybrid availability group topology
Configuring availability groups in SQL Server on Linux
Understanding the differences between Windows and Linux clustering
Setting up an availability group in SQL Server on Linux
Administering availability groups
Analyzing DMVs for availability groups
Analyzing wait types for availability groups
Analyzing extended events for availability groups
Alerting for availability groups
Chapter 12 Administering security and permissions
Azure Active Directory universal authentication
Azure Active Directory password authentication
Azure Active Directory integrated authentication
Advanced types of server principals
Authentication to SQL Server on Linux
Contained database authentication
Understanding the basics of privileges
Configuring login server principals
Configuring database principals
Understanding permissions for Data Definition Language and Data Manipulation Language
Understanding overlapping permissions
Common security administration tasks
Moving SQL Server logins and permissions
Saving the day with the Dedicated Administrator Connection
Chapter 13 Protecting data through encryption, privacy and auditing
General Data Protection Regulation (GDPR)
Introducing security principles and protocols
Securing your environment with defense in depth
The difference between hashing and encryption
A primer on protocols and transmitting data
Symmetric and asymmetric encryption
The encryption hierarchy in detail
Using EKM modules with SQL Server
Master keys in the encryption hierarchy
Protecting sensitive columns with Always Encrypted
Auditing with SQL Server and Azure SQL Database
Auditing with Azure SQL Database
Securing Azure infrastructure as a service
User-defined routes and IP forwarding
Additional security features in Azure networking
Chapter 14 Performance tuning SQL Server
Understanding isolation levels and concurrency
Understanding how concurrent sessions become blocked
Understanding and handling common concurrency scenarios
Understanding the enterprise solution to concurrency: row version-based concurrency
Understanding on-disk versus memory-optimized concurrency
Understanding durability settings for performance
Delayed durability database options
Understanding how SQL Server executes your query
Understanding the overall query execution process
Retrieving execution plans in SQL Server Management Studio
Understanding parameterization and “parameter sniffing”
Understanding the Procedure Cache
Understanding advanced engine features for tuning queries
Chapter 15 Understanding and designing indexes
Choosing a proper rowstore clustered index key
The case against intentionally designing heaps
Understanding the OPTIMIZE_FOR_SEQUENTIAL_KEY feature
Designing rowstore nonclustered indexes
Understanding nonclustered index design
Creating filtered nonclustered indexes
Understanding the “missing” indexes feature
Understanding and proving index usage
Understanding columnstore indexes
Understanding the deltastore of columnstore indexes
Demonstrating the power of columnstore indexes
Understanding indexing in memory-optimized tables
Understanding hash indexes for memory-optimized tables
Understanding nonclustered indexes for memory-optimized tables
Understanding index statistics
Automatically creating and updating statistics
Manually creating statistics for on-disk tables
Understanding statistics on memory-optimized tables
Understanding statistics on external tables
Understanding other types of indexes
Understanding full-text indexes
Chapter 16 Designing and implementing hybrid and Azure database infrastructure
Cloud computing and Microsoft Azure
Managing Azure: The Azure portal and PowerShell Core
Chapter 17 Provisioning Azure SQL Database
Provisioning a logical SQL server
Creating a server using the Azure portal
Creating a server by using PowerShell Core
Establishing a connection to your server
Provisioning a database in Azure SQL Database
Creating a database using the Azure portal
Creating a database by using PowerShell Core
Creating a database by using Azure CLI
Creating a database by using T-SQL
Security in Azure SQL Database
Security features shared with SQL Server 2019
Server and database-level firewall
Integration with virtual networks
Preparing Azure SQL Database for disaster recovery
Understanding default disaster recovery features
Manually backing up a database
Enabling zone redundant configuration
Using Azure Backup for long-term backup retention
Chapter 18 Provisioning Azure SQL Database managed instance
Differences between SQL Server on-premises and in a Managed Instance
Selecting a pricing tier and service objective
Provisioning a managed instance from the portal
Provisioning a managed instance using PowerShell
Creating the endpoints via the portal
Creating the VPN Gateway via PowerShell
Connect from an on-premises computer
Connect from a Virtual Machine (VM)
Migrating data to Managed Instance
Network requirements for Managed Instances
Managed Instance administration features
Azure Active Directory (Azure AD)
Chapter 19 Migrating to SQL Server solutions in Azure
Database Experimentation Assistant (DEA)
Azure Data Migration Assistant (DMA)
Specifics for Linux migrations
Features of Data Migration Assistant
SQL Server Migration Assistant
Common causes for migration failures
Open Source PowerShell migration option
Integration Runtime options for Migration Services
Best Practices for security and resiliency during migration
Part VII Big Data and Machine Learning
Chapter 20 Leveraging big data and machine learning
Introducing MapReduce with Hadoop and HDFS
Introducing distributed memory with Spark
Big Data Clusters technical overview
Unified data platform features
Operationalizing analytics with Machine Learning Server