The Planning Tab

The Planning tab is illustrated in Figure 2-2 and consists of numerous links to MSDN (Microsoft Developer Network) pages, which provide you with important documentation on SQL Server, such as a complete set of hardware and software requirements and documentation for SQL Server’s security model.

In addition to accessing documentation with the links provided, you can also access two tools. The first of these is the System Configuration Checker. This tool runs during the installation process to determine if there are any conditions that will prevent SQL Server from being installed. These checks include ensuring that the server is not already configured as a domain controller and checking that the WMI (Windows Management Instrumentation) service is running. When you run this tool before you begin installing SQL Server, it can prewarn you of any issues that may cause the installation to fail so that you can fix them before you begin installation. The System Configuration Checker is also available on the Tools tab on the Installation Center.

The second tool (or more accurately, a link to its download page) is the Data Migration Assistant. This tool can be used to detect compatibility issues when upgrading to SQL Server 2022, as well as recommending performance and reliability enhancements.

The Installation Tab

As illustrated in Figure 2-3, the Installation tab of the Installation Center contains the tools that you will use for installing a new instance of SQL Server, adding new features to an existing instance, or upgrading an instance from SQL Server 2016, 2017, or 2019. To upgrade from a version earlier than SQL Server 2016, you will need to upgrade to SQL Server 2016 first and then upgrade the 2016 instance to SQL Server 2022.

In order to install a stand-alone instance of SQL Server, you would select the New SQL Server stand-alone instance or add new features to an existing installation option.

In addition to installing a stand-alone instance, adding new features to an instance, and upgrading an existing instance to the latest version, there are also options on this screen for installing a SQL Server failover clustered instance and for adding a new node to an existing failover cluster. A failover cluster is a system where between 2 and 64 servers work together to provide redundancy and protect against a failure that stops one or more of the servers from functioning. Each server that participates in the cluster is known as a node.

The SQL Server Database Engine and the SQL Server Analysis Services are both “cluster-aware” applications, meaning that they can be installed on a Windows cluster and can make use of its failover capabilities. When installed on a failover cluster, databases and transaction logs are located on shared storage, which any node in the cluster can use, but the binaries are installed locally on each of the nodes.

There are also links to the download pages of SQL Server component, which are no longer included with the database media. These include SQL Server Data Tools, which is a studio for T-SQL and BI development, SQL Server Management Studio—an administration and development interface for SQL Server and SQL Server Reporting Services, which will allow the server to host and distribute reports.

The Maintenance Tab

The Maintenance tab contains tools for performing an edition upgrade, repairing a corrupt instance, and removing a node from a cluster; it also contains a link to run Windows Update, as illustrated in Figure 2-4.

You can use the Edition Upgrade option to upgrade an existing SQL Server 2022 instance from one edition to another, so, for example, you may wish to upgrade an instance installed as Developer edition to Enterprise edition.

You can use the Repair option to attempt to resolve issues with a corrupt installation of SQL Server. For example, you can use this tool if the Registry entries or binaries are corrupt, preventing the instance from starting.

Use the Remove node from a SQL Server Failover cluster option to remove SQL Server from a node within a failover cluster. You can use this option as part of the process for evicting a node. Unfortunately, the Installation Center has no functionality for uninstalling an instance. You must do this through the Control Panel.

Not surprisingly, you can use the Launch Windows Update to search for product updates option to launch Windows Update. You can then choose to install the updates and fixes that are available for SQL Server.

The Tools Tab

The Tools tab contains a selection of tools that will assist you in installing SQL Server, as illustrated in Figure 2-5. This includes the System Configuration Checker, which I introduced earlier in this chapter, a discovery tool for SQL Server components already installed on the local server, and the Microsoft Assessment and Planning (MAP) tool.

Choose the Installed SQL Server features discovery report option to analyze the local server and return a list of all SQL Server features and components that are installed. This will include features from all versions, from SQL Server 2000 and on.

The Microsoft Assessment and Planning (MAP) Toolkit For SQL Server option will provide you with a link from which you can download the MAP for SQL Server tool. When you run this tool, it will perform a network-wide search for SQL Server, Oracle, and MySQL installations. It will produce a detailed report, which, for SQL Server, will include the name, version, and edition of the component. For Oracle, it will include the size and usage of each schema, including complexity estimates for migration. You can also use this tool to plan migration and consolidation strategies and to audit license requirements across the enterprise.

The Resources Tab

As illustrated in Figure 2-6, the Resources tab contains links to useful information regarding SQL Server. This includes a link to SQL Server Books Online, the Developer Center, and the SQL Server product evaluation site. Additionally, on this tab, you will also find links to Microsoft’s privacy statement and the full SQL Server license agreement. Another very useful link is one that directs you to the SQL samples site. From this site, you can download the WideWorldImporters databases, which will aid you in testing features of SQL Server with a precreated database.

The Advanced Tab

On the Advanced tab, illustrated in Figure 2-7, you will find tools for performing advanced installations of SQL Server, both as a stand-alone instance and also as a cluster. These tools include Install based on configuration file, Advanced cluster preparation, Advanced cluster completion, Image preparation of a stand-alone instance of SQL Server, and Image completion of a stand-alone instance of SQL Server.

When you are installing SQL Server, a configuration file will automatically be created. It is also possible to create this configuration file manually. You can then use this configuration file to install other instances of SQL Server with an identical configuration. This can be useful for promoting consistency across the enterprise. Once this configuration file has been created, you can use the Install based on configuration file option to install further instances based on the pre-created configuration. Configuration files can also be useful for command-line installs, which will be discussed in Chapter 3. Additionally, you can also use a configuration file for cluster preparation.

If you wish to use a configuration file for cluster preparation, then instead of choosing to install the cluster via the New SQL Server failover cluster installation and Add node to a SQL Server failover cluster wizards, which are available on the Installation tab, you should choose the Advanced cluster preparation option on the Advanced tab. You will initially run this on one of the cluster nodes that can be a possible owner of the SQL Server instance, and a configuration file will be generated. Subsequently running the Advanced cluster preparation wizard on all other nodes of the cluster that can be possible owners will result in the configuration file being used to ensure consistency of installation across the cluster. This approach will even work for multi-subnet clusters (also known as geoclusters), since SQL Server will automatically detect the relationship between the subnets and you will be prompted to select an IP address for each subnet. The installation will then add each of the IP addresses as dependencies to the cluster role, using the OR constraint, where each node cannot be the possible owner of every IP address. Alternatively, it will use the AND constraint, where each node can be the possible owner of every IP address.

Once you have run the Advanced cluster preparation wizard on every node that is a possible owner of the clustered instance, you can run the Advanced cluster completion wizard. You only have to run this wizard once and you can run it on any of the nodes that are possible owners. After this wizard has completed successfully, the clustered instance will be fully functioning.

The Image preparation of a stand-alone instance of SQL Server option will use Sysprep for SQL Server to install a vanilla instance of SQL Server, which is not configured with account-, computer-, or network-specific information. It can be used in conjunction with Windows Sysprep to build a complete template of Windows with prepared SQL Server instances, which can then be used for deployments across the enterprise. This helps enforce consistency. In SQL Server 2022, all features of a stand-alone instance are supported by Sysprep; however, repairing an installation is not supported. This means that if an installation fails during either the prepare phase or the complete phase of the process, the instance must be uninstalled.

To finish the installation of a prepared image, you can use the Image completion of a prepared stand-alone instance of SQL Server option. This option will allow you to complete the configuration of the instance by inputting the account-, computer-, and network-specific information.

The Options Tab

As illustrated in Figure 2-8, the Options tab of the SQL Server Installation Center allows you to specify a path to the installation media. This can be useful if you have a copy of the media stored locally on the server.

Preparation Steps

When you choose to install a new instance of SQL Server, the first screen of the wizard that you are presented with will prompt you to enter the product key for SQL Server, as illustrated in Figure 2-9.

If you do not enter a product key on this screen, you will only be able to install either the Express edition of SQL Server, the Developer edition, or the Evaluation edition. Express edition provides a free, but cutdown edition of SQL Server with a reduced feature set and limits on database size. The Developer edition provides the same level of functionality as Enterprise but is not licensed for production use. The Evaluation edition has the same level of functionality as the Enterprise edition, but it expires after 180 days.

The next screen of the wizard will ask you to read and accept the license terms of SQL Server, as illustrated in Figure 2-10. A link provided on this screen will give you further details of Microsoft’s privacy policy.

After you accept the license terms, SQL Server setup will run a rules check to ensure that it can continue with the installation, as illustrated in Figure 2-11. This is the same configuration check that you can run independently from the Planning tab of SQL Server Installation Center, as discussed earlier in this chapter.

Assuming that all checks pass successfully, the screen of the wizard illustrated in Figure 2-12 will prompt you to choose if you want Microsoft Update to check for SQL Server patches and hotfixes. The choice here will depend on your organization’s patching policy. Some organizations implement a ridged patching regime for the testing and acceptance of patches, followed by a patching cycle, which is often supported with software such as WSUS (Windows Server Update Services). If such a regime exists in your organization, then you should not select this option.

The next screen of the wizard will attempt to scan for SQL Server updates to ensure that you install the latest CUs (cumulative updates) and SPs (service packs) with your installation. It will check the Microsoft Update service on the local server for these updates and list any that are available. This is an extension of slipstream installation functionality, which allows you to install updates at the same time as the installation of the base binaries by specifying their location for setup, but it has now been deprecated. The Product Updates page can also be configured to look for updates in local folders or network locations. This functionality will be discussed in further detail in Chapter 3.

As setup moves to the next page of the wizard, the extraction and installation of the files required for SQL Server setup begin, and the progress displays. This screen also displays the progress of the download and extraction of any update packages that were found by Product Updates.

As illustrated in Figure 2-13, the next screen of the wizard runs an installation rule check and displays and errors or warnings that you may need to address before installation begins.

In Figure 2-13, notice the warning being displayed for Windows Firewall. This will not stop the installation from proceeding, but it does warn you that the server has Windows Firewall switched on. By default, Windows Firewall is not configured to allow SQL Server traffic, so rules must be created in order for client applications to be able to communicate with the instance that you are installing. We will discuss SQL Server ports and Firewall configuration in detail in Chapter 5.

Assuming no errors are discovered that need to be addressed before you continue, the next page of the wizard will allow you to choose the feature that should be installed. This is discussed in detail in the next section.

The Feature Selection Page

The Feature Selection page of the setup wizard allows you to select the options that you wish to install. An overview of each of the available options can be found in Chapter 1. The Feature Selection page is illustrated in Figure 2-14.

Additionally, this page of the wizard requires you to specify folder locations for the instance root directory and the shared features directory. You may want to move these to a different drive in order to leave the C: drive for the operating system. You may want to do this for space reasons, or just to isolate the SQL Server binaries from other applications. The instance root directory will typically contain a folder for each instance that you create on the server, and there will be separate folders for the Database Engine, SSAS, and SSRS installations. A folder associated with the Database Engine will be called MSSQL16.[InstanceName], where instance name is either the name of your instance, or MSSQLSERVER for a default instance. The number 16 in this folder name relates to the version of SQL Server, which is 16 for SQL Server 2022. This folder will contain a subfolder called MSSQL, which in turn will contain folders that will be home to the files associated with your instance, including a folder called Binn, which will contain the application files, application extensions, and XML configurations associated with your instance; a folder called Backup, which will be the default location for backups of databases; and a folder called Data, which will be the default location of the system databases. The default folders for TempDB, user databases, and backups can be modified later in the installation process, and splitting these databases onto separate volumes is a good practice in many environments, as discussed in Chapter 1. Other folders will also be created here, including a folder called LOGS, which will be the default location for the files for both the Error Logs and the default Extended Event health trace.

On the next page of the wizard, an additional rules check will be carried out to ensure that the features that you have selected can be installed. The rules that are checked will vary depending on the features that you have selected.

The Instance Configuration Page

After successful completion of the rules check, the following screen of the wizard will allow you to specify if you would like to install a default instance or a named instance, as illustrated in Figure 2-15. The box in the lower half of the screen will give you details of any other instances or shared features that are already installed on the server.

The difference between a default instance and a named instance is that a default instance takes the name of the server that it is installed on, whereas a named instance is given an extended name. This has the obvious side effect that it is only possible to have a single default instance of SQL Server on a server, but you can have multiple named instances. With SQL Server 2022, up to 50 stand-alone instances can be hosted on a single server. Naturally, these instances will share the server’s physical resources. For failover clusters, this number stays the same if your data is hosted on an SMB file share, but it reduces to 25 if you use a shared cluster disk for storage.

You are not required to install a default instance before installing a named instance. It is a perfectly valid configuration to have only named instances on a server with no default instance. Many DBA teams choose to only support named instances in their environments so that they can enforce naming conventions that are meaningful at the SQL Server layer, as opposed to relying on the naming conventions imposed by the infrastructure teams who build the servers or VMs. The maximum length of an instance name is 16 characters. By default, the InstanceID will be set to the instance name, or MSSQLSERVER for a default instance. Although it is possible to change this ID, it is bad practice to do so, because this ID is used to identify Registry keys and installation directories.

Selecting Service Accounts

The next screen of the wizard is separated into two tabs. The first tab will allow you to specify service accounts for each of the SQL Server services, as illustrated in Figure 2-16, and the second tab will allow you to specify the collation of your instance.

SQL Server 2022 supports the use of local and domain accounts, built-in accounts, virtual accounts, MSAs (managed service accounts), and gMSAs (group managed service accounts) as the security context used to run a service. The service account model that you choose is key to both the security and manageability of your environment.

Different organizations have different requirements for service account models, and you may be constrained by compliance requirements and many other factors. Essentially, the choice that you make is a trade-off between the security and operational supportability of your environment. For example, the Microsoft best practice is to use a separate service account for every service and to ensure that every server in your environment uses a discrete set of service accounts, since this fully enforces the principle of least privilege. The principle of least privilege states that each security context will be granted the minimum set of permissions required for it to carry out its day-to-day activities.

In reality, however, you will find that this approach introduces significant complexity into your SQL Server estate, and it can increase the cost of operational support while also risking increasing outage windows in disaster scenarios. On the flip side, I have worked in organizations where the service account model is very coarse, to the point where there is only a single set of SQL Server service accounts for each region. This approach can also cause significant issues. Imagine that you have a large estate and the whole estate uses the same service account. Now imagine that you have a compliance requirement to change service account passwords on a 90-day basis. This means that you would cause an outage to your entire SQL Server estate at the same time. This simply is not practical.

There is no right or wrong answer to this problem, and the solution will depend on the requirements and constraints of individual organizations. For organizations that use domain accounts as service accounts, however, I tend to recommend a distinct set of service accounts for each data-tier application. So if you imagine an environment, as shown in Figure 2-17, where your data-tier application consists of a two-node cluster and an ETL server in a primary site, and two disaster recovery (DR) servers in a secondary site, this design would involve a common set of service accounts used by all of these instances, but other data-tier applications would not be allowed to use these accounts and would require their own set.

Of course, this model poses its own challenges. For example, you would need to review and amend this policy if you were to start a process of consolidation. Because of the challenges surrounding service account management, Microsoft introduced virtual accounts and MSAs. Virtual accounts are local accounts that have no password management requirements. They can access the domain by using the computer identity of the server on which they have been created. Managed service accounts, on the other hand, are domain-level accounts. They provide automatic password management within AD (Active Directory) and also automatically maintain their Kerberos SPNs (service principal names), as long as your domain is running at the functional level of Windows Server 2008 R2 or higher.

Both of these types of account have a limitation, however. They can only be used on a single server. As discussed earlier, this can introduce complexity into your SQL Server estate, especially for highly available, multiserver applications. This issue has been resolved by the introduction of group MSAs, which give you the ability to associate an MSA with multiple servers within the domain. In order to use this functionality, however, your forest needs to be running at the functional level of Windows Server 2012 or higher.

Additionally, on this page of the wizard, you can choose to grant the Perform Volume Maintenance Tasks user rights assignment to the SQL Server service account. If you select this option, then SQL Server will have the ability to create database files and grow database files without the need to fill the empty space with zeros. This significantly improves the performance of file creation and growth operations.

The trade-off is that it opens a very small security hole. If any data was stored on the same area of the disk, that the database file is created on, then with specialized tooling, it is possible to retrieve that data, as it has not been overwritten. The chance of exploitation of this security hole is so remote, however, that I always recommend granting this privilege, in all but the most secure environments.

Choosing the Collation

The second tab of the Server Configuration page will allow you to customize your collation, as illustrated in Figure 2-18.

Collations determine how SQL Server will sort data and also define SQL Server’s matching behavior, with regard to accents, kana, width, and case. You can also specify that sorting and matching should be carried out on the binary or binary code point representations.

If your collation is accent sensitive, then in comparisons, SQL Server does not regard è as the same character as e, whereas it will treat these characters as equal, if accent insensitivity is specified. Kana sensitivity defines if the Japanese Hiragana character set is equal to the Katakana character set. Width sensitivity defines if a single byte representation of a character is equal to its two-byte equivalent.

You can see from the results in Figure 2-19 that the first query only found one example of the word John, because it used a case-sensitive collation, but because the second query uses a case-insensitive collation, it matched two results.

The results in Figure 2-20 show that the value Chips has been sorted differently using the two collations. This is because in traditional Spanish, ch is regarded as a separate character and is sorted after cz.

The results in Figure 2-21 show that because the data is ordered by code point rather than alphabetically, the values beginning with capital letters are ordered before those beginning with lowercase letters, since this matches the code points of the characters.

Unless you have a specific backward compatibility requirement, you should avoid using SQL collations and only use Windows collations. It is best practice to use Windows collations because SQL collations are deprecated and are not all fully compatible with Windows collations. Additionally, you should be mindful when selecting newer collations, such as Norwegian or Bosnian_Latin. Although this new family of collations map to code pages in Windows Server 2008 or above, they do not map to code pages in older operating systems. So if you were to run a SELECT * query, against your instance from an older operating system, such as Windows XP, the code page would not match, and an exception would be thrown.

Provisioning Instance Security

The next page of the setup wizard allows you to configure the Database Engine. It consists of six tabs. In the first tab, you can specify the authentication mode of the instance and instance administrators, as illustrated in Figure 2-22. The second tab allows you to specify the folder that will be used as the default data directory, as well as specific locations for user databases and TempDB. The third tab provides configuration options for TempDB, while the fourth allows you to configure the maximum degree of parallelism for the instance, the fifth allows for instance memory settings to be configured, and the final tab will allow you to configure FILESTREAM.

Windows authentication mode means that the credentials that a user supplies when logging into Windows will be passed to SQL Server, and the user does not require any additional credentials to gain access to the instance. With Mixed Mode, although Windows credentials can still be used to access the instance, users can also be given second-tier credentials. If this option is selected, then SQL Server will hold its own usernames and passwords for users inside the instance, and users can supply these in order to gain access, even if their Windows identity does not have permissions.

For security best practice, it is a good idea to only allow Windows authentication to your instance. This is for two reasons. First, with Windows authentication only, if an attacker were to gain access to your network, then they would still not be able to access SQL Server, since they would not have a valid Windows account with the correct permissions. With mixed-mode authentication, however, once inside the network, attackers could use brute-force attacks or other hacking methodologies to attempt to gain access via a second-tier user account. Second, if you specify mixed-mode authentication, then you are required to create an SA account. The SA account is a SQL Server user account that has administrative privileges over the instance. If the password for this account became compromised, then an attacker could gain administrative control over SQL Server.

Mixed-mode authentication is a necessity in some cases, however. For example, you may have a legacy application that does not support Windows authentication, or a third-party application that has a hard-coded connection that uses second-tier authentication. These would be two valid reasons why mixed-mode authentication may be required. Another valid reason would be if you have users that need to access the instance from a nontrusted domain.

Configuring the Instance

On the Server Configuration tab, you will also need to enter at least one instance administrator. You can use the Add Current User button to add your current Windows security context, or the Add button to search for Windows security principles, such as users or groups. Ideally, you should select a Windows group, which contains all DBAs that will require administrative access to the instance, since this simplifies security.

The Data Directories tab of Database Engine Configuration page is illustrated in Figure 2-23.

The Data Directories tab allows you to alter the default location of the data root directory. On this screen, you can also change the default location for user databases and their log files, as well as specify where TempDB data and log files should be created. As you may recall from Chapter 1, this is particularly important, because you will probably wish to separate user data files from their logs and also from TempDB. Finally, this tab allows you to specify a default location for backups of databases that will be taken.

The TempDB tab (Figure 2-24) allows you to configure file options for TempDB. The number of files required for TempDB is important, as too few files can cause contention on system pages, such as GAM (Global Allocation Map) and SGAM (Shared Global Allocation Map). The optimal number of files can be calculated using the formula: SMALLEST(Number of logical cores, 8). If your server has hyper-threading turned on, then the number of logical cores will be the number of physical cores multiplied by two. On VMWare, the number of logical cores will be equal to the number of virtual cores.

When considering the initial size of the files, I usually work to the rule: SUM(Data file size for all user databases) / 3 for busy OLTP (online transaction processing) systems, but this will vary, based on your requirements and the workload profile of your user databases.

The MaxDOP tab of the wizard, illustrated in Figure 2-25, allows you to configure the maximum number of CPU cores that can be used by any single query. The setup program calculates a default recommended value, but you can override this if required. Please see Chapter 5 of this book for a detailed discussion of how MaxDOP should be configured.

Figure 2-26 illustrates the Memory tab. Here, you are able to specify if you would like to use the default configuration, for the minimum and maximum amount of memory that can be allocated to the instance, use the recommended values, calculated by the setup wizard, or specify your own values. To specify your own preferred values, choose the recommended option, enter your values, and check the Click here to accept the recommended memory configurations for the SQL Server database engine check box. This check box must also be used, if you wish to adhere to the setup program’s recommendations. A detailed discussion of how best to configure minimum and maximum memory settings for the database engine can be found in Chapter 5 of this book.

The FILESTREAM tab of the Database Engine Configuration page allows you to enable and configure the level of access for SQL Server FILESTREAM functionality, as illustrated in Figure 2-27. FILESTREAM must also be enabled if you wish to use the FileTable feature of SQL Server. FILESTREAM and FileTable provide the ability to store data in an unstructured manner within the Windows folder structure while retaining the ability to manage and interrogate this data from SQL Server.

Selecting Enable FILESTREAM for Transact-SQL access will enable FILESTREAM, but the data can only be accessed from inside SQL Server. Additionally, selecting Enable FILESTREAM for file I/O access enables applications to access the data directly from the operating system, bypassing SQL Server. If this option is selected, then you will also need to provide the name of a preexisting file share, which will be used for direct application access. The Allow remote clients access to FILESTREAM data option makes the data available to remote applications. The three options build on top of each other, so it is not possible to select Enable FILESTREAM for file I/O access without fist selecting Enable FILESTREAM for Transact-SQL access, for example. FILESTREAM and FileTable will be discussed further in Chapter 6.

Configuring SQL Server Extension for Azure

As illustrated in Figure 2-28, the next page of the wizard will prompt you to enter the connection details that are required, in order to register with your Azure environment, to enable Azure Arc for the SQL Server instance. This will provide Azure management features, via Azure Arc, to the instance, without the need for any additional agent installations. Configuring this functionality is discussed in more detail in Chapter 21.

Completing the Installation

After a final rules check, the Ready to Install page of the wizard is displayed. This is the final page before installation commences, and it is illustrated in Figure 2-29. This screen gives you a summary of the features that will be installed, but possibly the most interesting component of this page is the Configuration file path section. This gives you the path to a configuration file that you can reuse to install further instances with an identical configuration. Configuration files will be discussed further in Chapter 3.

The setup wizard will display a progress bar during the installation. When installation is complete, a summary screen will be displayed, as shown in Figure 2-30. You should check to ensure that each of the components being installed has a status of Succeeded. The SQL Server installation is then complete.

If there are any issues during installation and one or more component fail, then a useful point to start troubleshooting is the installation Summary log. A URI to this log can be found at the bottom of the screen. We will discuss installation logs in Chapter 3.