Installing and configuring SQL Server instances and features

Installing a new instance

In this section, you learn how to begin a new SQL Server 2019 instance installation, upgrade an existing installation, or add features to an existing instance.

The instructions in this chapter are the same for the first installation or any subsequent installations, whether it is for the default or any named instances of SQL Server 2019. As opposed to an exhaustive step-by-step instruction list for installations, we’ve opted to cover the important decision points and the information you need and highlight new features from SQL Server 2019.

It’s important to note that even though you can change almost all of the decisions you make in SQL Server Setup after installation, those changes potentially require an outage or server restart. Making the proper decisions at installation time is the best way to ensure the least administrative effort. Some security and service account decisions should be changed only via the SQL Server Configuration Manager application, not through the Services console (services.msc). This guidance will be repeated elsewhere for emphasis.

We begin by going through the typical interactive installation. Later in this chapter, we go over some of the command-line installation methods that you can use to automate the installation of a SQL Server instance.

Planning for multiple SQL Server instances

You can install as many as 50 SQL Server instances on a Windows Server; obviously, we do not recommend this. In a Windows failover cluster, the maximum number of SQL Server instances is reduced by half if you’re using shared cluster drives.

Only one of the SQL Server instances on a server can be the default instance. All, or all but one, of the SQL Server instances on a SQL Server will be named instances. The default instance is reachable by connecting to the name of the Windows Server, whereas named instances require an instance name, for example, Servername\InstanceName. The SQL Browser service is required to handle traffic for named instances on the SQL Server.

For example, you can reach the default instance of a SQL Server by connecting to servername. All named instances have a unique instance name, such as servername\instancename.

Creating an Azure VM running SQL Server

You should not need to install SQL Server on new Azure VMs, because provisioning new VMs with various versions and editions of Windows Server and SQL Server are available in the Azure Marketplace. For more information about bringing your existing SQL Server licensing to Azure VMs, visit https://azure.microsoft.com/blog/easily-bring-your-sql-server-licenses-to-azure-vms/.

When manually creating a new VM with SQL Server, however, there are still some configuration choices you can make in the browser. We will walk through some of those decision points in this section, although much of this is already handled for you.

Specifically in the SQL Server settings page of the Create a virtual machine wizard, you can choose to enable Mixed Mode Authentication. If you do, you must specify a username and password, but note that this probably isn’t needed for you–administrators can and should use Azure Active Directory authentication when possible with your Azure VMs.

You can configure the Azure VM to be automatically powered down, which is very handy for rarely used VMs in your own sandbox. You can use Azure Automation to easily schedule VMs to run during pre-scheduled hours, thus lowering the monthly cost.

You can also enable Azure-specific integrations with Azure Key Vault, automatic patching, and automatic SQL backups for your new VM from the Azure Portal. Use Tags to organize your Azure infrastructure with information on environments, applications, locations, or other custom departmental metadata as desired, making it easy to monitor and manage objects in groups.

In the Review + create page, you can review the expected hourly spend and all of your Azure VM settings. Don’t miss this detail, which is similar to SQL Server setup: there is a link to download a template for automation. An Azure VM template is a .json file that stores the options needed to create Azure resources, so that you can reference it within the Azure Portal, PowerShell, the Azure CLI (-Line Interface) and others to create Azure VMs with the same choices. Use the .json file to create a new Azure VM, for example, by choosing “Create a Resource” in Azure, then “Template deployment (deploy using custom templates)”.

• You’ll find much more on Azure infrastructure, including the differences between Azure VMs, Azure SQL Database, and Azure SQL Database Managed Instances in Chapter 16 “Designing and implementing hybrid and Azure database infrastructure.”

Installing SQL Server on Windows

The rest of this chapter is dedicated to installations of SQL Server not part of a pre-made Azure Marketplace VM and apply to installation of SQL Server on any Windows Server.

While logged in as a local Windows administrator, begin by mounting the installation .iso to the Windows server. These days, this rarely involves inserting a physical disc or USB flash drive; although you can use them if necessary.

Launching SQL Server Setup

You should not run Setup with the installation media mounted over a remote network connection, via a shared remote desktop drive, or any other high-latency connection. For a faster SQL Server Setup experience, unpack the contents of the .iso file to a physical file folder local to the server, instead of mounting a file or launching Setup from a location over the network.

Start setup.exe on the SQL Server Setup media, running the program as a Windows user with administrator privileges. If AutoPlay is not turned off (it usually is), Setup.exe will start when you first mount the media or double-click to open the .iso. Instead, as a best practice, right-click Setup.exe and then, on the shortcut menu that appears, click Run As Administrator.

We’ll review here a few items (not all) in the SQL Server Installation Center worth noting before you begin an installation.

In the tab pane on the left, click Planning to open a long list of links to Microsoft documentation websites. Most helpful here might be a standalone version of the System Configuration Checker, which you run during SQL Server Setup later, but it could save you a few steps if you review it now. A link to download the Data Migration Assistant (DMA) is also present, which is a helpful Microsoft-provided tool when upgrading from prior versions of SQL Server.

On the Maintenance page, you will find the following:

• A link to launch the relatively painless Edition Upgrade Wizard. This is only for promoting your existing installation’s edition as we discussed earlier.

• The Repair feature is not a commonly used feature. Its use is necessitated by a SQL Server with a corrupted installation. You might also need to repair an instance of SQL Server when the executables, .dll files, or Registry entries have become corrupted or damaged by disk corruption, antivirus, or malicious activity. A failed SQL Server in-place upgrade or cumulative update installation might also require a Repair, which could be better than starting from scratch.

• Note that whereas removing a node from an existing SQL Server failover cluster is an option in the Maintenance page, adding a node to an existing SQL Server failover cluster is an option in the Installation page.

• On the Advanced page, there is a link to perform an installation based on a configuration file. We will discuss how to easily generate and use a configuration file later in this chapter, in the section Automating SQL Server Setup by using configuration files. If you are tasked with installing multiple SQL Servers with mostly common settings, consider this time-saving method. There are also links to wizards for advanced failover cluster installations.

• We discuss Failover Cluster Instances (FCIs) in Chapter 11, “Implementing high availability and disaster recovery.”

Windows Update in the SQL Server Setup

Since SQL Server 2012, the SQL Server installer has had the ability to patch itself while within the Setup wizard. The Product Updates page is presented after the License Terms page, and, after you accept it, it is downloaded from Windows Update (or Windows Server Update Services) and installed along with other SQL Server Setup files.

This is recommended, and so a SQL Server 2019 Setup with Internet connectivity is the easiest way to carry out the installation. This also could be described as a way to “slip-stream” updates, including hotfixes and cumulative updates, into the SQL Server installation process, eliminating these efforts post-installation.

For servers without Internet access, there are two Setup.exe parameters that support downloading these files to an accessible location and making them available to Setup. When starting Setup.exe from Windows PowerShell or the command line (you can read more about this in the next section), you should set the /UpdateEnabled parameter to FALSE to turn off the download from Windows Update. The /UpdateSource parameter can then be provided as an installation location of .exe files. Note that the /UpdateSource parameter is a folder location, not a file. You will find more on these two parameters later in the “Installing by using a configuration file” section.

Regardless, after installation is complete and before the SQL Server enters further use in your team, verify that the latest SQL Server patches have been applied. For SQL Server 2019, see the official build versions site: https://support.microsoft.com/help/4518398/sql-server-2019-build-versions.

Installing SQL Server stand-alone installation

Although what follows in this chapter is not a step-by-step walk-through, we’ll cover key new features and decision points of the New SQL Server stand-alone installation option of the SQL Server Installation Center.

Granting Perform Volume Maintenance Tasks

On the same Server Configuration page on which service accounts are set, you will see a check box labeled Grant Perform Volume Maintenance Task privilege to the SQL Server Database Engine Service. This option was added to SQL Server Setup in SQL Server 2016.

This automates what used to be a standard post-installation checklist step for SQL DBAs since Windows Server 2003. The reason to grant this permission to use instant file initialization is to speed the allocation of large database data files, which could dramatically reduce the Recovery Time Objective (RTO) capacity for disaster recovery.

This can mean the difference between hours and minutes when restoring a very large database. It can also have a positive impact when creating databases with large initial sizes, or in large autogrowth events; for example, with multiple data files in the TempDB (more on this next). It is recommended that you allow SQL Server Setup to turn on this setting.

SELECT servicename, instant_file_initialization_enabled
FROM sys.dm_server_services
WHERE filename LIKE '%sqlservr.exe%';

• For more information on instant file initialization, see Chapter 3.

Instance Collation

The Collation tab on the Server Configuration page allows you to choose a collation for the Database Engine. The collation determines how character data is stored, sorted and compared. For more information, see Chapter 7: “Understanding table features” in the section on Collation.

Initially the instance collation provided in SQL Setup is the default collation for the server’s regionalization, but you may need to change this collation based on vendor or developer specifications.

While changing the collation of a database is easy, the instance collation is important to get right at the time of SQL Server installation, as changing the instance collation is quite difficult. To change the collation of the SQL Server instance, reference this lengthy and difficult Microsoft guide: https://docs.microsoft.com/sql/relational-databases/collations/set-or-change-the-server-collation. Note that in the case of Azure SQL managed instances, you cannot change the server-level collation after it is created. For more information, visit: https://docs.microsoft.com/sql/relational-databases/collations/set-or-change-the-server-collation#setting-the-server-collation-in-managed-instance.

The server collation you set here acts as the collation for all system databases as well as the default for any newly created user databases. In SQL Server 2019 and for new application development, you may choose to take advantage of the new support for UTF-8 collations as the server default. Pictured below is the new interface in SQL Server Setup for choosing a UTF-8 collation.

Figure 4-1 The options to set a server-level Windows collation using the new UTF-8 collation options now available in SQL Server 2019 Setup.

Mixed Mode authentication

SQL Server supports two modes of authentication: Windows and SQL Authentication. One is preferable to the other, and in multiple places in this book we will emphasize the preference for Windows Authentication over SQL Authentication.

• You can read more on this topic in Chapter 12, “Administering security and permissions,” but it is important to note this decision point here.

Ideally, all authentication is made via Windows Authentication, through types of server principals called logins, that reference Windows accounts, which are ideally Active Directory domain accounts. These are created by your existing enterprise security team, which manages password policy, password resets, password expiration, and so on.

A redundant security model for connecting to SQL Server also exists within each instance: SQL Server Authenticated logins. Logins are maintained at the SQL Server level, are subject to local policy password complexity requirements, are reset/unlocked by SQL DBAs, have their own password change policy, and so forth.

Enabling Mixed Mode (SQL and Windows Authentication Mode) activates SQL Authenticated logins. It is important to note that SQL Authentication is not on by default, and isn’t the recommended method of connection. By default, the recommended Windows Authentication is turned on and cannot be turned off. When possible, applications and users should use Windows Authentication.

Enabling Mixed Mode also activates the “sa” account, which is a special built-in SQL Server Authentication that is a member of the server sysadmin role. Setup will ask for a strong password to be provided at this time.

• You can learn more about the “sa” account and server roles in Chapter 7.

If you find you have an actual need to enable SQL Server Authentication, but didn’t do this during SQL Server Setup, you can do this later on by connecting to the SQL Server instance via Object Explorer in SQL Server Management Studio. To do so, right-click the server name and then, on the shortcut menu that opens, click Properties, and then click the Security page, change to Mixed Mode. You must perform a SQL Server service restart to make this change effective.

Default settings for the TempDB database

Starting with SQL Server 2016, SQL Server Setup provides a more realistic default configuration for the number and size of TempDB data files. This has been a common to-do list for all post-installation checklists for DBAs since the early days of SQL Server.

The TempDB database page in SQL Server Setup provides not only the ability to specify the number and location of the TempDB’s data and log files, but also their initial size and autogrowth rates. The best number of TempDB data files is almost certainly greater than one, and less than or equal to the number of logical processor cores, including hyperthreading for local machines. For example, with 16 logical processors, SQL Server Setup will default the installation to have 8 TempDB data files.

Adding too many TempDB data files can degrade SQL Server performance, perhaps severely if you add too many. For example, with 20 logical processors, SQL Server Setup will still default the installation to have 8 TempDB data files. If you add 20 TempDB data files, SQL Server may struggle to respond.

• For more information on the best number of TempDB data files, see Chapter 3.

Specifying TempDB’s initial size to a larger, normal operating size is important and can improve performance after a SQL Server restart when the TempDB data files are reset to their initial size. Setup accommodates an individual TempDB data file initial size up to 256 GB. For data file initial sizes larger than 1 GB, you will be warned that SQL Server Setup can take a long time to complete if instant file initialization is not turned on.

Since SQL Server 2016, all TempDB files autogrow at the same time, keeping file sizes the same over time, which is critical to the way multiple TempDB data files are used. This is superior to the old way of ensuring TempDB data files stay the same size, using the server-level setting via server Trace Flag 1117, which applied the data file growth behavior to all databases. Trace Flag 1117 is no longer necessary.

Note also the new naming convention for the second TempDB data file and beyond: tempdb_mssql_n.ndf. A SQL Server uninstallation will automatically clean up TempDB data files with this naming convention—for this reason, we recommend that you follow this naming convention for TempDB data files.

• TempDB is discussed in greater detail in Chapter 3.

Default settings for MaxDOP

New in SQL Server 2019 are defaults for the configuration of the server-wide Maximum Degrees of Parallelism setting, included on the Database Engine Configuration page under the new MaxDOP tab.

Similar to how new TempDB defaults since SQL Server 2016 are dependent on the detected processors, a suggested default MaxDOP is also configured based on the number of logical processors. For many servers with 16 or fewer virtual processor cores, the default is the same as the number of the cores, effectively the same as MaxDOP setting = 0, which allows for unlimited parallelism.

For example, with 8 logical processors, SQL Server Setup will default the installation to use MaxDOP 8, as pictured in Figure 4-2. With over 16 logical processors, SQL Server Setup may default to half the number of logical processors—at most 16. For example, with 20 logical processors, SQL Server Setup will default the installation to use MaxDOP 10. For more recommendations about MAXDOP, visit Microsoft Support at https://support.microsoft.com/help/2806535. See also the section on Max degree of parallelism in Chapter 3.

Figure 4-2 The new MaxDOP tab in the Database Engine Configuration tab in SQL Server 2019 Setup, which has recommended a MaxDOP of 8 for a server with 8 virtual cores. This is effectively the same as MaxDOP 0, but offers the administrator an option to potentially change MaxDOP at the time of installation.

You can always reconfigure the MaxDOP after installation, without a restart, though not without potential disruption. While changing the server-wide (or database-level) MaxDOP setting takes effect immediately, it is definitely not advisable to do so during normal production operating hours, because it can lead to widespread plan recompilation and a heavy CPU spike. This server-wide MaxDOP setting can be overridden at the database, query, or Resource Governor group level.

• For much more information on performance tuning, parallelism, and the MaxDOP setting, see Chapter 14, “Performance tuning SQL Server.”

Default settings for Maximum Server Memory

New in SQL Server 2019 are defaults for the configuration of the instance-level Maximum Server Memory option, a common post-installation checklist item, under the Memory tab of the Database Engine Configuration page. SQL Server Setup makes a guess based on total server memory for an appropriate option. In previous versions of SQL Server, it was important to remember to go and change the Max Server Memory setting after installation was complete, otherwise SQL Server memory would be uncapped, and have access to all memory on the server.

Now in SQL Server 2019, this max server memory option can be configured intelligently at the time of installation. It’s important to note (and there’s a checkbox to accept this guess) that SQL Server Setup is assuming this SQL Server instance will run alone on this server. If you expect to host other applications on this server, or memory-heavy features of SQL Server on the same server, such as SSAS or SSRS, you should reduce the maximum server memory for the SQL Server instance further.

• We discussed the Maximum Server Memory setting in Chapter 3, in the “Configuration Settings” section.

An example of the new max server memory recommendation configuring a Windows Server with one SQL Server instance and 16 GB of memory, SQL Server setup recommends a Max Server Memory setting of 12672 MB, as seen in Figure 4-3.

Figure 4-3 The new Memory tab in the Database Engine Configuration tab in SQL Server 2019 Setup.

Just above the Maximum Server Memory setting is the Minimum Server Memory setting, which establishes a floor for memory allocation. It is generally unnecessary to change this setting from the default of 0. You might find this setting useful for situations in which the total system memory is insufficient and many applications, including SQL Server instances, are present. The minimum server memory is not immediately allocated to the SQL Server instance upon startup; instead, it does not allow memory below this level to be freed for other applications.

After installation, server memory settings are accessible via SQL Server Management Studio, in Object Explorer, and on the Server Properties page.

You should assure that SQL Server leaves enough memory for the operating system and other applications. Keep in mind that SQL Server will slowly consume more memory over time, and may take hours or days, depending on your business cycle, for the SQL Server instance to consume the maximum amount of memory made available. Similarly, lowering this setting after installation and during operation does not return SQL Server memory back to the OS immediately; rather, it does so over time during SQL Server activity. Similarly, increasing this setting will not immediately show the effect of a change in memory utilization.

Installing common features

Aside from the SQL Server service itself, other features of the product might be common to your installations. SQL Server Analysis Services, SQL Server Integration Services, and SQL Server Reporting Services are part of the license and are provided at no additional cost. If you need them, this section covers installing these features using Setup. Later in this chapter, we cover the post-installation steps necessary to use them.

Installing SQL Server Analysis Services

Installing SQL Server Analysis Services (SSAS) requires you to make a decision at installation time regarding the mode in which it can be installed. Each instance of SQL Server Analysis Services can be in only one mode, which means that with a single license, you can run only Multidimensional mode, the newer Tabular mode (introduced in SQL 2012), or the Power Pivot mode.

Ask your business intelligence decision makers which platform you should use, though for most new development, Tabular mode is popular and recommended. Tabular mode databases can also run in Azure Analysis Services. The following are brief descriptions of each mode:

• Multidimensional mode. This is the SSAS setup that was first introduced in SQL 2000. This is also the only mode to support data mining and other features that existing SSAS data models predating SQL Server 2012 may be dependent on. The primary language for building and querying multidimensional models is MDX.

  • Tabular mode. This is the newer and recommended SQL Server Analysis Services setup that was first introduced in SQL 2012, using the in-memory VertiPaq processing engine. Since SQL Server 2017, this is the default installation mode selected on the Analysis Services Configuration page of Setup. The primary language for building and querying tabular models is DAX, which is similar to the Excel function language.

  • Power Pivot mode. This mode installs SQL Server Analysis Services in the Power Pivot for SharePoint mode. Power Pivot workbooks use both DAX and MAX. Note that Analysis Services Power Pivot for SharePoint support for Microsoft SharePoint 2019 has been discontinued.

For more on the differences between these SSAS installation options, visit https://docs.microsoft.com/analysis-services/comparing-tabular-and-multidimensional-solutions-ssas.

Installing SQL Server Integration Services

The SQL Server Integration Services instance for SQL Server 2019 is installed once per server per version, not once per instance, like other features. Starting in SQL Server 2017, however, a new Integration Services Scale Out Configuration is available. We discuss this new feature further in the next section.

A 64-bit version of SQL Server Integration Services is installed on 64-bit operating systems. If you worry about connecting to 32-bit servers, data sources, or applications installations (such as Microsoft Office), don’t—those connections are not dependent on the 32-bit/64-bit installation and are handled at the package or connection-string level. Unlike other features, you can install SQL Server Integration Services on a 32-bit OS; however, we do not recommend this.

Installations of different versions of SQL Server Integration Services are installed side-by-side on a server; specifically, the service SQL Server Integration Services 15.0 is compatible with prior versions.

Outside of configuring the service account, you do not need any additional configuration when installing SQL Server Integration Services during SQL Server Setup. The default virtual service account is NT Service\MsDtsServer150.

Installing SQL Server Integration Services Scale Out configuration

Integration Services supports a Scale Out configuration by which you can run a package on the same or multiple SQL Server instances, a new feature that was first introduced in SQL Server 2017. This also allows for high availability of SQL Server Integration Services, and a similar architecture allows for integration and “lift and shift” code deployments from on-premises SQL Server Integration Servers to the Azure Integration Runtime.

The master node talks to worker nodes in a SQL Server Integration Services Scale Out, with the communication over a port (8391 by default) and secured via a new Secure Sockets Layer (SSL) certificate. The SQL Server installer can automatically create a 10-year self-signed certificate and endpoint for communication at the time the master node is set up.

When adding another SQL Server Integration Services installation as a Scale Out Worker, start the new SQL Server Integration Services Manage Scale Out window via SQL Server Management Studio. Right-click the Catalog you have created, and then click Manage Scale Out. At the bottom of the page, click the + button to add a new Scale Out Worker node. Provide the server name on which to connect. If using a named instance, provide only the server name of the node; do not include the instance name. A dialog box confirms the steps taken to add the Worker node, including copying and installing certificates between the Worker node and Master node, updating the endpoint and HttpsCertThumbprint of the worker, and restarting the Worker’s Scale Out service. After the worker node is added, refresh the Worker Manager page, and then click the new Worker node entry, which will be red. You must turn on the Worker Node by clicking Enable Worker.

You also can copy and install the certificates manually between servers. You will find them in: %program files%\Microsoft SQL Server\150\DTS\Binn\. For more information on certificates between servers, visit https://docs.microsoft.com/sql/integration-services/scale-out/deal-with-certificates-in-ssis-scale-out. For a Microsoft-provided walkthrough of setting this up, visit https://docs.microsoft.com/sql/integration-services/scale-out/walkthrough-set-up-integration-services-scale-out.

One major security difference with Scale Out is that even though the SQL Server Integration Services Service Account doesn’t run packages or need permission to do very much, the Scale Out Master and Worker service accounts actually do run packages. The SQL Server Integration Services service account is different from the Scale Out Master and Scale Out Worker service accounts.

The Worker and Master nodes do not appear in SQL Server Configuration Manager (as of SQL 2019) but do appear in Services.msc. By default, these services run under virtual accounts NT Service\SSISScaleOutMaster150 and NT Service\SSISScaleOutWorker150, but you might want to change these to a Windows-authenticated Domain service account that will be used to run packages across the Scale Out.

Installing SQL Server Reporting Services

Starting with SQL Server 2017, SQL Server Reporting Services is no longer found in the SQL Server Setup media; it is instead available as a simplified, unified installer and a small download. SQL Server Reporting Services is now a 95+MB download named SQLServerReportingServices.exe, but still needs a SQL Server Database Engine instance as part of the license to host the two Report Server databases.

Note that SQL Server Reporting Services isn’t free, and that the separate installer isn’t a licensing change, though SQL Server Express with Advanced Services offers some limited SSRS support. For more information on the limitations of SSRS with SQL Server Express license, view https://docs.microsoft.com/sql/reporting-services/reporting-services-features-supported-by-the-editions-of-sql-server-2016.

To install SSRS, you will need to provide a license key upon installation in a production environment. You can choose a free edition to install (Evaluation, Developer, or Express), though you should note that Developer edition is not allowed in a production environment.

The “native” mode of SQL Server Reporting Services is now the only mode, since SQL Server 2017. If you are familiar with Reporting Services Report Manager in the past, accessible via the URL servername/Reports, that is the “native mode” installation of Reporting Services.

You’ll notice the Report Server Configuration Manager in a new location, in its own Program Files menu: Microsoft SQL Server Reporting Services. After installation, start the Report Server Configuration Manager (typically installed in a path like \Program Files (x86)\Microsoft SQL Server\150\Tools\Binn\RSConfigTool.exe). The Report Server Configuration Manager application itself is largely unchanged since SQL 2008.

The virtual service account NT SERVICE\SQLServerReportingServices is the default SQL Server Reporting Services service account. It is a second-best option, however: we recommend that you create a new domain service account to be used only for this service; for example, Domain\svc_ServerName_SSRS or a similar naming convention. You will need to use a domain account if you choose to configure report server email with Report server service account (NTLM) authentication.

If you choose to change the SQL Server Reporting Services service account later, use only the Reporting Services Configuration Manager tool to make this change. Like other SQL Server services, never use the Services console (services.msc) to change service accounts.

After installation, you will need to follow-up on other changes and necessary administrative actions; for example, configuring the SQL Server Reporting Services Execution Account, email settings, or backing up the encryption key using Reporting Services Configuration Manager.

SQL Server 2019 Reporting Services also can integrate with Microsoft Power BI dashboards. A page in the Report Server Configuration Manager supports registering this installation of SQL Server Reporting Services with a Power BI account. You will be prompted to sign into Azure Active Directory. The account you provide must be a member of the Azure tenant where you intend to integrate with Power BI. The account should also be a member of the system administrator in SQL Server Reporting Services, via Report Manager, and a member of the sysadmin role in the SQL Server that hosts the Report Server database.

Installing machine learning features

The Machine Learning Services (In-Database or the standalone Machine Learning Server) feature makes it possible for developers to integrate with the R language and/or Python language extensions using standard T-SQL statements.

Data scientists can take advantage of this feature to build advanced analytics, data forecasting, and algorithms for machine learning.

In SQL Server 2019, the Instance Feature Machine Learning Services (In-Database) is renamed to Machine Learning Services and Language Extensions, while the name for the standalone Shared Feature Machine Learning Server (Standalone) is the same.

Machine Learning Services and Language Extensions support Java code execution inside the Database Engine. By default it installs the Zulu Open JRE if you select the Java checkbox under “Machine Learning Services and Language Extensions” in the feature selection page. If you must use a different Java JRE because of an application dependency, such as the official Oracle JRE, you should add it via a home variable and the icacls Windows command, as detailed here: https://docs.microsoft.com/sql/language-extensions/install/install-sql-server-language-extensions-on-windows.

• For more on Installing and Leveraging Machine Learning Services, see Chapter 20, “Leveraging Big Data and Machine Learning.”

Installing PolyBase Query Service for External Data

The PolyBase connector is a much-marketed feature for allowing native connectors for external data sources, even non-Microsoft or non-relational database platforms like Oracle, Teradata, MongoDB, Cloudera, or Apache Hadoop.

Using PolyBase EXTERNAL tables, we can use SQL data types and T-SQL queries to seamless query data sources in-place, in what Microsoft calls “data virtualization.” This eliminates the need for complex heterogeneous data movement, and reduces the need for developers to have knowledge of other external query languages. The PolyBase Query Engine feature is specifically designed for read and write queries against non-Microsoft database platforms like Oracle and DB2, but also for Hadoop nonrelational data or Azure Blob Storage files, MongoDB, and more. This is a superior alternative to linked servers to the same external data sources, because PolyBase allows “Push Down” computation for these external sources, reducing the amount of data transferred and increasing the performance of analytical-scale queries.

New to SQL Server 2019 is the ability to install the Java connector for HDFS data sources with SQL Server Setup. This 7GB+ option doesn’t replace the requirement to install the Oracle JRE in order to install the PolyBase Query Service feature.

• For more on the PolyBase Query Service feature, see Chapter 20.

Logging SQL Server Setup

SQL Server Setup generates a large number of logging files for diagnostic and troubleshooting purposes. These logs should be the first place you go when you have an issue with Setup.

First, a System Configuration Check report .htm file is generated each time you run Setup, so you can view this report in SQL Server Setup near the start of the installation steps.

A new timestamp-named folder of log files is generated for each launch of SQL Server Setup. After you proceed past the Ready To Install page, and regardless of whether Setup was a complete success, it generates a number of log files in the following folder:

%programfiles%\Microsoft SQL Server\150\Setup Bootstrap\Log\YYYYMMDD_HHMMSS\

However, when you run Setup using the /Q or /QS parameters for unattended installation, the log file is written to the Windows %temp% folder.

A log summary file of the installation is created that uses the following naming convention:

Summary_instancename_YYYYMMDD_HHMMSS.txt

Setup generates similar files for the Component and Global Rules portions of Setup as well as a file called Detail.txt in the same folder. These files might contain the detailed error messages you are looking for when troubleshooting a failed installation. The Windows Application Event log might also contain helpful information in that situation.

You’ll also find the new SQL Server instance’s first error log encoded at UTC time in this folder, showing the log from startup, similar to the normal SQL Server Error Log.

Automating SQL Server Setup by using configuration files

Let’s dig more into what you can do with setup.exe outside of the user interface. You can use configuration files to automate the selection process when installing SQL Server, which helps to create a consistent configuration.

Values provided in configuration files can prepopulate or override Setup settings. They also can configure Setup to run with the normal user interface, or silently without any interface.

Starting SQL Server Setup from the command line

You can start setup.exe from either Windows PowerShell or the command prompt, providing repeatability and standardization of parameter options. You also can use it to prefill sections of the Setup wizard or to change the default behavior of Setup.

For the purposes of the installer, ensure that you always use the Administrator level for these two shells. The title on each application window should be preceded by Administrator:, such as: Administrator: Windows PowerShell.

Sometimes, you also might find it necessary to start Setup from the command line or Windows PowerShell because of a workaround for a specific problem, or to automate and standardize future SQL Server installations. To start Windows PowerShell or command prompt as Administrator, in the Start menu, search for the desired application (see Figure 4-4), right-click it, and then, on the shortcut menu that opens, select Run As Administrator.

Figure 4-4 Starting Setup.exe from PowerShell.

From the location of the SQL Server Setup installation files, for example the mounted .iso file, execute the following with PowerShell or the Windows Prompt:

.\Setup.exe /ConfigurationFile=c:\install\SQL2019_basic.INI

The previous sample script and all scripts for this book are available for download at https://www.MicrosoftPressStore.com/SQLServer2019InsideOut/downloads. The above code sample uses a configuration file to pre-select installation choices, for example, features to be installed. Let’s talk more about configuration files.

Generating a configuration file

Writing a configuration file by hand is not necessary and can be tedious. Instead of going through that effort, you can let SQL Server Setup create a configuration file for you.

Work your way through the normal SQL Server Setup user interface, completing everything as you normally would, but pause when you get to the Ready To Install page. Near the bottom of this page is a path (see Figure 4-5). At that location, even before you hit the Install button, you’ll find a generated configuration file, ready for future use and modification if needed.

Figure 4-5 The Ready To Install page displays a summary of the installation steps as the Configuration File Path that has been prepared.

For example, the first modification you need to make to the .ini file is to accept the SQL Server license terms via the IACCEPTSQLSERVERLICENSETERMS parameter, which isn’t automatically provided in the automatically generated .ini file. Unless you modify an .ini file to provide this, it isn’t possible to run the installer without user interaction.

Installing by using a configuration file

Now that you have a configuration file generated by using a previous walk-through of Setup, you can take the next step to automating or standardizing your installation.

You can start Setup.exe from a Prompt with a configuration file by using the /CONFIGURATIONFILE parameter of Setup.exe. Or, you can launch SQL Server Setup with a configuration file by navigating to the Advanced page of the SQL Server Installation Center that starts with Setup.exe in Windows. Select the Install Based On A Configuration File check box. A message appears, asking you to browse to the .ini file. After you select the appropriate file, setup.exe will start with those options.

One thing to keep mind, however, is that configuration files generated by Setup.exe do not and should not store the passwords you provided for any service accounts. If you do want to configure service account credentials in your configuration file, for security reasons, do not store the service account passwords in plain text in a configuration file. You should instead store passwords separately and securely and provide them when you run setup.exe.

Each service’s account parameters are available in a Setup.exe runtime parameter, which is listed in Table 4-1.

Table 4-1 Common Setup.exe parameters and their purposes

For example, in the snippet that follows, the PROD_ConfigurationFile_Install.INI has provided the account name of the of the SQL Server Database Engine service account, but the password is provided when Setup.exe runs in the command prompt or PowerShell:

Setup.exe /SQLSVCPASSWORD="securepwd" /ConfigurationFile="d:\SQL\PROD_Install.INI"

The previous sample script and all scripts for this book are available for download at https://www.MicrosoftPressStore.com/SQLServer2019InsideOut/downloads.

You can provide further parameters like passwords when you run Setup. Parameter settings provided will override any settings in the configuration file, just as the configuration file’s settings will override any defaults in the Setup operation. Table 4-2 lists and describes the parameters.

Table 4-2 Common Setup.exe parameters of which you should be aware

By default the /UpdateEnabled parameter is enabled and doesn’t need to be specified, and SQL Server will include updates found via Windows Update. If you choose to disable this behavior by provided /UpdateEnabled=False, you can also specify /UpdateSource the location of cumulative update or other SQL patch file executables to be included in the installation.