Part I: Introduction
Chapter 1: Get started with SQL Server tools
SQL Server setup
Install SQL Server with the Installation Center
Plan before an upgrade or installation
Install or upgrade SQL Server
Tools and services installed with the Database Engine
Machine Learning Services
Data Quality Services
Command line interface
SQL Server Configuration Manager
Performance and reliability monitoring tools
Database Engine Tuning Advisor
Extended Events
Management Data Warehouse
SQL Server Reporting Services (SSRS)
Installation
Report Server Configuration Manager
SQL Server Management Studio (SSMS)
Releases and versions
Install SQL Server Management Studio
Upgrade SQL Server Management Studio
Features of SQL Server Management Studio
Additional tools in SQL Server Management Studio
Error logs
Activity Monitor
SQL Server Agent
Azure Data Studio
User interface
Highlighted features in Azure Data Studio
Notebooks in Azure Data Studio
SQL Server Data Tools
SQL Server Integration Services
SQL Server on Azure Arc–enabled servers
Microsoft Purview
Discontinued and deprecated features
Chapter 2: Introduction to database server components
Memory
Understand the working set
Cache data in the buffer pool
Cached plans in the procedure cache
Lock pages in memory
Editions and memory limits
Central processing unit
Simultaneous multithreading
Non-uniform memory access
Disable power saving everywhere
Data storage
Types of storage
Configure the storage layer
Connect to SQL Server over the network
Protocols and ports
Added complexity with Virtual Local Area Networks
High-availability concepts
Why redundancy matters
Disaster recovery
Clustering
The versatility of log shipping
Always On availability groups
Secure SQL Server
Integrated Authentication and Active Directory
Azure Active Directory
Kerberos for Azure SQL Managed Instance
Understand virtualization and containers
Going virtual
Provision resources for virtual consumers
When processors are no longer processors
The network is virtual, too
Chapter 3: Design and implement an on-premises database infrastructure
Introduction to SQL Server database architecture
Data files and filegroups
Group data pages with extents
Contents and types of data pages
Verify data pages by using a checksum
Record changes in the transaction log
Flush data to the storage subsystem with checkpoints
Inside the transaction log file
The Minimum Recovery LSN
Types of database checkpoints
Restart with recovery
MinLSN and the active log
A faster recovery with accelerated database recovery
Partition tables
Compress data
Table and index compression
Backup compression
Manage the temporary database
Storage options for tempdb
Recommended number of files
Configuration settings
Manage system usage with Resource Governor
Configure the operating system page file
Take advantage of logical processors with parallelism
SQL Server memory settings
Allocate CPU cores with an affinity mask
File system configuration
Part II: Deployment
Chapter 4: Install and configure SQL Server instances and features
What to do before installing SQL Server
Decide on volume usage
Important SQL Server volume settings
SQL Server editions
Install a new instance
Plan for multiple SQL Server instances
Install SQL Server on Windows
Install common features
Log SQL Server Setup
Automate SQL Server Setup with configuration files
SQL Server on Azure virtual machines
Post-installation server configuration
Post-installation checklist
Post-installation configuration of other features
SSIS initial configuration and setup
SQL Server Reporting Services initial configuration and setup
SQL Server Analysis Services initial configuration and setup
Azure Synapse Link for SQL Server
Container orchestration with Kubernetes
Kubernetes support for SQL Server
Deploy SQL Server in containers
Get started with SQL Server on Kubernetes
Deploy SQL Server on Kubernetes
Review cluster health
Chapter 5: Install and configure SQL Server on Linux
What is Linux?
Differences between Windows and Linux
Linux distributions supported by SQL Server
Considerations for installing SQL Server on Linux
Configure OS settings
Install SQL Server on Linux
Installation requirements
Download and install packages
Configure SQL Server on Linux
Use mssql-conf to set up and configure SQL Server
Caveats of SQL Server on Linux
Missing SQL Server features on Linux
Chapter 6: Provision and configure SQL Server databases
Add databases to a SQL Server instance
Create a database
Move existing databases
Upgrade database compatibility levels
Other considerations for migrating databases
Database-scoped configurations
Database properties and options
Move and remove databases
Move user and system databases
Move databases within instances
Single-user mode
Chapter 7: Understand table features
Review table structures
General-purpose data types
Specialized data types
Data type precedence
Constraints
Sequence objects
User-defined data types and user-defined types
Sparse columns
Computed columns
Special table types
System-versioned temporal tables
Memory-optimized tables
Graph tables
Store large binary objects
Understand FILESTREAM
FileTable
Table partitions
Horizontally partitioned tables and indexes
Vertical partitions
Capture modifications to data
Use change tracking
Use change data capture
Query change tracking and change data capture
Compare change tracking, change data capture, and temporal tables
Benefits of PolyBase for external data sources and external tables
Unified data platform features
Install and configure PolyBase
More PolyBase examples, architectures including S3 and URL queries
PolyBase examples with a generic ODBC driver
Azure bulk operations examples
Part III: SQL Server management
Chapter 8: Maintain and monitor SQL Server
Detect, prevent, and respond to database corruption
Set the database’s page verify option
Repair database data file corruption
Recover from database transaction log file corruption
Database corruption in Azure SQL Database
Maintain indexes and statistics
Change the fill factor when beneficial
Monitor index fragmentation
Maintain indexes
Manage database file sizes
Understand and find autogrowth events
Shrink database files
Monitor activity with DMOs
Observe sessions and requests
Understand wait types and wait statistics
Monitor with the SQL Assessment API
Use Extended Events
View Extended Events data
Use Extended Events to capture deadlocks
Use Extended Events to detect autogrowth events
Use Extended Events to detect page splits
Secure Extended Events
Capture performance metrics with DMOs and data collectors
Query performance metrics with DMVs
Capture performance metrics with Performance Monitor
Monitor key performance metrics
Monitor key performance metrics in Linux
Monitor key performance metrics in Azure portal
Protect important workloads with Resource Governor
Configure the Resource Governor classifier function
Configure Resource Governor resource pools and workload groups
Monitor resource pools and workload groups
Understand the SQL Server servicing model
Updated servicing model
Plan for the product support life cycle
Chapter 9: Automate SQL Server administration
Foundations of SQL Server automated administration
Database Mail
SQL Server Agent
Maintain SQL Server
Basic care and feeding of SQL Server
Use SQL Server maintenance plans
Cover databases with the maintenance plan
Maintenance plan tasks
Maintenance plan report options
Build maintenance plans using the Maintenance Plan designer in SSMS
Back up availability groups using a secondary replica
Strategies for administering multiple SQL Servers
Master/Target servers for SQL Agent jobs
SQL Server Agent event forwarding
Policy-based management
Use PowerShell to automate SQL Server administration
PowerShell basics
Install the PowerShell SQLServer module
Use PowerShell with SQL Server
Use PowerShell with availability groups
Chapter 10: Develop, deploy, and manage data recovery
Prepare for data recovery
A disaster recovery scenario
Define acceptable data loss (RPO)
Define acceptable downtime (RTO)
Establish and use a runbook
Ransomware attacks
Understand different types of backups
An overview of SQL Server recovery models
Full backups
Differential backups
The backup chain
File and filegroup backups
Additional backup options and considerations
Understand backup devices
Back up to disk
Back up to URL
Backup and media sets
Back up to S3-compatible storage
Create and verify backups
Create backups
Verify backups
Restore a database
Restore a database using a full backup
Restore a database with differential and log backups
Restore a database to a point in time
Restore a database piecemeal
Define a recovery strategy
A sample recovery strategy for our DR scenario
Recovery strategies for hybrid and cloud environments
Chapter 11: Implement high availability and disaster recovery
Overview of high-availability and disaster-recovery technologies
Compare HA and DR technologies
Understand log shipping
Understand the capabilities of failover clustering
Understand the capabilities of availability groups
Configure failover cluster instances
Understand FCI quorum
Configure a SQL Server FCI
Patch a failover cluster
Design availability groups solutions
Compare different cluster types
Create WSFC for use with availability groups
Understand the database mirroring endpoint
Recent improvements to availability groups
Choose the correct secondary replica availability mode
Understand the impact of secondary replicas on performance
Understand failovers in availability groups
Seeding options when adding replicas
Additional actions after creating an availability group
Read secondary database copies
Query Store on replicas
Implement a hybrid availability group topology
Understand the Azure SQL Managed Instance link feature
Failover and failback to Azure SQL Managed Instance with database portability
Provision and scale the Azure SQL Managed Instance link feature
Failover and failback tooling and automation
Configure availability groups in SQL Server on Linux
Understand the differences between Windows and Linux clustering
Set up an availability group in SQL Server on Linux
Administer availability groups
Analyze DMVs for availability groups
Analyze wait types for availability groups
Analyze Extended Events for availability groups
Alerts for availability groups
