Then enter your SQL product key. If you don’t have it, select the free edition (like Developer), but remember that with the Developer edition you can only develop or test software (don’t use in production tasks).

In the?License Terms?step, accept the license agreement.

Installing SQL Server 2019 Components

In this step, you are prompted to install different SQL Server components. Let’s see what they are and which of them to install:

• Database Engine Services?is a basic SQL Server engine that must be installed.
• SQL Server Replication?is the replication services. This feature is used quite often, so if you are not sure whether you need it, it is better to check it for the installation.
• Machine Learning Services and Language Extensions?are the services that run an R/Python/Java code in the SQL Server context. It is necessary if you are going to use Machine Learning.
• Full-Text and Semantic Extractions for Search?– you will need this feature if you need a full-text search technology or semantic search in the documents (for example, in .docx). In case of semantic search, you will also need FILESTREAM. We will tell about this feature later.
• Data Quality Services?are the services to correct or validate data. If you are not sure if you need DQS, it is better not to install it.
• PolyBase Query Service For External Data?allows access to external data, for example located on another SQL Server or in Oracle Database. Java connector for HDFS data sources is related to the PolyBase technology and if you want to use HDFS technology, install it.
• Analysis Services?is also known as SSAS. The technology is used for business reporting (BI) and OLAP in large companies.

Then move on to the list of Shared Features (functions that apply to the entire server, and not to a specific instance).

1. Machine Learning Server (Standalone)?is the same as Machine Learning Services and Language Extensions, but it can be installed without the SQL Server engine.
2. Data Quality Client?is a standalone DQS version.
3. Client Tools Connectivity?includes ODBC, OLE DB and some other libraries. It is recommended to install them.
4. Integration Services?are data integration services, also known as SSIS. It is used in data ETL (Extract, Transform, Load). SSIS are necessary if you want to import data automatically and change them while importing. Scale Out Master/Worker are used to scale SSIS operation. If you are not sure if you need them, don’t check the feature.
5. Client Tools Backwards Compatibility?are legacy DMVs and system procedures. These are recommended for installation.
6. Client Tools SDK?is a developer tool kit. If you are not sure you need it, don’t install the SDK.
7. Distributed Replay Controller/Client?duplicates and improves SQL Server Profiler features. Distributed Replay services simulate load for different performance tests.
8. SQL Client Connectivity SDK?is as ODBC/OLE DB SDK for developers.
9. Master Data Services?is a Microsoft Power BI component. It is used to analyze, validate, integrate or correct data.

Some of the features (like Java connector for HDFS data sources) may not be available in previous SQL Server versions.

In the same step, you can specify a directory for SQL Server files. Unless you have a compelling reason to change it, leave the default (C:\Program Files\Microsoft SQL Server\).

After you have selected MSSQL features you need, the installation wizard will check their compatibility with your system, and if there are no problems, this step will be completed automatically.

SQL Server Instance Name

You can leave the Default Instance option. Then the name of your instance will be MSSQLSERVER. If you select?Named Instance, you can configure your SQL Server name. In my case, I’ll name my instance?DEV. It is recommended to enter the same Instance ID as its name to avoid confusion.

In the Installed instances list, MSSQL instances installed on the server are shown. I have got one.

Configuring SQL Server Service Settings

On the Service Accounts tab, specify the accounts under which SQL Server services will run on the host. The best practice is to use MSA (Managed Service Accounts) or gMSA?(Group Managed Service Accounts) as they are the most secure ones. I will use a regular domain user account.

Set the Startup Type?to?Automatic for the?SQL Server Agent?or you will have to run the agent manually.

Also, you can set?IFI (Instant File Initialization)?during the server installation starting from SQL Server 2016. It is called “Grant Perform Volume Maintenance Task privilege to SQL Server Database Engine” in the installation wizard. If you enable it, old data won’t overwritten with zeroes if you:

• Create a database;
• Add data to data or log files;
• Expand the size of existing files (including auto-growth operations);
• Recover a database or file group.

It will speed up file initialization, but affects security since old data are not zeroed (old information in the files may be partially available).

I recommend to enable the setting if the data leak threat is low.

In the next step, you must select the?Collation.

Collation is the SQL Server encoding. This parameter sets page encoding, sorting rules, char/varchar encoding and other language-related settings.

During server installation, you select Collation for the whole SQL Server. After the installation, you will be able to edit the setting, but it is rather hard to do it, so it is better to select a suitable Collation at once.

If you have data in English only, you may select?SQL_Latin1_General_CP1_CI_AS.

If you are going to use SQL Server in productive tasks, see the Collation section of the documentation, since it is an important setting though it can be set for each database separately.

Configure Database Engine Options on SQL Server

In the?Database Engine Configuration?step, 6 tabs are available, let’s look at them in turn:

In Server Configuration, select Authentication Mode and specify the SQL Server administrator account.

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

• Only users of your domain or computer running Windows will be able to authenticate using Windows authentication.
• In the Mixed mode, you will also be able to authenticate using your SQL Server account.

Microsoft recommends using Windows authentication as the most secure method, however, in practice you are likely to log on to your server from other apps as well. For example, from those written on Java. Then you will have to use SQL Server authentication.

If you are sure that your users will only log on from Windows computers or apps that support Windows authentication, then select Windows authentication mode.

In my case, I check Mixed mode. In this mode, you will have to specify a sa user password and select a Windows account that will have admin privileges.

In the Data Directories tab, select the directory where your SQL Server will store database files and transaction logs.

It is better to use a separate RAID drive to store your data. A disk subsystem is critically important for an SQL Server performance, so use the best available variant of data storage, for example, NAS or a local RAID of fast disks.

Keeping all directories (system databases, user database logs, backups) in different storages is considered a good practice. Thus, you will get maximum SQL Server performance on the data storage level.

In my case, I will specify a separate RAID 1 drive for all folders.

In the?TempDB tab, you can configure tempdb settings. Its correct configuration is important for your server performance, since this database is involved almost in all operations with data.

• Number of files?is the number of data files for tempdb. You must specify the number of files depending on the number of CPU cores. A good practice is to specify the number of files equal to the number of CPU cores divided by 2. It means that 16 files are recommended for a 32-core server. Also, it is not recommended to specify less than 8 files independent of the number of cores. It is necessary to avoid problems described here:?https://support.microsoft.com/en-us/kb/2154845?.
• Initial size?is the initial size of tempdb data files. After each server restart, the tempdb size will be reset to its initial size. It is recommended to specify the data file size depending on the planned load. If you cannot estimate it, leave the value at 8 MB. If you allocate a special array/drive for your tembdb files (we will discuss it below), it is better to specify the file size so that they occupy the whole disk to avoid constant file growth.
• Autogrowth?is the step of tempdb file incremental growth. This parameter must be set depending on the initial size. Leave it 64 MB if you cannot estimate your future load. Note that if IFI (Instant File Initialization) is enabled, the time to wait for file growth block will be significantly less. It is not recommended to set a large value of the incremental size, since it will result it considerable delays when increasing the file size.
• Data Directories?are the directories where tempdb files will be located. If you specify multiple directories, the files will be placed following the Round-robin algorithm, i. e., in a cycle. In other words, if you specify 4 directories, the data files will be distributed equally across all directories. The best practice is to add different disk arrays for your data files.
• TempDb Log file: Initial size/Autogrowth?allows to configure the initial size and step of the incremental growth of your tempdb log file. The same rules apply as for tempdb data files.
• Log Directory?is the directory where the tempdb log file is stored. There is only 1 log file independent of the number of data files you have, and only 1 directory is specified. If possible, allocate a separate array for the log file as well.

The?MaxDOP tab.

MaxDOP in an SQL Server setting that is responsible for running parallel queries and parallelism degree. In order your SQL Server to use all CPU cores in processing parallel plans, set 0 as the MaxDOP value. If you want to disable parallel queries on some reason, set 1 as the value. To gain maximum performance, configure MaxDOP according to the rules in the table below (https://go.microsoft.com/fwlink/?linkid=2084761):

In my case, I will set it to 0. This will give me maximum performance to run parallel queries, but it can also result in delays since other processes will have to wait till the current process is over, because all processor cores will be busy with executing the current query.

I still recommend to follow the rules listed in the table above for a server with a high workload. Also see the documentation following the link above.

In the?Memory?tab, specify the minimum and maximum RAM size your SQL Server will use. Since it is quite hard to estimate the right size for a server, it is recommended to dedicate 80-85% of the whole server RAM to your SQL Server. To know the real size of RAM used, you need to monitor RAM usage 24 hours a day using special DMVs (Dynamic Management View). You can estimate a real RAM usage only if you have these data.

I will leave the default values (min 0 and max 2147483647 MB).

In the FILESTREAM tab, you can enable FILESTREAM feature. It allows to store binary files on the file system and provides access to them through SQL. If you are not sure if you want to work with binary data on the SQL level, leave FILESTREAM disabled.

The Feature Configuration Rules step will be done automatically. Read the information in the Ready to Install section and click Install.

Thus, you have completed basic SQL Server 2019 Enterprise installation. In the following article we’ll look at the main ways to analyze performance and problems in SQL Server.