SQL Server 2019 Administrator's Guide

A definitive guide for DBAs to implement, monitor, and maintain enterprise database solutions, 2nd Edition

Marek Chmel, Vladimír Mužný

• Editorial: Packt Publishing
• Año: 2020
• ISBN:
  9781789950335
• Edición: 2
• Categoría: Computer Science
• Categoría: Data Processing

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.

Section 1: Provisioning the SQL Server Environment

The objective of this section is to walk you through the SQL Server technology stack, its complete installation on different operating systems, and initial post-installation configuration.

This section contains the following chapters:

• Chapter 1, Setting Up SQL Server 2019
• Chapter 2, Keeping Your SQL Server Environment Healthy

Chapter 1: Setting Up SQL Server 2019

Microsoft SQL Server is not just a database engine; over the years, it has become a very complex and robust technology set for data management, analysis, and visualizations. As the progress of technologies incorporated into SQL Server grows, it has become more complicated to decide which specific technology is needed, how to prepare the environment for its installation, and which configuration properties administrators should be aware of. With the rising popularity of cloud services, we also have a great option to host database services in Microsoft Azure as well. This chapter offers an introduction to the SQL Server technology stack in on-premises environments and helps you to accomplish its proper installation to start your work with SQL Server.

In this chapter, we will study what the SQL Server technology set contains and the purpose of each technology. We will also get familiar with the prerequisites and pre-installation steps, and will find out which settings are important and which post-installation steps are recommended during the installation of Windows. Using a step-by-step approach, we will learn to install SQL Server on Linux using Ubuntu. In this chapter, we will also understand the process of SQL Server provisioning in containers.

In this chapter, we will cover the following topics:

• Overview of the Microsoft SQL Server 2019 technology
• Preparing the SQL Server 2019 installation on Windows
• Installing SQL Server 2019 on Windows
• Installing SQL Server 2019 on Linux
• Installing SQL Server 2019 on containers

Overview of the Microsoft SQL Server 2019 technology

Microsoft SQL Server offers a powerful end-to-end data processing platform. In other words, we can gain data from an extensive set of sources, securely and reliably managed, transformed, processed, analyzed, and visualized under an all-in-one license.

The following diagram shows what the bigger picture of the SQL Server technology set looks like:

Fig. 1.1 – Overview of SQL Server technology set on-premises

The preceding diagram shows one of the many possible ways in which technologies within SQL Server can cooperate. SQL Server Database Engine (DE) is depicted twice in the diagram because it possibly plays two major roles within the data processing platform, as follows:

• Line-of-Business (LOB) application contention: In the diagram, at the left occurrence of SQL Server DE, SQL Server provides data ingestion from client applications or other external sources of data.
• Business Intelligence (BI) contention: In the diagram, at the right occurrence of SQL Server DE, the SQL Server instance manages data warehouses, that is, databases used for Analysis Services data model processing or for reporting purposes.

As seen in the preceding diagram, SQL Server contains many technologies used and maintained by just one person. So, the following list shows how specific roles use such technologies:

• Database Administrators (DBAs): DBAs work with SQL Server and SQL Server Agent services, ensuring the continuity of operations, security, disaster recovery (DR) strategies, and similar tasks.
• SQL developers: SQL developers are responsible for the content of databases, from database design and transaction handling to the quality and accuracy of data stored in databases.
• Extract, Transform, Load (ETL) developers: ETL developers' playground lies mainly in SQL Server Integration Services (SSIS) services. ETL developers create a whole ETL workflow and ensure the quality and integrity of data extracted from sources and uploaded to targets of the ETL flow.
• BI developers: BI developers work mainly with reports on SQL Server Reporting Services (SSRS) and with multidimensional data models hosted on SQL Server Analysis Services (SSAS).

Although our attention is focused on DBAs, it is still useful to have a brief idea of other user roles within the same huge technology set. The DBA is mainly responsible for assisting all users.

Now, we will explain all components, including SSIS and SSRS, in more detail in the following sections.

Understanding SQL Server DE

The core service in the SQL Server technology set is the SQL Server DE service. This service covers the following three responsibilities, apart from storing and manipulating data:

• Handling recovery: This responsibility means that after any sudden or a planned breakdown of the service or database, the service will recover every database to its last consistent state without any undone transactions.
• Handling transactions: A transaction is mentioned as a single unit of work, and SQL Server DE guarantees that transactions will be durable and isolated and correctly finished with COMMIT or ROLLBACK.
• Handling security: SQL Server DE resolves every request for authentication and authorization and decides if a user or application is known (authenticated) and if a user or application has permission for certain actions (authorization).

SQL Server does not provide its capabilities to end users only. Still, it's necessary to keep in mind that SQL Server DE serves as a base service for almost every other service in the SQL Server technology stack and note the following important points:

• Every BI service, such as Analysis Services or Reporting Services, is actually a client of SQL Server DE.
• Some services, such as Machine Learning Services, can be installed within or independently of SQL Server DE.
• SQL Server Agent (not seen in the previous diagram) plays an exceptional role in the SQL Server ecosystem. This service exists as an indivisible part of every SQL Server DE application. SQL Server Agent hugely helps administrators, as well as other services or components, to automate routine tasks.

Why do we need this information? It's one of the crucial moments when planning a SQL Server installation. For example, Analysis Services is a heavily resource-consuming service, and its deployment along with SQL Server DE could lead to big performance problems and user disappointment with regard to responses on their requests. From a different perspective, installing SQL Server services on separated operating systems leads to increased license expenses and more complex administration efforts.

The following sections will describe each SQL Server service in detail.

SSIS

SSIS is basically used as a data pump of SQL Server. SSIS is used to maintain data movements and transformations between a wide scale of heterogeneous data sources and destinations, as well as migrating or transforming data between several instances of SQL Server. A very common use case of SSIS is in data warehousing to extract, transform, and save data from online transactional processing (OLTP) databases to a data warehouse.

The working unit of this technology is the SSIS package. This is an executable unit of integration services, and we can think of it as a simple application. Its definition consists of two main parts: control flow and data flow. Control flow contains tasks such as creating a temporary folder (Filesystem task), accessing a File Transfer Protocol (FTP) site (FTP task), and many others. One of the most crucial tasks in control flow is called the data flow task. This data flow task contains a definition of the path that data goes through,...