product iamge

SQL Server 2019 Administrator's Guide: A definitive guide for DBAs to implement, monitor, and maintain enterprise database solutions

This document was uploaded by one of our users. The uploader already confirmed that they had the permission to publish it. If you are author/publisher or own the copyright of this documents, please report to us by using this DMCA report form.

Download
Search on Amazon

Simply click on the Download Book button.

Yes, Book downloads on Ebookily are 100% Free.

Sometimes the book is free on Amazon As well, so go ahead and hit "Search on Amazon"

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. Setting Up SQL Server 2019
  2. Keeping Your SQL Server Environment Healthy
  3. Implementing Backup and Recovery
  4. Securing Your SQL Server
  5. Working with Disaster Recovery Options
  6. Indexing and Performance
  7. Planning Migration and Upgrade
  8. Automation - Using Tools to Manage and Monitor SQL Server 2019
  9. Configuring Always On High Availability Features
  10. In-Memory OLTP - Why and How to Use it
  11. Combining SQL Server 2019 with Azure
  12. Taming Big Data With SQL Server

Author(s): Marek Chmel, Vladimír Mužný
Edition: 2
Publisher: Packt Publishing
Year: 2020

Language: English
Commentary: True PDF
Pages: 522

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