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

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

The previous section described the whole set of services and features contained in SQL Server. From now on, we will pay attention to on-premises SQL Server DE installed on Windows only.

In this section, we will discuss the following topics:

• Which edition of SQL Server to buy with respect to the features and performance capabilities
• How to prepare our Windows operating system and other prerequisites
• Installation options such as installation wizard, Command Prompt, and the sysprep utility

Edition comparison

Microsoft provides SQL Server in several editions. Each edition has its supported features, and with these features, the allocation of resources will differentiate. This can be seen in terms of performance, price, runtime, and service availability. A complete edition comparison matrix is published at https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server...

Microsoft provides several options to install SQL Server and its technologies as simply as possible. Almost everything is done through the SQL Server Installation Center, which is opened via the autorun property of the installation media. The next chapter describes typical scenarios of installation and first post-installation checks and configurations.

Installation options

The SQL Server Installation Center provides several ways to install SQL Server. The most common method of installation is to use the wizard for a standalone installation as well as for a cluster installation of SQL Server.

For situations where more SQL Servers are propagated into the environment (for example, new departments or sales points are often created, and every department or sales point has its own SQL Server), SQL Server provides an option to be installed through the command line, which is also the only installation approach possible for...

Since SQL Server 2017, Microsoft decided to offer its distribution on Linux. SQL Server's Linux distribution helps administrators to use familiar operating systems as well as install SQL Server to containers. This section is a step-by-step walkthrough example of the sample SQL Server installation process on Ubuntu 18.04.

Preparing the test environment

For many administrators and DBAs strongly bound to Microsoft operating systems, the world of Linux seems very strange and confusing. That's why the first step is a preparation of the Hyper-V virtual machine (VM). Microsoft provides a Hyper-V option called Hyper-V Quick Create. Its usage is very simple and straightforward, as follows:

1. In the Start menu, find the Hyper-V Quick Create application.
2. In the opened window, select Ubuntu 18.04.
3. Click the Create Virtual Machine button.
4. Follow the installation instructions.

The whole installation process is almost self...

Virtualization of computers hosting SQL Server is very common nowadays. Containers provide the next level of virtualization. A container itself is a lightweight computer hosted by the container environment. The question is: why use containers over virtual machines? Containers provide a simple way to prepare the environment that is often destroyed or moved between hosts. It is useful, for instance, during the development phase, when developers need to refresh their server environment frequently.

The basis of containers adopted by Microsoft lies on a Linux-based technology called Docker. It's also a prerequisite when we'd like to try provisioning. We need to have Docker Engine 1.8 or higher installed on our Linux computer, or we can install Docker for Windows as well.

Note

Installing Docker for Windows needs the Hyper-V feature of Windows installed, because Docker itself is then running on the Linux VM.

The provisioning of...

The SQL Server ecosystem provides you with a wide set of technologies. The first problem is to know what is the responsibility of every single technology of SQL Server. In the first section of this chapter, we cleared what is necessary to install, which helped us recognize what we need.

In the second section, we prepared our operating system and the complete infrastructure to install SQL Server. The most important decision before starting the installation is which technologies to install and how many computers will be needed to distribute SQL Server services appropriately across an infrastructure. Before installing SQL Server DE itself, we need to ensure that a sufficient set of disk storage is in place, appropriate security accounts are prepared, and all software prerequisites are fulfilled.

The installation described in the Installing SQL Server 2019 on Windows section of this chapter is quite a straightforward process, but we should still consider some settings. Even...