SQL Server 2022 Administration Inside Out

SQL Server 2022 Administration Inside Out

by Randolph West, Melody Zacharias, William Assaf, Deepthi Goguri, Elizabeth Noble, Meagan Longoria, Joseph D'Antoni, Louis Davidson
Released May 2023
Publisher(s): Microsoft Press
ISBN: 9780137899845

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Conquer SQL Server 2022 and Azure SQL administration from the inside out!

Dive into SQL Server 2022 administration and grow your Microsoft SQL Server data platform skillset. This well-organized reference packs in timesaving solutions, tips, and workarounds, all you need to plan, implement, deploy, provision, manage, and secure SQL Server 2022 in any environment: on-premises, cloud, or hybrid, including detailed, dedicated chapters on Azure SQL Database and Azure SQL Managed Instance. Nine experts thoroughly tour DBA capabilities available in the SQL Server 2022 Database Engine, SQL Server Data Tools, SQL Server Management Studio, PowerShell, and much more. Youll find extensive new coverage of Azure SQL Database and Azure SQL Managed Instance, both as a cloud platform of SQL Server and in their new integrations with SQL Server 2022, information available in no other book. Discover how experts tackle todays essential tasks and challenge yourself to new levels of mastery.

  • Identify low-hanging fruit and practical, easy wins for improving SQL Server administration

  • Get started with modern SQL Server tools, including SQL Server Management Studio, and Azure Data Studio

  • Upgrade your SQL Server administration skillset to new features of SQL Server 2022, Azure SQL Database, Azure SQL Managed Instance, and SQL Server on Linux

  • Design and implement modern on-premises database infrastructure, including Kubernetes

  • Leverage data virtualization of third-party or non-relational data sources

  • Monitor SQL instances for corruption, index activity, fragmentation, and extended events

  • Automate maintenance plans, database mail, jobs, alerts, proxies, and event forwarding

  • Protect data through encryption, privacy, and auditing

  • Provision, manage, scale and secure, and bidirectionally synchronize Microsofts powerful Azure SQL Managed Instance

  • Understand and enable new Intelligent Query Processing features to increase query concurrency

  • Prepare a best-practice runbook for disaster recovery

  • Use SQL Server 2022 features to span infrastructure across hybrid environments

.

Table of contents

  1. Cover Page
  2. Title Page
  3. Copyright Page
  4. Pearson’s Commitment to Diversity, Equity, and Inclusion
  5. Dedications
  6. Contents at a glance
  7. Table of Contents
  8. About the Authors
  9. Acknowledgments
    1. Randolph West
    2. William Assaf
    3. Elizabeth Noble
    4. Meagan Longoria
    5. Joey D’Antoni
    6. Louis Davidson
    7. Melody Zacharias
    8. William Carter
    9. Josh Smith
  10. Foreword
  11. Introduction
    1. Who this book is for
    2. How this book is organized
    3. Conventions
    4. Errata, updates, and book support
  12. Part I: Introduction
    1. Chapter 1. Get started with SQL Server tools
      1. SQL Server setup
      2. Tools and services installed with the Database Engine
      3. Performance and reliability monitoring tools
      4. SQL Server Reporting Services (SSRS)
      5. SQL Server Management Studio (SSMS)
      6. Azure Data Studio
      7. SQL Server Data Tools
      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
      2. Central processing unit
      3. Data storage
      4. Connect to SQL Server over the network
      5. High-availability concepts
      6. Secure SQL Server
      7. Understand virtualization and containers
    3. Chapter 3. Design and implement an on-premises database infrastructure
      1. Introduction to SQL Server database architecture
      2. Data files and filegroups
      3. Record changes in the transaction log
      4. Partition tables
      5. Compress data
      6. Manage the temporary database
      7. Configuration settings
  13. Part II: Deployment
    1. Chapter 4. Install and configure SQL Server instances and features
      1. What to do before installing SQL Server
      2. Install a new instance
      3. SQL Server on Azure virtual machines
      4. Post-installation server configuration
      5. Post-installation configuration of other features
      6. Container orchestration with Kubernetes
    2. Chapter 5. Install and configure SQL Server on Linux
      1. What is Linux?
      2. Considerations for installing SQL Server on Linux
      3. Install SQL Server on Linux
      4. Configure SQL Server on Linux
      5. Caveats of SQL Server on Linux
    3. Chapter 6. Provision and configure SQL Server databases
      1. Add databases to a SQL Server instance
      2. Move and remove databases
    4. Chapter 7. Understand table features
      1. Review table structures
      2. Special table types
      3. Store large binary objects
      4. Table partitions
      5. Capture modifications to data
      6. Benefits of PolyBase for external data sources and external tables
  14. Part III: SQL Server management
    1. Chapter 8. Maintain and monitor SQL Server
      1. Detect, prevent, and respond to database corruption
      2. Maintain indexes and statistics
      3. Manage database file sizes
      4. Monitor activity with DMOs
      5. Monitor with the SQL Assessment API
      6. Use Extended Events
      7. Capture performance metrics with DMOs and data collectors
      8. Protect important workloads with Resource Governor
      9. Understand the SQL Server servicing model
    2. Chapter 9. Automate SQL Server administration
      1. Foundations of SQL Server automated administration
      2. Maintain SQL Server
      3. Use SQL Server maintenance plans
      4. Strategies for administering multiple SQL Servers
      5. Use PowerShell to automate SQL Server administration
    3. Chapter 10. Develop, deploy, and manage data recovery
      1. Prepare for data recovery
      2. Ransomware attacks
      3. Understand different types of backups
      4. Understand backup devices
      5. Create and verify backups
      6. Restore a database
      7. Define a recovery strategy
    4. Chapter 11. Implement high availability and disaster recovery
      1. Overview of high-availability and disaster-recovery technologies
      2. Configure failover cluster instances
      3. Design availability groups solutions
      4. Understand the Azure SQL Managed Instance link feature
      5. Configure availability groups in SQL Server on Linux
      6. Administer availability groups
  15. Part IV: Security
    1. Chapter 12. Administer instance and database security and permissions
      1. Understand authentication modes
      2. Grasp security principals
      3. Understand permissions and authorization
      4. Perform common security administration tasks
    2. Chapter 13. Protect data through classification, encryption, and auditing
      1. Privacy in the modern era
      2. Microsoft Purview overview
      3. Introduction to security principles and protocols
      4. Protect the data platform
      5. Ledger overview
      6. Audit with SQL Server and Azure SQL Database
      7. Secure Azure infrastructure as a service
  16. Part V: Performance
    1. Chapter 14. Performance tune SQL Server
      1. Understand isolation levels and concurrency
      2. Understand durability settings for performance
      3. How SQL Server executes a query
      4. Use advanced engine features to tune queries
    2. Chapter 15. Understand and design indexes
      1. Design clustered indexes
      2. Design rowstore nonclustered indexes
      3. Understand columnstore indexes
      4. Understand indexes in memory-optimized tables
      5. Understand index statistics
      6. Understand other types of indexes
  17. Part VI: Cloud
    1. Chapter 16. Design and implement hybrid and Azure database infrastructure
      1. Cloud computing and Microsoft Azure
      2. Cloud models and SQL Server
      3. Cloud security
      4. Other data services in Azure
    2. Chapter 17. Provision Azure SQL Database
      1. Provision an Azure SQL Database logical server
      2. Provision a database in Azure SQL Database
      3. Provision an elastic pool
      4. Manage database space
      5. Security in Azure SQL Database
      6. Prepare Azure SQL Database for disaster recovery
    3. Chapter 18. Provision Azure SQL Managed Instance
      1. What is Azure SQL Managed Instance?
      2. Create a SQL managed instance
      3. Delete a SQL managed instance
      4. Establish a connection to a SQL managed instance
      5. Migrate data to Azure SQL Managed Instance
      6. Azure SQL Managed Instance administration features
      7. Azure SQL Managed Instance security features
      8. Azure SQL Managed Instance data protection features
    4. Chapter 19. Migrate to SQL Server solutions in Azure
      1. Migration services options
      2. Resolve common migration failures using Database Migration Service
      3. Migrate with Azure Data Factory
      4. Best practices for security and resilience during migration
  18. Index
  19. Code Snippets

Product information

  • Title: SQL Server 2022 Administration Inside Out
  • Author(s): Randolph West, Melody Zacharias, William Assaf, Deepthi Goguri, Elizabeth Noble, Meagan Longoria, Joseph D'Antoni, Louis Davidson
  • Release date: May 2023
  • Publisher(s): Microsoft Press
  • ISBN: 9780137899845