Preparing the SQL Server 2019 installation on Windows

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-2017?view=sql-server-ver15. The core editions are as follows:

• Enterprise edition: Intended for big enterprise environments.
• Standard edition: Contains almost all services (except MDS and DQS) but has some limited hardware resource consumption as well as some internal limits in SQL Server DE.
• Developer edition: Edition containing all enterprise features, but for development purposes only! Must not be provisioned to the production environment.
• Express edition: The Express edition of SQL Server is published for free but with many limitations; for example, Analysis Services, Integration Services, and SQL Server Agent are not contained in this edition.

Pre-installation tasks

When planning to install SQL Server 2019, there are three important points to be considered, as follows:

• Amount of memory
• Disk set
• Security consequences

Planning memory

Every edition of SQL Server has its limit of maximum consumable memory. It's needed to set up the accessible memory correctly because SQL Server consumes as much memory as possible. Every request to SQL Server needs memory. When preparing the server for SQL Server installation, we must consider two main memory usages, as follows:

• Interpreted queries: This is the traditional approach where SQL Server processes user requests. Data is stored on disk, and when some portion of data is needed by any query, it's cached to a memory area called the buffer cache. The buffer cache, with many other memory portions such as the procedure cache, user connections, and others, is a matter of memory limit given by the edition of SQL Server.
• In-Memory OLTP: In-memory OLTP (with original code name Hekaton, which is still used in articles and books sometimes) is a relatively new SQL Server DE technology that was introduced with the SQL Server 2014 Enterprise edition. Later, in SQL Server 2016 SP 1, In-Memory OLTP has ceased to be an Enterprise feature, and now its memory capacity depends on memory limit determined by certain editions. For example, SQL Server Standard Edition has maximum memory set to 128 GB, and In-Memory OLTP capacity is set to 1/4 of maximum SQL Server memory per database, which means 32 GB of memory up to the regular limit for each database that uses In-Memory OLTP. The In-Memory OLTP area is used for memory-optimized tables—tabular structures for extremely fast access, especially in conjunction with natively compiled stored procedures. If any application needs to use In-Memory OLTP technology, be aware of this extra memory requirement.

When planning the amount of memory, we must keep in mind any concurrent service or application that will be present on the same server.

Planning disk capacity

No simple formula exists to calculate disk capacity. We can just estimate the amount of disk space needed from similar applications or older databases. Disk space needs to be described on MSDN as sufficient for an empty SQL Server installation, not for the production environment.

When preparing disks, we should consider the following points:

• Using directly attached disks is a very common approach. The only possible issue is that the server itself does not have a sufficient number of controllers, so disks don't have enough space for large-scale, real-world production databases. Directly attached disks are a good option when the server should be quickly provisioned—for instance, in production halls where hardware lifetime is shortened by a dusty environment.
• Usually, the best way is to use storage area network (SAN) storage, which has a sufficient number of controllers and allows you to spread every database across more disks.
• Let its own disk be present for the tempdb database; this database is used internally by SQL Server as well as explicitly by developers as an optimization helper for complicated queries (however, this is not the best practice).
• If the server has a low amount of memory (less than 64 GB) and more memory is needed, especially for read-intensive OLTP databases, the administrator can set up a buffer pool extension (BPE). This is a file supplying more memory space for so-called clean pages. SQL Server enhances the buffer cache and stores data pages intended to be read-only from the database to this file. A best practice is to place the BPE on its own solid-state drive (SSD) disk.
• Data files and log files of databases should always be separated. SQL Server uses write-ahead logging. This means that SQL Server caches data from data files, and, at the same moment, describes to the transaction log file what will be done with the data. When data and log files are not separated, overhead could occur on the disk controller.
• Even if many databases consist just of two files (primary data file and transaction log file), larger databases can be divided into more data files. As per the performance requirements, data files can be placed to more disks. This possibly causes the need for more physical disks. Dividing a database into more files will be described in further chapters.

Software and other requirements

When installing SQL Server 2019 on Windows, only a 64-bit system is supported. Supported versions of the operating system start from Windows Server 2012 and above for non-Enterprise editions; desktop operating systems—such as Windows 8.1 or Windows 10 (including Home edition)—are supported as well.

SQL Server uses the .NET Framework for some features. The .NET Framework of versions 2.0 and 3.5 SP 1 must be present before installation.

The easiest way to check whether everything is in place is to start the SQL Server Installation Center. It starts automatically when installation media is added to the server, or it can be reached from Windows Explorer by clicking on the setup.exe file. The SQL Server Installation Center provides a central point from where to find resources about SQL Server as well as tools needed for a standalone installation, cluster installation, adding or removing SQL Server components, and so on. The installation center is divided into sections, and every section contains a list of links. The first section, when the installation center starts, is called Planning. There is a link to a tool called System Configuration Checker (SCC). This section is shown in the following screenshot:

Fig. 1.3 – SQL Server Installation Center

The SCC is a tool that checks all prerequisites needed for the successful installation of SQL Server. The following screenshot shows how it looks when every requirement is fulfilled:

Fig. 1.4 – SCC successful result

Besides the requirements, the SCC checks the overall state of the server and other prerequisites, such as whether the installation is running with administrator's privileges or whether a restart is needed.

Security accounts for SQL Server

SQL Server, as well as other technologies within the SQL Server technology set, needs to log in to the operating system. From a security point of view, it is important to set an account for every service correctly. The general recommendation is to create a login account with the weakest permissions for every service of SQL Server separately. As the installation process itself is run in the administrator's security context, the installer will set local permissions for every service account correctly during the installation. The following are the most common scenarios:

• Built-in service accounts: This type of account provides less control from the administrator's side, and it's good enough for small, standalone installations of SQL Server. In a wider domain environment, it's not recommended at all.
• Dedicated domain account: This option means that the domain administrator prepares dedicated domain accounts with regular user rights (no elevated permissions are needed or recommended), and during installation (or after the installation), prepared domain accounts are set. A big concern is that such domain accounts must fulfill security policies—namely, password expiration—and SQL Server as a machine cannot create its own password for, say, every 3 months.
• Managed service accounts: Managed service accounts are domain accounts similar to regular domain accounts. Unlike domain accounts, managed service accounts create and change their passwords without any action needed from administrators. That's why a managed service account is usually the best approach to setting security accounts for SQL Server and its services.