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,...
