MS SQL Server 2019 Installation Guide: basic settings and recommendations

In this article we will step by step cover the installation of Microsoft SQL Server 2019 with a description of all options, components, current recommendations and best practices.

MS SQL Server is the leading DBMS (Relational Database Management System) and also the main competitor of Oracle Database in the corporate segment. In CIS MSSQL is most often used for own application software development and for 1C.

MS SQL Server 2019 editions

There are 5 issues (editions) of MSSQL 2019 in total:

1. Express is a free edition for use. The functionality is quite limited, the most tangible limitation of the Express version is the maximum base size of 10 GB. This edition is suitable for small projects such as student work or SQL/T-SQL learning.
2. Standard is a full-fledged paid edition, but many functions still do not. The maximum amount of RAM that SQL Server can use is 128 GB, and there are no AlwaysOn accessibility groups or other components. Standard is designed for applications in small organizations.
3. Enterprise includes all possible functions and components, there are no limitations. Enterprise edition is usually used by large corporations or companies that need the functionality of this version.
4. Developer edition as well as Enterprise edition has no limitations and can be used for free, but it can be used only for application development and testing.
5. Web edition is almost the same as standard, except for stronger limitations in functionality and correspondingly lower licensing costs.

Features of SQL Server licensing

MS SQL Server is licensed for 2 models:

• PER CORE – licenses MSSQL on server cores
• SERVER + CAL – a license for the entire server and for each user who will work with the server

Enterprise edition can only be licensed by PER CORE type

Also in MSSQL Server 2019 there is a new feature for licensing containers, virtual machines and Big Data Clusters.

For more information on SQL Server licensing, please see a separate article.

Getting started with SQL Server installation

In this article we will install MS SQL Server 2019 Enterprise Edition on Windows Server 2019.

Note. In SQL Server 2019 there is full support for Linux, and respectively Docker and Kubernetes.

• Download and unpack the SQL Server 2019 installation image. Run setup.exe;
• Since in this article we are going to install a normal isolated instance, on the Installation tab select “New SQL Server stand-alone installation”.

In SQL Server installer you can perform many other actions: update an old instance, fix a broken one and some other things.

SQL Server update options during installation

At this point, you can enable Windows Update to search for updates. It is up to you to enable this option or not. It all depends on your update scheduling and server resilience requirements. If you do not have a clear service plan for your servers, it is best to leave this option enabled.

At this step, you can see such a mistake:
Error 0x80244022: Exception from HRESULT: 0x80244022

This is due to problems with the Windows Update service.

Press Next.

The Install Setup Files step will happen automatically. It will prepare the files for installation.

Install Rules will also pass automatically if the installer does not find problems that need to be resolved before installing MSSQL (for example, reboot your computer or if your version of Windows is incompatible with the version of SQL Server).

Type of installation of SQL Server

At this step, you can choose to install a new instance or add functionality to an already installed instance. In our case, choose “Perform a new installation”. Choose the type of sql server installation – new installation

Now you have to enter the product key. If you do not have a key, select Free edition (e.g. Developer), but keep in mind that with the Developer edition you are only allowed to develop and test software, but not to use the server in a productive environment. enter the installation key of the sql server

At the step License Terms we accept the license agreement.

Components of SQL Server 2019: what you need, what you need to install

At this stage you are offered to install various SQL Server components. Let’s take a closer look at them, see what you need to install in different situations:

• Database Engine Services is the main engine of SQL Server. It is obligatory to install.
• SQL Server Replication – replication services. The component is used quite often, so if you are not sure if you need it, it is better to mark it for installation.
• Machine Learning Services and Language Extensions – services to execute R/Python/Java code in the SQL Server context. This is necessary if you are going to do Machine Learning.
• Full-Text and Semantic Extractions for Search – This component is necessary if you need a full-text search technology or a semantic search in documents (e.g. docx). In the case of semantic document searches, you will also need FILESTREAM, about it below.
• Data Quality Services – services for correcting and validating data. If you are not sure if you need DQS, it is better not to install it.
• PolyBase Query Service For External Data – technology to access external data, for example, on another SQL Server or in Oracle Database. Java connector for HDFS data sources refers to PolyBase technology and is needed in case you want to work with HDFS technology.
• Analysis Services – also known as SSAS. Technology for business reports (BI) and work with OLAP. Used in large companies for reporting.

Next, we move on to the list of Shared Features (features that apply to the entire server, not to a particular instance).

• Machine Learning Server (Standalone) – the same as the Machine Learning Services and Language Extensions, but with the ability to install without the SQL Server engine itself.
• Data Quality Client – the same as DQS, only standalone.
• Client Tools Connectivity – ODBC, OLE DB and some other libraries. We recommend to set necessarily.
• Integration Services – data integration services, also known as SSIS. Technology for ETL (Extract, Transform, Load) data. SSIS is needed if you want to automate data import and change it during the import process. Scale Out Master/Worker is needed to scale the operation of SSIS. If you are not sure if you need it, do not mark it.
• Client Tools Backwards Compatibility – Outdated DMV and system procedures. I recommend you put them in.
• Client Tools SDK – A resource pack for developers. You may not want to install it if you are not sure if you need it.
• Distributed Replay Controller/Client – Repeats and improves the functionality of SQL Server Profiler. Distributed Replay services are needed for load modeling and performance testing of all kinds.
• SQL Client Connectivity SDK – ODBC/OLE DB SDK for developers.
• Master Data Services – component from Microsoft Power BI. Needed for data analysis, validation, integration and correction.

Some of these components (such as Java connector for HDFS data sources) may not be available in older versions of SQL Server.

Just below, on the same step, you can specify a directory for SQL Server files. If you have no good reason to change it, then leave the default one (C:\Program Files\Microsoft SQL Server\).

Once you have selected the MSSQL components you need, the installer checks the compatibility of the components with your system, and if there are no problems, this step will go automatically.

Configure SQL Server instance naming

You can leave the Default Instance parameter, in which case your instance name will be MSSQLSERVER. When you select Named Instance, you specify the name of your SQL Server instance. In my case, I will name the DEV instance. It is recommended that you set the Instance ID to the same name as the instance name to avoid confusion.

The Installed instances displays the MSSQL instance installed on the server, I already have one.

Configuring SQL Server service parameters, encoding

On the Service Accounts tab, specify the accounts from under which the SQL Server services on the host will run. It is good practice to use MSA (Managed Service Accounts) and gMSA (Group Managed Service Accounts) technologies as the most secure. I will use a normal domain account.

Select the Startup Type field in Automatic from the SQL Server Agent, otherwise the agent will have to be run manually.

Also, starting with SQL Server 2016 it is now possible to set the IFI (Instant File Initialization) parameter when installing the server. In installer it is called “Grant Perform Volume Maintenance Task privilege to SQL Server Database Engine”. Enabling it means that old data will not be overwritten with zeros:

• Creating a database;
• Adding data to data files or log files;
• Increasing the size of existing files (including auto zoom operations);
• Restore database/file group.

This speeds up the process of initializing files, but reduces security by not overwriting old data with zeros, so the old information contained in these files may be partially available.

I recommend enabling this option if the risk of data leakage is not significant.

On the next step, you must select Collation.

Roughly speaking, Collation is a configuration of SQL Server encoding. This parameter sets page encoding, sorting rules, char/varchar encoding and other language settings.

When you install the server, you select Collation for the whole SQL Server. After installation, you can change this parameter, but it will not be easy to do, so you need to immediately select the right Collation for your tasks.

If you plan to use SQL Server in combat conditions, read the documentation on choosing Collation, as it is an important parameter, although it can be set for a specific database.

Configure Database Engine in SQL Server

On the Database Engine Configuration step 6 tabs are available, let’s start in order:

In Server Configuration you must select Authentication Mode and specify an account for the SQL Server administrator.

You have 2 modes to choose from: Windows authentication mode and Mixed mode.

• With Windows authentication, only users on your domain or Windows computer can authenticate.
  In Mixed mode, in addition to windows authentication, you will be able to authenticate using the credentials of SQL Server itself.
• Microsoft recommends that you use Windows Authentication as the most secure, but in practice you will most likely need to login to a server from other applications. For example, applications that are written in java, in which case you can’t do without SQL Server authentication.

If you are sure that your users will only login from Windows computers and applications that support Windows authentication, then choose Windows authentication mode.

In my case, I will set the Mixed mode. In this mode, you will need to prescribe a password from user sa and select a Windows account that has administrative privileges.

On the Data Directories tab, you must select the directory in which SQL Server will store the database and transaction logs.

For data, it is best to allocate a separate RAID array. The disk subsystem is critical to SQL Server performance, so you must choose the best storage option available to you, whether it is NAS or local RAID from fast disks.

It is good practice to separate all directories (system databases, user databases, user database logs, backups) into different storage. In this way you will get the maximum performance from SQL Server at the storage level.

In my case I will specify a separate drive with RAID 1 for all directories.

The TempDB tab is used to configure parameters for the tempdb base. Its correct configuration is important for server performance, as this database is involved in almost all data operations.

• Number of files is the number of data files for tempdb. You need to specify the number of files depending on the processor cores. It is good practice to set the number of files to the number of processor cores divided by 2. That is 16 files are recommended for 32 cores of your server. Also regardless of the number of cores it is not recommended to set less than 8 files, it is necessary to avoid the problems described here https://support.microsoft.com/en-us/kb/2154845 .
• Initial size is the initial size of tempdb data files. Each time you reboot your server, the tempdb size will be reset to its initial size. It is recommended to specify the size of the data files depending on the planned load. If you cannot plan your future load, then leave 8 MB. If you allocate a separate array/disk for the tempdb files (as discussed below), it is best to specify a file size that will fill the disk completely to avoid constant file enlargement operations.
• Autogrowth is the step to enlarge tempdb files. The size should be set according to the initial size. Leave 64MB if you cannot plan the load. Note that if IFI (Instant File Initialization) is enabled, the waiting time for locks on file extension will be much less. It is not recommended to set the step size too large, as this will cause significant delays when increasing the file size.
• Data Directories – directories for placing tempdb data files. If you specify several directories, the files will be placed according to the Round-robin algorithm, i.e. cyclically. Roughly speaking, if you specify, for example, 4 directories, the data files will be distributed equally across all directories. It is good practice to add different disk arrays for data files.
• TempDb Log file: Initial size / Autogrowth – setting the initial size and step of increasing the tempdb log file. You should follow the same rules as for tempdb data files.
• Log Directory – directory for storing the tempdb log file. Log file is only 1, regardless of the number of data files, you should specify only 1 directory. If possible, the log file should also have a separate array.

Tab MaxDOP

MaxDOP is a parameter of SQL Server that is responsible for parallel execution of queries and, accordingly, the degree of parallelism. For SQL Server to use all cores of the processor to process parallel plans, set 0 as the MaxDOP value. If for some reason you want to disable parallel execution of queries, set 1 as a value.

Tab Memory

Tab Memory – you must specify the minimum and maximum amount of RAM that will use SQL Server. Since it is difficult to predict the required amount for the server, it is recommended to allocate SQL Server 80-85% of the total amount of RAM. In order to know the real amount of RAM used, you need to monitor RAM consumption 24 hours a day through special DMV (Dynamic Management View) and monitor RAM consumption peaks. Only with this information can you predict the actual RAM consumption.

Tab FILESTREAM

Tab FILESTREAM – enabling FILESTREAM technology. It allows you to store binary files on the file system and provides access to them via SQL. If you are not sure that you want to work with binary data at the SQL level, then leave FILESTREAM off.

The Feature Configuration Rules step will go automatically. Read the summary in Ready to Install and click Install.

This completes the basic installation of SQL Server 2019 Enterprise.