Installing SQL Server 2019 on Windows

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 installations on core editions of Windows servers, or sysprep installation.

Installation wizard

The installation wizard starts from the SQL Server Installation Center from the second tab, called Installation. There are several wizards (shortened), as follows:

• New SQL Server standalone installation
• Install SQL Server Reporting Services (new since SQL Server 2017; the version 2016 installation of SSRS was added to SQL Server's installation wizard directly)
• Install SQL Server Management Tools (means SSMS; beginning in version 2016, management and data tools are not installed within the SQL Server installation process; they are downloaded, installed, and versioned separately)

The first option, called New SQL Server standalone installation, is the right way to install SQL Server on a clean machine just with the operating system. When a user clicks on this option, the wizard starts. The installation wizard consists of many steps. The way through each step is this:

1. Global Rules: This is an automated installation step that checks the state of the operating system.
2. Product Updates: This step is also automated and checks if any available updates of SQL Server exist.
3. Install Setup Files: The runtime environment is prepared for further installation. This step also does not need any interaction with the administrator.
4. Install Rules: This step checks the registry keys' consistency. It also checks if the computer is a domain controller (installation of SQL Server is not allowed on domain controllers), and it also checks for firewall rules. If any of the checks fail, the setup process is corrupted, and the issues reported by Install Rules must be corrected.
5. Installation Type: On this page of the setup wizard, we can decide if we want to install a new instance of SQL Server, or add features to an existing instance of SQL Server.
6. Product Key: This step asks for a license key. If we do not have the key, we can select one of the free editions (Evaluation, Express, or Developer edition).
7. License Terms: We should read through and accept the end-user license agreement (EULA).
8. Feature Selection: The following screenshot shows the tree of SQL Server features:

Fig. 1.5 – Setup wizard step with Feature Selection

As shown in the preceding screenshot, the setup wizard offers a wide set of features to be installed at once. For administrators who already installed previous versions of SQL Server, the setup step writes an information message about SQL Server Reporting Services. This service is installed separately from the SQL Server installation.

When Installing SQL Server DE, the administrator selects the following options:

• Database Engine Services: This is the core component, DE itself.
• Optionally SQL Server Replication: For the ability to set up replication scenarios.
• Optionally Full-Text and Semantic Extractions for Search: Full text is a strong feature provided by SQL Server DE, and it could be very useful for users.

  Note

  Other options, such as Machine Learning Services (provides support to run external scripts on SQL Server), can be installed later or in a standalone separated setup. Features such as PolyBase are beyond the scope of this book.

In the bottom part of this setup step, the user decides where to place program files, not data files. Lets take a look at these steps:

1. Instance Configuration: In this step, the administrator decides if the default or named instance of SQL Server will be installed. SQL Server can run in multi-instance mode. This means that more than one instance of SQL Server could be installed on the same machine. It is a good example of a side-by-side upgrade when it is done on the same operating system.

   Another scenario for several instances could be when some application needs its own SQL Server configuration (for instance, SharePoint Server). That's why SQL Server provides an ability to install default instances and named instances. The default instance has no special additional name provided by the administrator during installation, whereas every named instance must have its own additional name that is unique on a certain computer. When connecting to the default instance, clients just use the computer's name or IP address (such as MYSQLSERVER01); when connecting to a named instance, users must provide the instance name (for example, MYSQLSERVER01\MYADDITIONALNAME).

2. Server Configuration: Server Configuration is divided into two tabs. Don't forget to go through both! The first tab, Service Accounts, is to set user accounts for SQL Server and all other installed features. The second tab is called Collation. The following two screenshots show both tabs on this installation step:

Fig. 1.6 – Server Configuration, Service Accounts tab

The preceding screenshot shows the first Service Accounts tab of the Server Configuration installation step. The tab is the place where accounts are selected for each SQL Server service. The step also contains a Grant Perform Volume Maintenance Task privilege to SQL Server Database Engine Service checkbox. If the checkbox is checked, the service account use by SQL Server will be allowed to grow data files without the need to fill newly obtained space in the files with zeros. This privilege speeds up the growth operations of data files. Lets look at the Collation tab in the following screenshot:

Fig. 1.7 – Server Configuration, Collation tab

The preceding screenshot shows the Collation tab. The Collation setting defines which code page, sort rules, and case sensitivity will be used as the server's default way of handling string characters. This server default is inherited by databases as a database default collation, and every character column in the database has its own collation inherited from the database default collation. On the database as well as on the column level, the collation setting could be overridden, but it's not a good practice. It is crucial for the proper working of SQL Server to set the server default collation correctly. It's not simple to say which collation is the correct one. The only idea is that, if you're not sure, a better approach is to use an American National Standards Institute (ANSI)-compatible collation with case insensitivity in a combination of unicode SQL Server data types such as nchar or nvarchar.

Note

Collations contain only a configuration value that cannot easily be changed after installation! When mismatched, it leads to a reinstalling of system databases.

The next steps could vary—their set and order depend on features selected in the Feature Selection step. For our database administration purposes, the Database Engine Configuration step is the most important one. In this step, all crucial configurations are done by an administrator. As shown in the following screenshot, the step is divided into tabs (don't forget to go through all of them!):

Fig. 1.8 – Database Engine Configuration

The preceding screenshot shows the Database Engine Configuration step of the SQL Server installation wizard. Let's look at the different tabs present on the wizard, as follows:

• Server Configuration contains basic settings for SQL Server security from a client's perspective. The first setting is Authentication Mode. It has two options, as follows:

  a) Windows authentication mode: In this mode, logins to SQL Server can be established in a trusted way only. This means that only logins created from Windows (domain or local accounts of groups) can connect to SQL Server.

  b) Mixed mode: For cases when every user comes with its domain identity, SQL Server can manage Standard logins or SQL logins—logins with passwords managed directly by SQL Server. This was considered a minor security risk, but since SQL Server 2005 (this was the first version of SQL Server able to consume Group Policy Objects (GPOs)), this is not a problem anymore.

  When the administrator selects Mixed mode, they must provide a strong password for standard system SQL Server login called sa. This is the standard administrator login on SQL Server.

  Authentication mode is the configuration value that can be changed later during a SQL Server instance's lifetime. Still, it needs restarting, so it's a better approach to set it directly during installation.

  In the last part of this tab is a list of sysadmin server role members of the SQL Server instance being installed. Add yourselves to the list and add other users to the list of principals who will have administrator access to the SQL Server.

• Data directories tab is important as well. It's a set of paths to the filesystem where system databases, user databases, and backups are placed when the user does not provide their actual paths, listed as follows:

  a) Data root directory: This is the base path to all data files (with .mdf or .ndf extensions). All system databases are placed on this path (with the exception of the tempdb database). It is not recommended to use the default path to the program files, as was explained in the Planning disk capacity section; the best practice is to have a disk prepared just for data files.

  b) User database directory: This is the default base directory for user database data files. For certain data files, this path can be overridden.

  c) User database log directory: In this, all log files (with the .ldf extension) of every database are placed. Never mix data and log files in the same place.

  Users, as well as system databases, could be moved to different locations. 

• TempDB pre-calculates the best configuration settings for the tempdb database. The tempdb database has a very special position in SQL Server, as well as for developers using it in some optimization tasks. The optimized execution of the tempdb database roughly depends on the following:

  a) Number of data files

  b) Their location

  c) Their symmetric growth

  The number of data files is calculated from the number of CPUs. A best practice is to have 1/4 to 1/2 data files to the number of CPUs (even logical CPUs). The best location of data files is on a fast separate disk. In the case of tempdb failure due to a disk failure, tempdb is regenerated every time SQL Server starts.

  The symmetric growth of all data files is carried out by the SQL Server engine automatically. Unlike the prior versions that had trace flags set in startup parameters, this is not needed from SQL Server 2016.

• MaxDOP allows us to limit the maximum number of CPUs used for parallelism at the instance level. This configuration value can be adjusted later during the instance's lifetime, without the need to restart the service. We can leave MaxDOP's default value as 0. Still, SQL Server tends to overutilize CPUs in highly parallelized queries, which can lead to inaccessible CPUs for other tasks on SQL Server (this situation is known as SOS_SCHEDULER_YIELD wait). MaxDOP is also configurable at the database level, but this configuration is not part of the installation process.
• The Memory tab is new for the SQL Server 2019 installation wizard, which is why it is also shown in the preceding screenshot. Even if we can set minimum and maximum memory consumption for our instance of SQL Server later, the installation wizard helps us to estimate the right amount of maximum memory. We can select between the following two options:

  a) Recommended: The estimation of Max. Server Memory (MB) configuration. The max. server memory value limits the amount of memory used by SQL Server. Using the Recommended option also allows us to adjust Min. Server Memory (MB). This is the amount of memory that, once consumed by SQL Server, is not brought back to the operating system.

  b) Default: If the max. server memory estimation offered by the installation wizard is not used, max. server memory stays at the default value, which is unlimited.

• FILESTREAM: The FILESTREAM is a type of storage for binary data such as documents or pictures saved in the database. If you have no idea about saving FILESTREAM data at the moment of installation, the FILESTREAM should remain disabled. It can be enabled and configured later, without the need to restart SQL Server.

  After the database engine configuration, additional wizard steps could occur, depending on other features selected in Feature Selection.

  Note

  Maybe some readers are concerned about where the SQL Server Agent configuration is. SQL Server Agent doesn't have any special settings in the installation process, and its installation is automatically done along with every instance of database engine installation.

• Ready to install: This step of the installation wizard is basically the summary of selected options for review before the installation begins, but this step also contains a text field called Configuration file path. This text field contains a complete path to the configuration file. The file is very useful as a template when more instances of SQL Server need to be installed.

The installation wizard is almost complete now, and the setup operation starts and shows its progress.

Installing SQL Server from the command line

Installing SQL Server directly from the command line is possible, but when searching the Install SQL Server from the Command Prompt topic on MSDN, the user will obtain a huge set of options that need to be added to the Command Prompt (or to the .bat file) directly. This leads to a big risk of misspellings and other mistakes.

A better approach is to use configuration files for Command Prompt installations. This approach contains the same options as Command Prompt, but we can find very good working examples from any setup already run from the wizard. When the wizard setup finishes, it leaves the setup log and configuration on disk. If SQL Server is installed in the default location, the path is C:\Program Files\Microsoft SQL Server\150\Setup Bootstrap\Log. In this location is a file called Summary.txt that contains the actual path to the ConfigurationFile.ini file. The configuration file can be copied and adjusted as needed and then run using the following command from Command Prompt:

setup.exe /ConfigurationFile=<path to my config file>.ini /IACCEPTLICENCETERMS

The SQL Server installation wizard allows you to prepare a configuration file without installing. When the administrator goes through the wizard, everything is saved in the newly created configuration file. In the summary step of the wizard, the path to the configuration file is shown. So, the administrator can cancel the wizard without the actual installation of SQL Server.

Note

ConfigurationFile could be edited. However, we can set different instance names or service account names, for instance, but it is still needed to add the /IACCEPTLICENCETERMS command parameter to the command line.

Checking the completed installation

We have taken all the necessary steps to install a standalone instance of SQL Server DE. The next step is to check if the installation was successful and if the instance is up and running.

If any error occurs during installation, additional diagnostics are needed. Every single task of the setup process is described in the setup log (in the case of the default installation path, the log is placed on the C:\Program Files\Microsoft SQL Server\150\Setup Bootstrap\Log path).

However, it is still a good practice to check whether everything works as expected. Those simple post-installation checks could be done using Sql Server Configuration Manager, as illustrated in the following screenshot:

Fig. 1.9 – Sql Server Configuration Manager

Sql Server Configuration Manager is the only visual client tool actually installed in the SQL Server setup. Configuration Manager is a snap-in to Microsoft Management Console and consists of two main sections, as follows:

• SQL Server Services: When selected in the left pane, the right detail pane shows every SQL Server service or feature installed with its Name, State, Start Mode, and Log On As account. These settings can be changed by right-clicking on a certain row and selecting Properties from the pop-up menu. The Modal Properties dialog appears, and we can go through it to correct any setting as needed.
• SQL Server Network Configuration (32-bit): In this section, administrators view a list of instances (the MSSQLSERVER instance name seen in the preceding screenshot is the internal name for the default instance of SQL Server), and when any instance on the left is clicked on, a list of network protocols appears in the right pane.

There are also other nodes in the tree shown in the left pane of Sql Server Configuration Manager such as SQL Native Client configuration, which provides the ability to set client aliases for SQL Server instances (for example, when SQL Server is accessible only via its IP address, which is almost non-readable to users). Still, the preceding two sections are the most important ones.

SQL Server services configuration node

The administrator can call for a pop-up menu from every record shown in the right pane. Special attention should be focused on SQL Server itself and SQL Server Agent.

SQL Server may be shown in several records because every instance has its own configuration, so the first good thing is to select the right record. The following screenshot shows the Properties dialog box:

Fig. 1.10 – SQL Server instance properties

As shown in the preceding screenshot, the Properties dialog box allows you to set the following:

• Log On tab: The context of the Windows account that will be used by the instance to log in to the operating system. This configuration needs restarting if changed.
• Service tab: The only setting enabled on this tab is Startup mode, which should be set to Automatic.
• FILESTREAM tab: This tab contains FILESTREAM settings. As described earlier, FILESTREAM is a special kind of storage for binary data such as pictures or documents stored in relational data directly to a database. From an administrator's point of view, FILESTREAM must be enabled for at least T-SQL Access. When enabled, databases can contain FILESTREAM file groups, which are actual representations of the binary storage. There's an enhancement called File Tables for which the second two textboxes (allow for I/O... and enable remote clients...) must be switched on.
• Startup Parameters tab: This tab contains three startup parameters as default, as follows:

  a) d: The location of the primary data file of the database master (must be reconfigured when the master database is moved).

  b) l: The location of the log file of the database master (must be reconfigured when the master database is moved).

  c) e: The default path for error logs written by SQL Server.

  d) Additional parameters such trace flags and others can be added if needed.

• AlwaysOn High Availability tab: AlwaysOn is an advanced concept of data availability and reliability built on top of Microsoft Cluster Service (MSCS). When certain instances attend to the AlwaysOn group, it must be enabled on this tab. MSCS must already be present before this configuration is done.
• Advanced tab: This tab actually has no advanced settings, just error reporting and user feedback to Microsoft.

Special attention should be given to SQL Server Agent. SQL Server Agent is installed with every single instance of SQL Server. In other words, every instance of SQL Server has its own SQL Server Agent instance. Immediately after installation, SQL Server Agent is set to Manual Startup mode, which is not good enough for production environments.

That's why one of the first post-installation configurations should be to change SQL Server Agent's startup mode to Automatic. This is because the SQL Server Agent is an invaluable service for a lot of regular administrator tasks as well as automated tasks done by SQL Server itself (for example, data collection, strong diagnostics tool, and collecting performance statistics using SQL Server Agent jobs).

Understanding the SQL Server network configuration node

SQL Server communicates with clients on its own network application protocol called Tabular Data Stream (TDS). Under this network application layer, TCP/IP and Named Pipes (now deprecated) network protocols are supported. The third option, called Shared Memory, is always enabled and allows communication between server and client when the client is running locally on the same machine as SQL Server.

SQL Server supports both 32-bit and 64-bit protocols, so configuration for both modes is the same. Under the SQL Server Network Configuration node (even if it's the 32-bit node), network protocols for every instance of SQL Server already installed on the machine are placed. The administrator selects certain instances (for example, Protocols for MSSQLSERVER, which is the default instance), and in the right pane of the Sql Server Configuration Manager, selects the property window for certain network protocols by right-clicking on Properties.

The most complex configuration has to be made on the TCP/IP protocol. When SQL Server 2019 is installed, the protocol is enabled, so the administrator just checks whether the proper TCP ports are used. The default TCP port used for SQL Server communication is port number 1433. For additional named instances, ports starting with numbers 1450, 1451, or similar are often used. The ability and the port number have to be set for every variant of the IP address of every network interface.

After this configuration is done, the instance of SQL Server needs to be restarted.

Testing connection to a fresh SQL Server

As mentioned earlier, SQL Server does not contain a client management toolset in its installation. It's a good idea to install SQL Server Management Studio directly on the server where the SQL Server service is already running because a lot of the administrator's tasks will be done directly on the server, but for a quick check whether SQL Server is accessible to clients, Command Prompt can be used. Its name is sqlcmd , and it's the only client tool installed with SQL Server directly. This tool is very useful in the following scenarios:

• When SQL Server Management Studio is not present or cannot be used (for example, when restoring the master database)
• When the Express edition of SQL Server was installed, and SQL Server Agent cannot be used (when planning regular tasks, it can be done by PowerShell or by sqlcmd in conjunction with Windows Task Scheduler)

The simplest way to use sqlcmd is shown in the following code example:

sqlcmd

When running sqlcmd as shown in the preceding code example, it tries to connect the local default instance of SQL Server using the current user's Windows account. When successfully connected, rows in the Command Prompt window start to be numbered.

A better approach is to call sqlcmd with parameters precisely set, as follows:

sqlcmd -E -S localhost

In a domain user context or with a SQL login context, you would run the following code:

sqlcmd -U <user name> -P <password> -S localhost

Let me elaborate on each of the parameters, as follows:

• The E parameter (beware that all parameters of all command-line tools provided by SQL Server are case-sensitive) says to the connection that Windows login context of the user currently logged in the desktop will be used.
• The U and P parameters are used when the user wants to connect via a mixed Authentication mode of SQL Server. Then, the user and password created on the SQL Server are used, not the Windows identity.
• The S parameter is used for the name of the server. If connected locally on a default instance of SQL Server, shortcuts such as . or (localhost) could be used.

All the preceding examples start the sqlcmd tool in interactive mode. When successfully connected, rows start numbering, and the user can start to write queries. The GO keyword must follow every query. This keyword (sometimes called batch terminator) causes the text written to the console to be sent to SQL Server and then processed.

Results returned back to the console are not so readable that the sqlcmd could be started with the command parameter, o, followed by the path to the output file. The output file is just a text file catching all results from all queries sent by the user in the session.

When the user wants to run sqlcmd in unattended mode, the i parameter followed by the path to the input file may also be very useful. A complete example is shown in the following snippet:

--   content of demo.sql file

use master

go

select @@version as VersionOfMySQL

go

The first piece of the snippet shows the correctly created input file (for example, demo.sql).

The use master line establishes the correct database context in the connection, and it is highly recommended to never commit this row because very often, the database context is not the default database context set for login.

The third line is just an example of doing something meaningful.

When an administrator wants to run a script file like this, they can add the following command to Command Prompt:

sqlcmd -E -S (localhost) -i "c:\demo.sql" -o "c:\demo_output.txt"

The command will run, and it will save all results (even if an error occurs) to the file called demo_output.txt.

There are more useful command parameters for sqlcmd , but this set, especially the first three examples, is sufficient to test an instance's accessibility locally.

For remote testing of accessibility, a very common way is to use SQL Server Management Studio. Common issues (followed by Error No. 40 - Network Related Error) are as follows:

• SQL Server instance is not running: In Sql Server Configuration Manager, this error is seen if the service is running or not. When it's not running, we can try to start it up manually and diagnose additional errors.
• TCP/IP protocol is disabled: This issue may be corrected by Sql Server Configuration Manager (requires restart after reconfiguring).
• Other than default TCP port number is used: This can be corrected on the user's side by adding the port number after the server name (for example, MYSQLSERVER:12345).
• Firewall rules are not set: This must be resolved on the firewall's side by enabling certain ports for communication.