SQL Server 2019 Administrator's Guide - Second Edition

SQL Server 2019 Administrator's Guide - Second Edition

by Marek Chmel, Vladimir Muzny
Released September 2020
Publisher(s): Packt Publishing
ISBN: 9781789954326

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

Use Microsoft SQL Server 2019 to implement, administer, and secure a robust database solution that is disaster-proof and highly available

Key Features

  • Explore new features of SQL Server 2019 to set up, administer, and maintain your database solution successfully
  • Develop a dynamic SQL Server environment and streamline big data pipelines
  • Discover best practices for fixing performance issues, database access management, replication, and security

Book Description

SQL Server is one of the most popular relational database management systems developed by Microsoft. This second edition of the SQL Server Administrator's Guide will not only teach you how to administer an enterprise database, but also help you become proficient at managing and keeping the database available, secure, and stable.

You'll start by learning how to set up your SQL Server and configure new and existing environments for optimal use. The book then takes you through designing aspects and delves into performance tuning by showing you how to use indexes effectively. You'll understand certain choices that need to be made about backups, implement security policy, and discover how to keep your environment healthy. Tools available for monitoring and managing a SQL Server database, including automating health reviews, performance checks, and much more, will also be discussed in detail. As you advance, the book covers essential topics such as migration, upgrading, and consolidation, along with the techniques that will help you when things go wrong. Once you've got to grips with integration with Azure and streamlining big data pipelines, you'll learn best practices from industry experts for maintaining a highly reliable database solution.

Whether you are an administrator or are looking to get started with database administration, this SQL Server book will help you develop the skills you need to successfully create, design, and deploy database solutions.

What you will learn

  • Discover SQL Server 2019's new features and how to implement them
  • Fix performance issues by optimizing queries and making use of indexes
  • Design and use an optimal database management strategy
  • Combine SQL Server 2019 with Azure and manage your solution using various automation techniques
  • Implement efficient backup and recovery techniques in line with security policies
  • Get to grips with migrating, upgrading, and consolidating with SQL Server
  • Set up an AlwaysOn-enabled stable and fast SQL Server 2019 environment
  • Understand how to work with Big Data on SQL Server environments

Who this book is for

This book is for database administrators, database developers, and anyone who wants to administer large and multiple databases single-handedly using Microsoft's SQL Server 2019. Basic awareness of database concepts and experience with previous SQL Server versions is required.

Table of contents

  1. SQL Server 2019 Administrator's Guide – Second Edition
  2. Why subscribe?
  3. Contributors
  4. About the authors
  5. About the reviewers
  6. Packt is searching for authors like you
  7. Preface
    1. Who this book is for
    2. What this book covers
    3. To get the most out of this book
    4. Download the example code files
    5. Download the color images
    6. Conventions used
    7. Get in touch
    8. Reviews
  8. Section 1: Provisioning the SQL Server Environment
  9. Chapter 1: Setting Up SQL Server 2019
    1. Overview of the Microsoft SQL Server 2019 technology
      1. Understanding SQL Server DE
      2. SSIS
      3. SSAS
      4. SSRS
      5. Machine Learning Services
      6. SQL Server Agent
    2. Preparing the SQL Server 2019 installation on Windows
      1. Edition comparison
      2. Pre-installation tasks
    3. Installing SQL Server 2019 on Windows
      1. Installation options
      2. Checking the completed installation
    4. Installing SQL Server 2019 on Linux
      1. Preparing the test environment
      2. Installing SQL Server
      3. Testing connection to SQL Server on Linux
    5. Installing SQL Server 2019 on containers
    6. Summary
  10. Chapter 2: Keeping Your SQL Server Environment Healthy
    1. Understanding SQL Server patching
      1. Installing updates
    2. Configuring SQL Server environment
      1. Configuring security rights for your SQL Server account
      2. Configuring power settings
      3. Configuring firewall rules
      4. SQL Server post-installation configuration
    3. Creating a performance baseline
    4. Summary
  11. Section 2: Server and Database Maintenance
  12. Chapter 3: Implementing Backup and Recovery
    1. Data structures and transaction logging
      1. How data is stored
    2. Accelerated Database Recovery
    3. SQL Server recovery models
      1. How to configure a database's recovery model property
    4. Designing a backup strategy
      1. Backup types
      2. Advanced backup scenarios
    5. Using database and log restore
      1. Preparing for restore
      2. Executing restores
    6. Summary
  13. Chapter 4: Securing Your SQL Server
    1. Configuring SQL Server service accounts
      1. Virtual accounts
      2. Managed service accounts
      3. gMSAs
      4. Built-in system accounts
      5. Domain user accounts and local Windows accounts
    2. Configuring authentication and authorization
      1. Authentication
      2. Authorization
      3. Auditing
      4. Configuring credentials
    3. Encrypting SQL Server data
      1. Transparent Data Encryption
      2. Always Encrypted
    4. Data Discovery and Classification
    5. SQL Server vulnerability assessment
    6. Encrypting SQL Server connections
    7. Summary
  14. Chapter 5: Working with Disaster Recovery Options
    1. Understanding the basics of disaster recovery
      1. Disaster recovery exercises
    2. SQL Server options for high availability and disaster recovery
      1. Always On Failover Cluster
      2. Always On Availability Groups
      3. Database mirroring
      4. Log shipping
      5. Replication
    3. Configuring replication on SQL Server
      1. Creating a Publication
      2. Configuring the subscription
      3. Replication agents
    4. Understanding database mirroring
      1. Configuring database mirroring
    5. Configuring log shipping
      1. Switching log shipping roles
    6. Summary
  15. Chapter 6: Indexing and Performance
    1. Explaining SQL Server internals
    2. SQL Server protocols
      1. Query processor
      2. The storage engine layer
    3. Performance monitoring overview
    4. Tools for monitoring performance
      1. Activity Monitor
      2. Performance monitor
      3. SQL Server Profiler and SQL Trace
      4. Extended Events
      5. Dynamic management
      6. Data collection
      7. Query Store
    5. Indexes and maintenance
      1. Types of indexes
      2. Indexing considerations
    6. Common performance issue patterns
      1. Unacceptable slow response from SQL Server to a query
      2. I found the poor query; what's next?
      3. Can I want more from indexes?
    7. Summary
  16. Section 3: High Availability and the Cloud with SQL Server 2019
  17. Chapter 7: Planning Migration and Upgrade
    1. The importance of keeping up with latest version
    2. Planning the upgrade
      1. Exploring upgrade scenarios
      2. Pre-upgrade checks
    3. Performing the upgrade
      1. Upgrading Reporting Services 2019
    4. Migrating from other platforms
      1. Migration example from Microsoft Access
    5. Summary
  18. Chapter 8: Automation – Using Tools to Manage and Monitor SQL Server 2019
    1. Using SQL Server Agent
      1. Setting up the SQL Server Agent service
      2. Setting up SQL Server Agent Properties
      3. Setting up Database Mail
    2. Creating and editing maintenance plans
      1. The Maintenance Plan Wizard
      2. The Maintenance Plan Design Surface
    3. Creating SQL Server Agent objects
      1. Operators
      2. Jobs
      3. Alerts
    4. SQL Server Agent security
      1. SQL Server Agent security roles
      2. Job step security context
      3. Using SQL Server Agent proxies
    5. Summary
  19. Chapter 9: Configuring Always On High Availability Features
    1. Installing Windows Server Failover Cluster
    2. Configuring Always On Failover Cluster Instances
      1. Adding nodes to the SQL Server failover cluster
      2. Initiating a failover
    3. Always On Availability Groups
      1. Configuring Always On Availability Groups
    4. Summary
  20. Chapter 10: In-Memory OLTP – Why and How to Use it
    1. In-Memory OLTP architecture
      1. Data storage differences in In-Memory OLTP
      2. Request processing differences in OLTP
      3. Cooperation between the disk-based and memory-based parts of SQL Server
      4. In-Memory OLTP limitations
    2. Creating in-memory tables and natively compiled stored procedures
      1. Preparing for In-Memory OLTP
      2. Creating In-Memory OLTP objects
    3. In-Memory OLTP usage scenarios
      1. Assignment of the user story sample
      2. Example 1 – Inserting incoming data into in-memory tables
      3. Example 2 – Updating data in an in-memory table
      4. Example 3 – Improving real-time analytics
    4. Monitoring In-Memory OLTP
    5. Summary
  21. Chapter 11: Combining SQL Server 2019 with Azure
    1. Beginning with technologies in Microsoft Azure
      1. Overview of data-related technologies in Azure
      2. Azure SQL solutions
    2. Microsoft SQL Server 2019 and hybrid scenarios
      1. Data files in Azure
      2. Backups in Azure
    3. Quick overview of running SQL workloads in Azure
      1. Licensing
      2. SLA
      3. Disaster recovery
      4. Regular database maintenance
    4. Migrating SQL Server workloads to Azure
      1. Using Azure Database Migration Guide
      2. Using SQL Server Migration Assistant
      3. Using Data Migration Assistant
      4. Using Azure Database Migration Service
    5. Summary
  22. Chapter 12: Taming Big Data with SQL Server
    1. Big data overview
    2. Accessing external data with PolyBase
      1. PolyBase use cases
      2. Installing and configuring PolyBase
      3. Using PolyBase to access external data
    3. Explaining the SQL Server Big Data Clusters architecture and deployment
      1. Deploying Big Data Clusters
    4. Working with a SQL Server Big Data Clusters workload
    5. Summary
  23. Other Books You May Enjoy
    1. Leave a review - let other readers know what you think

Product information

  • Title: SQL Server 2019 Administrator's Guide - Second Edition
  • Author(s): Marek Chmel, Vladimir Muzny
  • Release date: September 2020
  • Publisher(s): Packt Publishing
  • ISBN: 9781789954326