Install and configure SQL Server instances and features

Install a new instance

In this section, you learn how to begin a new SQL Server 2022 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 2022. 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 to highlight new features from SQL Server 2022.

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 will go over some of the command-line installation methods that you can use to automate the installation of a SQL Server instance.

Plan for multiple SQL Server instances

You can install as many as 50 SQL Server instances on a Windows Server, although we obviously 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. 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.

Install SQL Server on Windows

The rest of this chapter is dedicated to installations of SQL Server that are not part of a pre-made Azure Marketplace VM and apply to the 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.

Launch SQL Server Setup

You should not run SQL Server Setup with the installation media mounted over a remote network connection, 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 folder local to the server.

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 select Run As Administrator on the shortcut menu that appears.

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

In the pane on the left, select 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 for upgrading from prior versions of SQL Server.

On the Maintenance page, you will find the following:

• The Edition Upgrade Wizard is relatively painless. This is only for promoting your existing installation’s edition, as discussed earlier.

• The Repair feature is not commonly used except in the case of an instance with a corrupted installation. You might also need to repair an instance of SQL Server when the executables, .dll files, or Windows Registry entries have become corrupted or damaged by disk corruption, antimalware, malware, 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.

• 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.

• The Advanced page features 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 “Automate SQL Server Setup with 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, “Implement 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 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, so a SQL Server 2022 Setup with Internet connectivity is the easiest way to carry out the installation. This also could be described as a way to “slipstream” 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 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 “Install by using a configuration file” section.

Regardless, after installation is complete, and before the SQL Server enters further use, verify that the latest SQL Server patches have been applied. For SQL Server 2022, see the official build versions at https://support.microsoft.com/help/4518398.

Install SQL Server stand-alone installation

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

PolyBase Services

Immediately after the instance configuration is a new configuration for the port range of PolyBase services. This is where you choose a range of ports to use for this service. If you plan to use PolyBase, the ports typically used are TCP ports between 16450 and 16460, of which there must be at least six ports. These should be allowed through the firewall if needed. This option was added to SQL Server Setup in SQL Server 2022.

Grant Perform Volume Maintenance Tasks

On the same Server Configuration page on which service accounts are set, notice the Grant Perform Volume Maintenance Task privilege to the SQL Server Database Engine Service check box. Selecting this check box automates what used to be a standard post-installation checklist step for SQL DBAs beginning with 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, “Design and implement an on-premises database infrastructure.”

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 the section on collation in Chapter 7, “Understand table features.”

Initially, the instance collation provided in SQL Setup is the default collation for the server’s regional settings, but you might 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.

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. For new application development, you may choose to take advantage of UTF-8 collations as the server default, introduced in SQL Server 2019.

Mixed Mode authentication

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

• You can read more on this topic in Chapter 12, “Administer instance and database 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—ideally, AD domain accounts or, starting with SQL Server 2022, Azure Active Directory (Azure AD) principals. 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. Be aware that SQL Authentication is not on by default, and isn’t the recommended method of connection. The recommended Windows Authentication 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 12.

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 it later by connecting to the SQL Server instance via Object Explorer in SQL Server Management Studio. To do so, right-click the server name and select Properties from the shortcut menu. Then, select the Security page and change to Mixed Mode. You must perform a SQL Server service restart to effect this change.

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 hyper-threading for local machines. For example, with 16 logical processors, SQL Server Setup will default the installation to have eight tempdb data files.

Adding too many tempdb data files can degrade SQL Server performance—perhaps severely. 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.

Also note the 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.

• The tempdb system database is discussed in detail in Chapter 3.

Default settings for MAXDOP

New in SQL Server 2019 were defaults for the configuration of the server-wide Maximum Degree of Parallelism (MAXDOP) setting on the Database Engine Configuration page under the new MaxDOP tab.

In the same way that 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 cores, effectively the same as a MAXDOP setting of 0, which allows for unlimited parallelism.

For example, with 8 logical processors, SQL Server Setup will default the installation to use a MAXDOP of 8. 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 a MAXDOP of 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.

You can always reconfigure the MAXDOP after installation without a restart, though not without potential disruption. Although 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. The MaxDOP tab in the Database Engine Configuration tab has a recommended MAXDOP setting of 8 for a server with eight virtual cores. This is effectively the same as a MAXDOP of 0, but offers the administrator an option to potentially change the MAXDOP at the time of installation.

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

Default settings for Maximum Server Memory

New in SQL Server 2019 were defaults for the configuration of the instance-level Max 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 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.

You can configure this Max Server Memory option intelligently at the time of installation. It’s important to note (and there’s a check box to accept this guess) that SQL Server Setup assumes this SQL Server instance will run alone on this server. If you expect to host other applications on this server, or to run memory-heavy features of SQL Server on the same server such as SSAS or SSRS, you should further reduce the Max Server Memory setting for the SQL Server instance.

• Chapter 3 discussed the Max Server Memory setting, in the “Configuration settings” section.

Let’s use an example of the new Max Server Memory recommendation for 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. The Min Server Memory setting, which establishes a floor for memory allocation, is set to 0. It is generally unnecessary to change this setting from the default. 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 Min Server Memory setting 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. Figure 4-1 shows the Memory tab of the Database Engine Configuration page, with the Min Server Memory and Max Server Memory settings visible.

Figure 4-1 This figure displays the minimum and maximum server default memory settings for the SQL Server setup.

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

You should ensure that SQL Server leaves enough memory for the OS 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. 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. Increasing this setting will not immediately show the effect of a change in memory use.

Install common features

Aside from the SQL Server service itself, other features of the product might be common to your installations. For example, 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. This section covers the installation of these features using SQL Server Setup. Later, this chapter covers the post-installation steps necessary to use them.

Install SQL Server Analysis Services

Installing SQL Server Analysis Services (SSAS) requires you to decide ahead of time which mode to install. Each instance of SSAS can be in only one mode, which means that with a single license, you can run either Multidimensional mode, the newer Tabular mode (introduced in SQL Server 2012), or Power Pivot mode.

Ask your business intelligence (BI) decision makers which platform you should use. For most new development, Tabular mode is popular and recommended. Tabular mode databases can also run in Azure Analysis Services. Brief descriptions of each mode follow:

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

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

• Power Pivot. This mode installs SSAS in Power Pivot for SharePoint mode. Power Pivot workbooks use both DAX and MDX. 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://learn.microsoft.com/analysis-services/comparing-tabular-and-multidimensional-solutions-ssas.

Install SQL Server Integration Services

The SQL Server Integration Services (SSIS) instance 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 became available. We discuss this new feature further in the next section.

A 64-bit version of SSIS is installed on 64-bit operating systems. If you worry about connecting to 32-bit servers, data sources, or application installations (such as Microsoft Office), you don’t need to. 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 SSIS on a 32-bit OS; however, we do not recommend this.

Installations of different versions of SSIS are installed side by side on a server. Specifically, SSIS 16.0 is compatible with prior versions.

Apart from configuring the service account, you need not do any additional configuration when installing SSIS during SQL Server Setup. The default virtual service account is NT Service\MsDtsServer160.

Install SQL Server Integration Services Scale Out configuration

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

• Additional information on integration runtimes can be found in Chapter 19.

The master node talks to worker nodes in an SSIS Scale Out system, 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 when the master node is set up.

When adding another SSIS installation as a Scale Out Worker, start the new SSIS Manage Scale Out window via SQL Server Management Studio. To do so, right-click the catalog you have created and select Manage Scale Out. At the bottom of the page, select the + button to add a new Scale Out Worker node.

Next, you 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 Scale Out 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 node’s Scale Out service.

After the worker node is added, refresh the Worker Manager page. Then select the new Worker node entry, which will be red. Finally, turn on the Worker node by selecting Enable Worker.

You also can copy and install the certificates manually between servers. You will find them in %Program Files%\Microsoft SQL Server\160\DTS\Binn\.

• For more information on certificates between servers, visit https://learn.microsoft.com/sql/integration-services/scale-out/deal-with-certificates-in-ssis-scale-out. For a Microsoft-provided walk-through of setting this up, visit https://learn.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 SSIS service account doesn’t run packages or need permission to do very much, the Scale Out Master and Worker service accounts do run packages. The SSIS 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 Server 2019) but do appear in the Services console (services.msc). By default, these services run under virtual accounts NT Service\SSISScaleOutMaster160 and NT Service\SSISScaleOutWorker160, 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.

Install SQL Server Reporting Services

Starting with SQL Server 2017, SQL Server Reporting Services (SSRS) is no longer found in the SQL Server Setup media; it is instead available as a simplified, unified installer and a small download. SSRS 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 SSRS isn’t free, and that the separate installer isn’t a licensing change—although SQL Server Express with Advanced Services offers some limited SSRS support.

• For more information on the limitations of SSRS with SQL Server Express license, see https://learn.microsoft.com/sql/reporting-services/reporting-services-features-supported-by-the-editions-of-sql-server-2016.

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

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

Report Server Configuration Manager is 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\160\Tools\Binn\RSConfigTool.exe). The Report Server Configuration Manager application itself is largely unchanged since SQL Server 2008.

The default SSRS service account is the virtual service account called NT SERVICE\SQLServerReportingServices. It is a second-best option, however. We recommend that you instead create a new domain service account to be used only for this service—for example, Domain\svc_ServerName_SSRS or something with 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 SSRS service account later, you must use the Reporting Services Configuration Manager tool. As with other SQL Server services, you should 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 SSRS Execution Account and email settings or backing up the encryption key using Reporting Services Configuration Manager.

SSRS can also integrate with Microsoft Power BI dashboards. A page in the Report Server Configuration Manager supports the registration of this installation of SSRS with a Power BI account. You will be prompted to sign into Azure AD. 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 SSRS, via Report Manager, and a member of the sysadmin role in the SQL Server that hosts the Report Server database.

Install machine learning features

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

Data scientists can take advantage of this feature to build advanced analytics, data forecasting, and algorithms for machine learning. Data engineers can leverage these languages to integrate predictive analytic and machine learning. The scripts you create can be executed in-database without having to move data. You can prepare, clean, train, evaluate, perform feature engineering, and deploy machine learning models where the data resides. This eliminates the transfer of data across the network to another server.

Machine Learning Services is not a standalone feature. It requires a Database Engine instance. Also, it is now only available in the Instance Features section, and is no longer available in the Shared Features section.

Beginning with SQL Server 2022, runtimes for R, Python, and Java are no longer installed with SQL Setup. You must run the SQL Setup Wizard to install Machine Learning Services and Language Extensions. Then you must install your desired R, Python, or Java runtime(s) and packages.

You can install and use your open-source package and framework of choice, such as PyTorch, TensorFlow, and others. Machine Learning Services use an extensibility framework to run Python and R scripts.

EXEC sp_configure  'external scripts enabled';

• Note that there are separate Microsoft Docs articles for installation of Machine Learning Services on SQL Server 2019 and prior, and for SQL Server 2022. For installation on SQL Server 2022 on Windows, visit https://learn.microsoft.com/sql/machine-learning/install/sql-machine-learning-services-windows-install-sql-2022. For information about installing Machine Learning Services for SQL Server 2022 on Linux, see https://learn.microsoft.com/sql/linux/sql-server-linux-setup-machine-learning-sql-2022.

Availability groups are supported for Machine Learning Services, to ensure business continuity by configuring packages on each node, and failover cluster instances are supported from SQL Server 2019 onward.

You can execute Python and R scripts on a SQL Server instance with the stored procedure sp_execute_external_script.

You can find more details on each framework for this evolving feature in these Microsoft Docs articles:

• Extensibility framework. https://learn.microsoft.com/sql/machine-learning/concepts/extensibility-framework

• Python extension. https://learn.microsoft.com/sql/machine-learning/concepts/extension-python

• R extension. https://learn.microsoft.com/sql/machine-learning/concepts/extension-r

Install 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, and MongoDB.

Using PolyBase EXTERNAL tables, we can use SQL data types and T-SQL queries to seamlessly 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 on non-Microsoft database platforms like Oracle and DB2, but also for 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.

Install Azure extension for SQL Server

A new feature for SQL Server 2022 is extensibility for Azure features. This is in large part where the connections are initially set up for the features that make up the most Azure-connected version of SQL Server to date. Let’s look at the most common ones available so you understand what you are setting up.

Azure Arc–enabled servers

Azure Arc–enabled SQL Server instances are on-premises but still managed by Azure. This extends the services of Azure to the datacenter or wherever it is needed.

Azure Arc–enabled servers are supported only for the following operating systems:

• Windows Server 2012 R2 and higher

• Ubuntu 16.04 and 18.04 (x64)

• Red Hat Enterprise Linux (RHEL) 7 (x64)

• SUSE Linux Enterprise Server (SLES) 15 (x64)

To perform all the actions needed to connect an Azure Arc–enabled server to Azure, you need an account with all of the following privileges:

• Microsoft.HybridCompute/machines/extensions/read

• Microsoft.HybridCompute/machines/extensions/write

• Microsoft.HybridCompute/machines/extensions/delete

• Microsoft.HybridCompute/machines/read

• Microsoft.HybridCompute/machines/write

• Microsoft.GuestConfiguration/guestConfigurationAssignments/read

• Microsoft.Authorization/roleAssignments/write

• Microsoft.Authorization/roleAssignments/read

To enable the services so that Azure Arc recognizes your instance, you need to register it for the services you want to take advantage of. There are a few steps to follow, both in Azure and on the server itself, for existing instances. Detailed instructions on how to do this can be found at https://learn.microsoft.com/sql/sql-server/azure-arc/overview.

Microsoft Defender for Cloud

Microsoft Defender for Cloud is a Cloud Security Posture Management (CSPM) and Cloud Workload Protection Platform (CWPP) that can be run in Azure but has been extended to on-premises and third-party clouds for multi-cloud opportunities with Azure Arc. The purpose of Defender is to assess, secure, and defend from threats. It does this by:

• Continuously assessing your security posture so you can identify opportunities, track vulnerabilities, and report

• Securing resources and checking best practices to provide cloud recommendations

• Defending from, detecting, alerting on, and resolving threats in real-time so you can prevent security events from happening

  • For more detailed steps on setting up Microsoft Defender for Cloud, see the Microsoft Cloud Guide tutorial at https://mslearn.cloudguides.com/guides/Protect%20your%20multi-cloud%20environment%20with%20Microsoft%20Defender%20for%20Cloud.

Microsoft Defender is only supported for SQL Server on Windows machines and must have one of the RBAC roles assigned to it, as described in the next paragraph.

• Details on how to install Defender on your Azure Arc–enabled server for SQL Server can be found at https://learn.microsoft.com/sql/sql-server/azure-arc/configure-advanced-data-security.

Microsoft Defender for Cloud uses Azure role-based access control (RBAC)—a built-in set of roles assigned to users, groups, and services in Azure—to assess, manage, and access resources. Users require the Assignments role with write permissions, such as a User Access Administrator or Owner. You can access information related to a resource when you are assigned the role of Owner, Contributor, or Reader for the subscription or the resource’s resource group.

Other built-in roles are specific to Microsoft Defender for Cloud:

• Security Reader users have viewing rights, which lets them view recommendations, alerts, security policies, and security states, but not make changes.

• Security Admin users have the same rights as Security Reader users but can also update the security policy, dismiss alerts and recommendations, and apply recommendations.

  • For detailed instructions on how to assign roles in the Azure portal, see “Assign Azure roles using the Azure portal” at https://learn.microsoft.com/azure/role-based-access-control/role-assignments-portal.

  • Find instructions for assigning administrator roles in Azure AD at https://learn.microsoft.com/azure/active-directory/roles/manage-roles-portal.

Azure AD Authentication

New with SQL Server 2022, you can authenticate SQL Server with Azure AD using the following methods:

• Azure AD Password

• Azure AD Integrated

• Azure AD Universal with Multi-Factor Authentication

• Azure AD access token

Azure AD support makes hybrid integrations with Azure Synapse Analytics, Azure SQL Managed Instance, Azure Arc, and other services easier. If your Windows Server AD is federated with Azure AD, users can use those credentials to sign into SQL Server. However, Azure AD authentication does not support service accounts or other complex architectures of AD.

Azure AD support requires that both SQL Server and the host server (Windows or Linux) be registered with Azure Arc.

• For more details on Azure AD, see Chapter 12.

Microsoft Purview

Microsoft Purview is a data-governance tool designed to support organizations in finding, understanding, governing, and consuming data stores. Microsoft Purview has been a cloud-first feature for some time, and has come to SQL Server on-premises with SQL Server 2022.

As with many other Azure hybrid features, SQL Server must be registered with Azure Arc to use Microsoft Purview. In addition, you will need to create a Microsoft Purview account and enable Azure AD.

• For information on creating a Microsoft Purview account, see https://learn.microsoft.com/azure/purview/create-catalog-portal.

Azure extension for SQL Server

To connect SQL Server to Azure Arc and take advantage of Microsoft Defender, Azure AD, and Microsoft Purview, you must install the Azure extension for SQL Server during SQL Server Setup on the Azure Extension for SQL Server page (see Figure 4-2). You can use your existing Azure credentials or an Azure Service Principal, and then complete the required fields such as Azure Research Group, Azure Region, and Azure Tenant ID. If you are not interested in connecting your SQL Server instance to Azure Arc, simply deselect the Azure Extension for SQL Server check box.

Figure 4-2 The Azure Extension for SQL Server page displays a number of required fields to enable Azure Arc features.

Log SQL Server Setup

SQL Server Setup generates many logging files for diagnostic and troubleshooting purposes. These logs should be the first place you go if you have an issue with Setup.

First, a System Configuration Check Report .htm file is generated each time you run Setup. 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.

Automate SQL Server Setup with 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.

Start 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 you always use the Administrator level for these two shells. The title on each application window should be preceded by Administrator:—for example, 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 the command prompt as Administrator, in the Start menu, search for the desired application, right-click it, and then select Run As Administrator on the shortcut menu that opens.

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

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

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

Generate 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-3). At that location, you’ll find a generated configuration file, ready for future use and modification if needed.

Figure 4-3 The Ready to Install page displays a summary of the installation steps as well as the path to the configuration file 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.

Install by using a configuration file

Now that you have a configuration file generated using the previous walk-through, you can take the next step to automate or standardize your installation.

You can start setup.exe from a command 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. Once there, 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 in mind 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. 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 provides the account name 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"

You can provide further parameters like passwords when you run Setup. Parameter settings provided override any settings in the configuration file, just as the configuration file’s settings 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 providing /UpdateEnabled=False, you can also specify /UpdateSource as the location of the cumulative update or other SQL patch file executables to be included in the installation.