Preparing for installation

The previous section described the whole set of services and features contained in SQL Server. From now, we will pay attention to on-premise 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 sysprep

Edition comparison

Microsoft provides SQL Server in several editions. Each edition has its supported features and with these features 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 on Microsoft's site. The core editions are as follows:

• Enterprise edition: Intended for big enterprise environment
• Standard edition: Contains almost all services (except Master Data Services and Data Quality Services) 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 production environment
• Express edition: Express Edition of SQL Server published for free but with a lot of limitations; for example, analysis services, integration services, and SQL Server Agent are not contained in this edition

Preinstallation tasks

When planning to install SQL Server 2017, there are three important points to be considered:

• 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 give SQL Server 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:

• 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 buffer cache. Buffer cache with many other memory areas like 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 is relatively new SQL Server DE technology that was introduced with 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 capacity is set to 1/4 of maximum SQL Server memory, which means 32 GB of memory up to the regular limit. In memory area is used for in memory tables--tabular structures for extremely fast access, especially in conjunction with native compiled stored procedures. If any application supposes to use in memory technology, be aware of this extra memory need.

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 from older databases. Disk space needs described on MSDN are sufficient for empty SQL Server installation, not for production environment.

When preparing disks, we have to consider the following points:

• Using directly attached disks is very common approach. Only possible issue is that the server itself does not have a sufficient number of controllers and disks don't have enough space for large scale real-world production databases.
• The best way is to use 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 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). It is a file supplying more memory area for so called clean pages. SQL Server enhances the buffer cache and stores data pages intended to be read only from database to this file. The best practice is to place the BPE on its own SSD disk.
• Data files and log files of databases should always be separate. SQL Server uses write-ahead logging. This means that SQL Server caches data from data files and, at the moment, describes to the transaction log file what will be done with the data. When data and log files are not separate, overhead could occur on the disk controller.

Software and other requirements

When installing SQL Server 2017 on Windows, a 64-bit system is mostly preferred. Supported versions of the operating system are from Windows Server 2012 higher 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. This tool provides a central point to find resources about SQL Server as well as tools needed for 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 shown when the installation center starts is called planning. There is a link to a tool called System Configuration Checker (SCC):

SQL Server Installation Center

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

System Configuration Checker successful result

Besides the requirements, 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 need 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 for every service of SQL Server separately with the weakest permissions. As the installation process itself is run in administrator security context, the installer will set permissions for every account correctly within the installation. The following are the most used 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 bigger 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 needed) 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 three 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 live administrators. That's why a managed service account is usually the best approach to setting security accounts for SQL Server and its services.