Table of Contents
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
Installing SQL Server Management Studio
Upgrading SQL Server Management Studio
Features of SQL Server Management Studio
Additional tools in SQL Server Management Studio
SQL Server Integration Services
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
Improve redundancy and performance with NIC teaming
Integrated authentication and Active Directory
Abstracting hardware with virtualization
When processors are no longer processors
Chapter 3 Designing and implementing a database infrastructure
Physical database architecture
Recording changes in the transaction log
Managing the temporary database
Managing system usage by using Resource Governor
Configuring the page file (Windows)
Taking advantage of logical processors by using parallelism
Carving up CPU cores using an affinity mask
Chapter 4 Provisioning databases
What to do before installing SQL Server
Important SQL Server volume settings
Planning for multiple SQL Server instances
Installing a SQL Server instance
Installing options and features
Installing other core features
Automating SQL Server Setup by using configuration files
Post-installation server configuration
Installing and configuring features
SSISDB initial configuration and setup
SQL Server Reporting Services initial configuration and setup
SQL Server Analysis Services initial configuration and setup
Adding databases to a SQL Server instance
Considerations for migrating existing databases
Database properties and options
Moving user and system databases
Database actions: offline versus detach versus drop
Chapter 5 Provisioning Azure SQL Database
Azure and database-as-a-service concepts
Managing Azure: The Azure portal and PowerShell
Provisioning a logical SQL server
Creating a server using the Azure portal
Creating a server by using PowerShell
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
Creating a database by using Azure CLI
Creating a database by using T-SQL
Selecting a pricing tier and service objective
Limitations of Azure SQL Database
Overcoming limitations with managed instances
Security in Azure SQL Database
Security features shared with SQL Server 2017
Server and database-level firewall
Preparing Azure SQL Database for disaster recovery
Understanding default disaster recovery features
Manually backing up a database
Using Azure Backup for long-term backup retention
Chapter 6 Administering security and permissions
Different types of authentication
Understanding Permissions for Data Definition Language and Data Manipulation Language
Granting commonly needed permissions
Ownership versus authorization
Understanding views, stored procedures, and function permissions
Using the Dedicated Administrator Connection
Moving SQL Server logins and permissions
Moving logins by using SQL Server Integration Services (SQL Server only)
Moving Windows-authenticated logins by using T-SQL (SQL Server only)
Moving SQL Server–authenticated logins by using T-SQL (SQL Server only)
Moving server roles by using T-SQL (SQL Server only)
Moving server permissions by using T-SQL (SQL Server only)
Moving Azure SQL Database logins
Other security objects to move
Alternative migration approaches
Chapter 7 Securing the server and its data
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
Securing network traffic with TLS
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 8 Understanding and designing tables
User-defined data types and user-defined types
System-versioned temporal tables
Horizontally partitioned tables and indexes
Capturing modifications to data
Comparing change tracking, change data capture, and temporal tables
Chapter 9 Performance tuning SQL Server
Understanding isolation levels and concurrency
Understanding how concurrent sessions become blocked
Stating the case against READ UNCOMMITTED (NOLOCK)
Changing the isolation level within transactions
Understanding the enterprise solution to concurrency: SNAPSHOT
Understanding on-disk versus memory-optimized concurrency
Understanding delayed durability
Delayed durability database options
Delayed durability transactions
Understanding parameterization and “parameter sniffing”
Understanding the Procedure Cache
Analyzing cached execution plans in aggregate
Retrieving execution plans in SQL Server Management Studio
Initially configuring the query store
Using query store data in your troubleshooting
Understanding automatic plan correction
Understanding execution plan operators
Interpreting graphical execution plans
Forcing a parallel execution plan
Chapter 10 Understanding and designing indexes
Choosing a proper clustered index key
The case against intentionally designing heaps
Designing nonclustered indexes
Understanding nonclustered index design
Creating “missing” nonclustered indexes
Understanding and proving index usage statistics
Demonstrating the power of Columnstore indexes
Using compression delay on Columnstore indexes
Understanding indexing in memory-optimized tables
Understanding hash indexes for memory-optimized tables
Understanding nonclustered indexes for memory-optimized tables
Moving to memory-optimized tables
Understanding other types of indexes
Understanding full-text indexes
Understanding index statistics
Manually creating and updating statistics
Automatically creating and updating statistics
Important performance options for statistics
Understanding statistics on memory-optimized tables
Understanding statistics on external tables
Chapter 11 Developing, deploying, and managing data recovery
The fundamentals of data recovery
A typical disaster recovery scenario
Establishing and using a run book
An overview of recovery models
Understanding different types of backups
Creating and verifying backups
Restoring a piecemeal database
A sample recovery strategy for a DR scenario
Strategies for a cloud/hybrid environment
Chapter 12 Implementing high availability and disaster recovery
Overview of high availability and disaster recovery technologies in SQL Server
Understanding types of replication
Understanding the capabilities of failover clustering
Understanding the capabilities of availability groups
Comparing HA and DR technologies
Configuring Failover Cluster Instances
Configuring availability groups
Comparing different cluster types and failover
Creating WSFC for use with availability groups
Understanding the database mirroring endpoint
Configuring the minimum synchronized required nodes
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 an availability group on Red Hat Linux
Setting up an availability group
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 13 Managing and monitoring SQL Server
Setting the database’s page verify option
Repairing database data file corruption
Recovering the 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
Reorganizing Columnstore indexes
Maintaining database file sizes
Understanding and finding autogrowth events
Monitoring databases by using DMVs
Understanding wait types and wait statistics
Using extended events to detect deadlocks
Using extended events to detect autogrowth events
Capturing Windows performance metrics with DMVs and data collectors
Querying performance metrics by using DMVs
Querying performance metrics by using Performance Monitor
Monitoring key performance metrics
Protecting important workloads using Resource Governor
Configuring the Resource Governor classifier function
Configuring Resource Governor pools and groups
Understanding the new servicing model
Chapter 14 Automating SQL Server administration
Components of SQL Server automated administration
Configuring SQL Server Agent jobs
Basic “care and feeding” of SQL Server
Using SQL Server Maintenance Plans
Maintenance Plan report options
Covering databases with the Maintenance Plan
Building Maintenance Plans by using the design surface in SQL Server Management Studio
Backups on secondary replicas in availability groups
Strategies for administering multiple SQL Servers
Master and Target servers for SQL Agent jobs
SQL Server Agent event forwarding
Evaluating policies and gathering compliance data
Using PowerShell to automate SQL Server administration
Installing the PowerShell SQLSERVER module
Using PowerShell with SQL Server