Table of Contents

  1. About the Authors

  2. Acknowledgments

  3. Foreword

  4. Introduction

    1. Who this book is for

    2. How this book is organized

    3. Conventions

      1. Text conventions

      2. Book features

    4. Errata, updates, and book support

  5. Part I: Introduction

    1. Chapter 1 Get started with SQL Server tools

      1. SQL Server setup

        1. Install SQL Server with the Installation Center

        2. Plan before an upgrade or installation

        3. Install or upgrade SQL Server

      2. Tools and services installed with the Database Engine

        1. Machine Learning Services

        2. Data Quality Services

        3. Command line interface

        4. SQL Server Configuration Manager

      3. Performance and reliability monitoring tools

        1. Database Engine Tuning Advisor

        2. Extended Events

        3. Management Data Warehouse

      4. SQL Server Reporting Services (SSRS)

        1. Installation

        2. Report Server Configuration Manager

      5. SQL Server Management Studio (SSMS)

        1. Releases and versions

        2. Install SQL Server Management Studio

        3. Upgrade SQL Server Management Studio

        4. Features of SQL Server Management Studio

        5. Additional tools in SQL Server Management Studio

        6. Error logs

        7. Activity Monitor

        8. SQL Server Agent

      6. Azure Data Studio

        1. User interface

        2. Highlighted features in Azure Data Studio

        3. Notebooks in Azure Data Studio

      7. SQL Server Data Tools

        1. SQL Server Integration Services

      8. SQL Server on Azure Arc–enabled servers

      9. Microsoft Purview

      10. Discontinued and deprecated features

    2. Chapter 2 Introduction to database server components

      1. Memory

        1. Understand the working set

        2. Cache data in the buffer pool

        3. Cached plans in the procedure cache

        4. Lock pages in memory

        5. Editions and memory limits

      2. Central processing unit

        1. Simultaneous multithreading

        2. Non-uniform memory access

        3. Disable power saving everywhere

      3. Data storage

        1. Types of storage

        2. Configure the storage layer

      4. Connect to SQL Server over the network

        1. Protocols and ports

        2. Added complexity with Virtual Local Area Networks

      5. High-availability concepts

        1. Why redundancy matters

        2. Disaster recovery

        3. Clustering

        4. The versatility of log shipping

        5. Always On availability groups

      6. Secure SQL Server

        1. Integrated Authentication and Active Directory

        2. Azure Active Directory

        3. Kerberos for Azure SQL Managed Instance

      7. Understand virtualization and containers

        1. Going virtual

        2. Provision resources for virtual consumers

        3. When processors are no longer processors

        4. The network is virtual, too

    3. Chapter 3 Design and implement an on-premises database infrastructure

      1. Introduction to SQL Server database architecture

      2. Data files and filegroups

        1. Group data pages with extents

        2. Contents and types of data pages

        3. Verify data pages by using a checksum

      3. Record changes in the transaction log

        1. Flush data to the storage subsystem with checkpoints

        2. Inside the transaction log file

        3. The Minimum Recovery LSN

        4. Types of database checkpoints

        5. Restart with recovery

        6. MinLSN and the active log

        7. A faster recovery with accelerated database recovery

      4. Partition tables

      5. Compress data

        1. Table and index compression

        2. Backup compression

      6. Manage the temporary database

        1. Storage options for tempdb

        2. Recommended number of files

      7. Configuration settings

        1. Manage system usage with Resource Governor

        2. Configure the operating system page file

        3. Take advantage of logical processors with parallelism

        4. SQL Server memory settings

        5. Allocate CPU cores with an affinity mask

        6. File system configuration

  6. Part II: Deployment

    1. Chapter 4 Install and configure SQL Server instances and features

      1. What to do before installing SQL Server

        1. Decide on volume usage

        2. Important SQL Server volume settings

        3. SQL Server editions

      2. Install a new instance

        1. Plan for multiple SQL Server instances

        2. Install SQL Server on Windows

        3. Install common features

        4. Log SQL Server Setup

        5. Automate SQL Server Setup with configuration files

      3. SQL Server on Azure virtual machines

      4. Post-installation server configuration

        1. Post-installation checklist

      5. Post-installation configuration of other features

        1. SSISDB initial configuration and setup

        2. SQL Server Reporting Services initial configuration and setup

        3. SQL Server Analysis Services initial configuration and setup

        4. Azure Synapse Link for SQL Server

      6. Container orchestration with Kubernetes

        1. Kubernetes support for SQL Server

        2. Deploy SQL Server in containers

        3. Get started with SQL Server on Kubernetes

        4. Deploy SQL Server on Kubernetes

        5. Review cluster health

    2. Chapter 5 Install and configure SQL Server on Linux

      1. What is Linux?

        1. Differences between Windows and Linux

        2. Linux distributions supported by SQL Server

      2. Considerations for installing SQL Server on Linux

        1. Configure OS settings

      3. Install SQL Server on Linux

        1. Installation requirements

        2. Download and install packages

      4. Configure SQL Server on Linux

        1. Use mssql-conf to set up and configure SQL Server

      5. Caveats of SQL Server on Linux

        1. Missing SQL Server features on Linux

    3. Chapter 6 Provision and configure SQL Server databases

      1. Add databases to a SQL Server instance

        1. Create a database

        2. Move existing databases

        3. Upgrade database compatibility levels

        4. Other considerations for migrating databases

        5. Database-scoped configurations

        6. Database properties and options

      2. Move and remove databases

        1. Move user and system databases

        2. Move databases within instances

        3. Single-user mode

    4. Chapter 7 Understand table features

      1. Review table structures

        1. General-purpose data types

        2. Specialized data types

        3. Data type precedence

        4. Constraints

        5. Sequence objects

        6. User-defined data types and user-defined types

        7. Sparse columns

        8. Computed columns

      2. Special table types

        1. System-versioned temporal tables

        2. Memory-optimized tables

        3. Graph tables

      3. Store large binary objects

        1. Understand FILESTREAM

        2. FileTable

      4. Table partitions

        1. Horizontally partitioned tables and indexes

        2. Vertical partitions

      5. Capture modifications to data

        1. Use change tracking

        2. Use change data capture

        3. Query change tracking and change data capture

        4. Compare change tracking, change data capture, and temporal tables

      6. Benefits of PolyBase for external data sources and external tables

        1. Unified data platform features

        2. Install and configure PolyBase

        3. More PolyBase examples, architectures including S3 and URL queries

        4. PolyBase examples with a generic ODBC driver

        5. Azure bulk operations examples

  7. Part III: SQL Server management

    1. Chapter 8 Maintain and monitor SQL Server

      1. Detect, prevent, and respond to database corruption

        1. Set the database’s page verify option

        2. Repair database data file corruption

        3. Recover from database transaction log file corruption

        4. Database corruption in Azure SQL Database

      2. Maintain indexes and statistics

        1. Change the fill factor when beneficial

        2. Monitor index fragmentation

        3. Maintain indexes

      3. Manage database file sizes

        1. Understand and find autogrowth events

        2. Shrink database files

      4. Monitor activity with DMOs

        1. Observe sessions and requests

        2. Understand wait types and wait statistics

      5. Monitor with the SQL Assessment API

      6. Use Extended Events

        1. View Extended Events data

        2. Use Extended Events to capture deadlocks

        3. Use Extended Events to detect autogrowth events

        4. Use Extended Events to detect page splits

        5. Secure Extended Events

      7. Capture performance metrics with DMOs and data collectors

        1. Query performance metrics with DMVs

        2. Capture performance metrics with Performance Monitor

        3. Monitor key performance metrics

        4. Monitor key performance metrics in Linux

        5. Monitor key performance metrics in Azure portal

      8. Protect important workloads with Resource Governor

        1. Configure the Resource Governor classifier function

        2. Configure Resource Governor resource pools and workload groups

        3. Monitor resource pools and workload groups

      9. Understand the SQL Server servicing model

        1. Updated servicing model

        2. Plan for the product support life cycle

    2. Chapter 9 Automate SQL Server administration

      1. Foundations of SQL Server automated administration

        1. Database Mail

        2. SQL Server Agent

      2. Maintain SQL Server

        1. Basic care and feeding of SQL Server

      3. Use SQL Server maintenance plans

        1. Cover databases with the maintenance plan

        2. Maintenance plan tasks

        3. Maintenance plan report options

        4. Build maintenance plans using the Maintenance Plan designer in SSMS

        5. Back up availability groups using a secondary replica

      4. Strategies for administering multiple SQL Servers

        1. Master/Target servers for SQL Agent jobs

        2. SQL Server Agent event forwarding

        3. Policy-based management

      5. Use PowerShell to automate SQL Server administration

        1. PowerShell basics

        2. Install the PowerShell SQLServer module

        3. Use PowerShell with SQL Server

        4. Use PowerShell with availability groups

    3. Chapter 10 Develop, deploy, and manage data recovery

      1. Prepare for data recovery

        1. A disaster recovery scenario

        2. Define acceptable data loss: RPO

        3. Define acceptable downtime: RTO

        4. Establish and use a runbook

      2. Ransomware attacks

      3. Understand different types of backups

        1. An overview of SQL Server recovery models

        2. Full backups

        3. Differential backups

        4. The backup chain

        5. File and filegroup backups

        6. Additional backup options and considerations

      4. Understand backup devices

        1. Back up to disk

        2. Back up to URL

        3. Backup and media sets

        4. Back up to S3-compatible storage

      5. Create and verify backups

        1. Create backups

        2. Verify backups

      6. Restore a database

        1. Restore a database using a full backup

        2. Restore a database with differential and log backups

        3. Restore a database to a point in time

        4. Restore a database piecemeal

      7. Define a recovery strategy

        1. A sample recovery strategy for our DR scenario

        2. Recovery strategies for hybrid and cloud environments

    4. Chapter 11 Implement high availability and disaster recovery

      1. Overview of high-availability and disaster-recovery technologies

        1. Compare HA and DR technologies

        2. Understand log shipping

        3. Understand the capabilities of failover clustering

        4. Understand the capabilities of availability groups

      2. Configure failover cluster instances

        1. Understand FCI quorum

        2. Configure a SQL Server FCI

        3. Patch a failover cluster

      3. Design availability groups solutions

        1. Compare different cluster types

        2. Create WSFC for use with availability groups

        3. Understand the database mirroring endpoint

        4. Recent improvements to availability groups

        5. Choose the correct secondary replica availability mode

        6. Understand the impact of secondary replicas on performance

        7. Understand failovers in availability groups

        8. Seeding options when adding replicas

        9. Additional actions after creating an availability group

        10. Read secondary database copies

        11. Query Store on replicas

        12. Implement a hybrid availability group topology

      4. Understand the Azure SQL Managed Instance link feature

        1. Failover and failback to Azure SQL Managed Instance with database portability

        2. Provision and scale the Azure SQL Managed Instance link feature

        3. Failover and failback tooling and automation

      5. Configure availability groups in SQL Server on Linux

        1. Understand the differences between Windows and Linux clustering

        2. Set up an availability group in SQL Server on Linux

      6. Administer availability groups

        1. Analyze DMVs for availability groups

        2. Analyze wait types for availability groups

        3. Analyze Extended Events for availability groups

        4. Alerts for availability groups

  8. Part IV: Security

    1. Chapter 12 Administer instance and database security and permissions

      1. Understand authentication modes

        1. Windows Authentication

        2. SQL Server Authentication

        3. Azure Active Directory

        4. Advanced types of server principals

        5. Authentication to SQL Server on Linux

        6. Contained database authentication

      2. Grasp security principals

        1. The basics of privileges

        2. Configure login server principals

        3. Database principals

      3. Understand permissions and authorization

        1. Permissions for controlling Data Definition Language and Data Manipulation Language

        2. How permissions accumulate

        3. Understand authorization

      4. Perform common security administration tasks

        1. Orphaned SIDs

        2. Create login with known SID

        3. Migrate SQL Server logins and permissions

        4. Dedicated administrator connection

    2. Chapter 13 Protect data through classification, encryption, and auditing

      1. Privacy in the modern era

        1. General Data Protection Regulation (GDPR)

      2. Microsoft Purview overview

      3. Introduction to security principles and protocols

        1. Secure your environment with defense in depth

        2. The difference between hashing and encryption

        3. A primer on protocols and transmitting data

        4. Digital certificates

      4. Protect the data platform

        1. Secure the network with TLS

        2. Data protection from the OS

        3. The encryption hierarchy in detail

        4. Use EKM modules with SQL Server

        5. Master keys in the encryption hierarchy

        6. Encrypt data with TDE

        7. Protect sensitive columns with Always Encrypted

        8. Row-level security

        9. Dynamic data masking

        10. Protect Azure SQL Database with Microsoft Defender for SQL

      5. Ledger overview

        1. Immutable storage

        2. Ledger verification

        3. Ledger considerations and limitations

        4. Data storage requirements

        5. Types of ledger tables

      6. Audit with SQL Server and Azure SQL Database

        1. SQL Server Audit

        2. Auditing with Azure SQL

      7. Secure Azure infrastructure as a service

        1. Network security groups

        2. User-defined routes and IP forwarding

        3. Additional Azure networking security features

  9. Part V: Performance

    1. Chapter 14 Performance tune SQL Server

      1. Understand isolation levels and concurrency

        1. Understand how concurrent sessions become blocked

        2. Change the isolation level

        3. Understand and handle common concurrency scenarios

        4. Understand row version-based concurrency

        5. Understand on-disk versus memory-optimized concurrency

      2. Understand durability settings for performance

        1. Delayed durability database options

      3. How SQL Server executes a query

        1. Understand the query execution process

        2. View execution plans

        3. Understand execution plans

        4. Understand parameterization and parameter sniffing

        5. Explore the procedure cache

        6. Understand parallelism

      4. Use advanced engine features to tune queries

        1. Internal improvements in SQL Server 2022

        2. Recent improvements to tempdb

        3. Leverage the Query Store feature

        4. Query Store hints

        5. Automatic plan correction

        6. Intelligent query processing

    2. Chapter 15 Understand and design indexes

      1. Design clustered indexes

        1. Choose a proper rowstore clustered index key

        2. The case against intentionally designing heaps

        3. Understand the OPTIMIZE_FOR_SEQUENTIAL_KEY feature

      2. Design rowstore nonclustered indexes

        1. Understand nonclustered index design

        2. Create filtered nonclustered indexes

        3. Understand the missing indexes feature

        4. Understand and provide index usage

      3. Understand columnstore indexes

        1. Design columnstore indexes

        2. Understand batch mode

        3. Understand the deltastore of columnstore indexes

        4. Demonstrate the power of columnstore indexes

      4. Understand indexes in memory-optimized tables

        1. Understand hash indexes for memory-optimized tables

        2. Understand nonclustered indexes for memory-optimized tables

      5. Understand index statistics

        1. Automatically create and update statistics

        2. Manually create statistics for on-disk tables

        3. Understand statistics on memory-optimized tables

        4. Understand statistics on external tables

      6. Understand other types of indexes

        1. Understand full-text indexes

        2. Understand spatial indexes

        3. Understand XML indexes

  10. Part VI: Cloud

    1. Chapter 16 Design and implement hybrid and Azure database infrastructure

      1. Cloud computing and Microsoft Azure

        1. Database as a service

        2. Managing Azure with the Azure portal and PowerShell 7

        3. Azure governance

        4. Cloud-first

        5. Resource scalability

        6. Networking in Azure

      2. Cloud models and SQL Server

        1. Infrastructure as a service

        2. Platform as a service

        3. Hybrid cloud with Azure

      3. Cloud security

      4. Other data services in Azure

        1. Azure Synapse Analytics

        2. Non-relational Azure data offerings

        3. Third-party fully managed data platforms

    2. Chapter 17 Provision Azure SQL Database

      1. Provision an Azure SQL Database logical server

        1. Create an Azure SQL Database server using the Azure portal

        2. Create a server using PowerShell

        3. Establish a connection to your server

        4. Delete a server

      2. Provision a database in Azure SQL Database

        1. Create a database using the Azure portal

        2. Create a database using PowerShell

        3. Create a database using Azure CLI

        4. Create a database using T-SQL

        5. Scale up or down

        6. Provision a named replica for a Hyperscale database

      3. Provision an elastic pool

      4. Manage database space

      5. Security in Azure SQL Database

        1. Security features shared with SQL Server 2022

        2. Server- and database-level firewall

        3. Integrate with virtual networks

        4. Azure Private Link for Azure SQL Database

        5. Control access using Azure AD

        6. Use Azure role-based access control

        7. Audit database activity

        8. Microsoft Defender for SQL

      6. Prepare Azure SQL Database for disaster recovery

        1. Understand default disaster recovery features

        2. Manually export database contents

        3. Enable zone-redundant configuration

        4. Configure geo-replication

        5. Set up failover groups

        6. Use Azure Backup for long-term backup retention

    3. Chapter 18 Provision Azure SQL Managed Instance

      1. What is Azure SQL Managed Instance?

        1. Differences between SQL Server and Azure SQL Managed Instance

      2. Create a SQL managed instance

        1. Select a service tier and service objective

        2. Use the Azure portal to provision a SQL managed instance

        3. Use PowerShell to provision a SQL managed instance

      3. Delete a SQL managed instance

      4. Establish a connection to a SQL managed instance

        1. Create the endpoints via the Azure portal

        2. Create a VPN gateway via PowerShell

        3. Network requirements for SQL managed instances

      5. Migrate data to Azure SQL Managed Instance

        1. Link feature for Azure SQL Managed Instance

        2. Azure Data Migration Service

        3. Migrate with backup and restore

        4. Managed instance pools

      6. Azure SQL Managed Instance administration features

        1. High availability

        2. Replication

        3. Scale up or down

        4. Monitor SQL managed instances

        5. Link feature for Azure SQL Managed Instance

      7. Azure SQL Managed Instance security features

        1. Azure Active Directory

      8. Azure SQL Managed Instance data protection features

        1. Prevent data exfiltration

        2. Isolation

        3. Auditing

        4. Data encryption

        5. Row-level security

        6. Dynamic data masking

    4. Chapter 19 Migrate to SQL Server solutions in Azure

      1. Migration services options

        1. Microsoft Assessment Planning toolkit

        2. Total Cost of Ownership calculator

        3. Database Experimentation Assistant

        4. Azure Data Migration Assistant

        5. Azure Database Migration Service

        6. SQL Server Migration Assistant

        7. Data Access Migration Toolkit

      2. Resolve common migration failures using Database Migration Service

        1. Large object columns with data larger than 32 KB

        2. Final notes for migration

        3. Open source PowerShell migration with dbatools

      3. Migrate with Azure Data Factory

        1. Azure integration runtime

        2. Self-hosted integration runtime

        3. Self-hosted IR servers and nodes

        4. Azure-SSIS integration runtime

      4. Best practices for security and resilience during migration

        1. Network security

        2. Cloud requirements for application resilience

  11. Index

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.119.131.72