Install and configure SQL Server instances and features

Contents

In this sample chapter from SQL Server 2022 Administration Inside Out, you will learn the process of installing and configuring a Microsoft SQL Server instance, creating and migrating databases, and how to deploy SQL Server using containers and Kubernetes.

  • What to do before installing SQL Server

  • Install a new instance

  • SQL Server on Azure virtual machines

  • Post-installation server configuration

  • Post-installation configuration of other features

  • Container orchestration with Kubernetes

This chapter reviews the process of installing and configuring a Microsoft SQL Server instance as well as creating and migrating databases. We pay special attention to new features introduced in SQL Server 2022 as well as other recent features you might not have noticed in earlier editions of SQL Server. We also discuss how to deploy SQL Server using containers and Kubernetes.

We present a post-installation checklist for you to use to verify your installation. When necessary, we also direct you to other sources of information and details for critical steps elsewhere in this book.

The content in this chapter related to SQL Server Setup mainly applies to SQL Server installations on Windows operating systems. Provisioning is vastly simplified for Azure SQL Database, Azure SQL Managed Instance, SQL Server on Linux, SQL Server in Linux containers, and Azure virtual machine (VM) images with pre-installed SQL Server from the Azure Marketplace. Even so, many recommended settings in this chapter apply for server-based platforms of SQL Server, such as in Linux containers or SQL Server on Linux. They are, after all, still very much the same SQL Server products that have always existed on Windows.

This chapter focuses on server-level setup and settings. Chapter 6, “Provision and configure SQL Server databases,” covers the initial creation and configuration of databases inside the SQL Server instance.

  • arrow.jpg For more on SQL Server on Linux, see Chapter 5, “Install and configure SQL Server on Linux.”

  • arrow.jpg For more on Azure SQL Database, see Chapter 17, “Provision Azure SQL Database.”

  • arrow.jpg For more on Azure SQL Managed Instance, see Chapter 18, “Provision Azure SQL Managed Instance.”

  • arrow.jpg For more on database migrations to SQL Server platforms in Azure, see Chapter 19, “Migrate to SQL Server solutions in Azure.”

What to do before installing SQL Server

Before running SQL Server Setup on your Windows Server, there are several factors and settings to consider—some of which you cannot easily change after installation. For example, choosing between the default instance and a named instance or choosing an instance collation are not decisions you can easily reverse after installation. (More about the server-level collation option later in this chapter, in the “Instance collation” section.)

However, many mistakes made in installation can be resolved afterward—albeit likely with some tedium and outages. For example, skipping the initial default data and log directories may land all your databases on the operating system (OS) volume. They can be moved to the appropriate volumes later, but it’s best to get it right the first time.

CAUTION

Do not install SQL Server on the same server as a domain controller. In some scenarios, it is not supported, and can even cause SQL Server Setup to fail.

SQL Server 2022 has most of the same hardware and software requirements as SQL Server 2019. There are some differences, however. For example, SQL Server 2022 requires .NET Framework 4.7.2, which you can download from https://dotnet.microsoft.com/download/dotnet-framework/net472.

In addition, we recommend you acquire the following before starting SQL Server Setup:

  • Active Directory (AD) service accounts for the SQL Server service, SQL Agent service, and other features if needed

  • The latest download of cumulative updates to bring the instance up to the latest patch level

  • A licensing decision around the number of processors and the edition to buy

  • A secure enterprise digital location for various passwords you will generate, backups of certificates, and keys

  • A decision as to whether to install the default or a named instance

  • A plan for where SQL Server files will go, with each volume formatted to the 64-KB disk unit allocation size (discussed in the next section)

INSIDE OUT: WHAT ARE SQL SERVER SERVICE ACCOUNTS?

SQL Server service accounts are the accounts used to handle the communication of services between the OS and SQL Server. Using AD accounts on Windows for these accounts is a best practice. These can be updated and set up after installation in SQL Server Configuration Manager if needed.

If possible, it is recommended to use managed service accounts (MSAs) or group managed service accounts (gMSAs). These are specially provisioned Windows accounts whose passwords are self-managed by Windows. This means privileged service account secrets no longer need to be secured and managed, providing greater security. For more information, visit https://learn.microsoft.com/windows-server/security/group-managed-service-accounts/group-managed-service-accounts-overview.

Decide on volume usage

For many good reasons, various types of SQL Server files should be placed on separate volumes. Although you can move user and system database data and log files to other locations after installation, it’s best to plan your volumes before installation.

The examples in this chapter assume your Windows OS installation is on the C: volume of your server. You should have many other volumes for SQL Server files, and we’ll review a sample layout soon. One of the basic guiding principles for a SQL Server installation is that anywhere you see “C:\,” you should change it to another volume. This helps minimize SQL Server’s footprint on the OS volume (especially if you install multiple SQL Server instances), and can have potential disaster recovery implications in terms of volume-level backups and restores.

INSIDE OUT: WHAT IF YOU ARE TIGHT ON SPACE ON THE OS VOLUME AFTER INSTALLING SQL SERVER?

There are some easy ways and some tricky ways to minimize the footprint of a SQL Server installation on the OS volume of your server (typically the C: volume, as it is for this example). In general, SQL Server Setup and cumulative updates delete temporary files involved in their installation, but not log files or configuration files, which should have a minimal footprint. Apart from log files, we recommend that you not delete any files installed by SQL Server Setup or cumulative updates. Instead, let’s look at some proactive steps to move these files off the C: volume.

Some parts of SQL Server Setup install on the OS volume (typically, and in this and future examples, the Windows C: volume). These files, which are staging areas for SQL Server Setup, are created on the OS volume in a C:\Program Files\Microsoft SQL Server\160\Setup Bootstrap\ subfolder structure, where 160 is specific to the internal version number (16.0) of SQL Server 2022. This folder is used for future cumulative updates or feature changes.

If you’re extremely tight on space before installing SQL Server, you will also find that the root binaries installation directory is, by default, C:\Program Files\Microsoft SQL Server\. When you’re using the SQL Server Setup user interface, there is no option to change this. You will, however, find this installation directory folder path listed as the INSTANCEDIR parameter in the config file that is generated by SQL Server Setup. How to use the config file to install SQL Server is further covered in the section “Automate SQL Server Setup with configuration files” later in this chapter.

If this is the first SQL Server instance you are installing on a server, you will have the opportunity to change the location of shared features files, the data root directory for the instance (which contains the system databases), and default database locations for user database files and their backups. If this is not the first SQL Server 2022 instance installation on this server, the shared features directory locations (for Program Files and Program Files x86) will already be set for you, and you cannot change them.

You should place as much of the installation as possible on other volumes, not the OS volume. Keep in mind that a full-featured installation of SQL Server 2022 can consume more than 14 GB.

INSIDE OUT: WHAT CAN YOU DO WITH THE D: VOLUME ON AN AZURE VM?

For Microsoft Azure Windows VMs, do not set the installation directories for any settings on the “Temporary Storage” D: volume. In a Linux VM, the same applies to /dev/sdb1.

The D: volume is the temporary storage volume on an Azure VM. The temporary storage volume is a high-speed disk that is locally present on the machine hosting the Azure VM, so it has better performance and lower latency than the default C: volume. The temporary storage volume contains only the Windows page file by default and is wiped upon server restart, resize, or host migration.

The only possible long-term use for the temporary storage volume is for tempdb files, which can exist on this drive if certain other considerations are taken. Otherwise, do not store any non-temporary files in the temporary storage.

For more details on using the D: volume for tempdb files, see “Locate tempdb files on the VM” in Chapter 16, “Design and implement hybrid and Azure database infrastructure.”

The following sample scenario is a good starting point for a volume layout for your SQL Server installation (the volume letters don’t matter):

  • Volume C. The OS. Some SQL Server files must be installed here.

  • Volume E. SQL Server installation files, log files, SQL Server database data files.

  • Volume F. SQL Server database log files.

  • Volume G. SQL Server tempdb data files and log files. (Alternatively, use the D: Temporary Storage volume on Azure Windows VMs.)

  • Volume H. SQL Server backups (if written locally).

Here are some more advanced volume decisions:

  • Use additional volumes for your largest data files (larger than 2 TB) for storage manageability:

    • For the most active databases

    • For FILESTREAM filegroups

    • For database replication snapshot files

    • For the Windows page file, especially for servers with large amounts of memory

INSIDE OUT: WHY SEPARATE SQL SERVER FILES ONTO DIFFERENT VOLUMES?

There are good reasons to separate your SQL Server files into various volumes, and not all of them are related to performance. You should still separate your files onto different volumes even if you exclusively use a storage area network (SAN).

More discrete storage I/O on a physical server with dedicated drives means better performance. But even in a SAN, separating files onto different volumes is also done for stability. Think of the volumes as bulkheads on a submarine. If a volume fills and has no available space, files cannot be allocated additional space. On the OS volume, running out of free space would result in Windows Server stability issues—user profile and remote desktop problems at least—and affect other applications.

Important SQL Server volume settings

There are some settings to consider for volumes that host SQL Server data and log files, and this guidance applies specifically to these volumes. For other volumes—for example, those that contain the OS, application files, or backup files—the default Windows settings are acceptable unless otherwise specified.

When adding these volumes to Windows, there are important volume configuration settings that you must examine or discuss with your storage administrator:

  • When creating new drives, opt for GUID Partition Table (GPT) over Master Boot Record (MBR) disk types for new SQL Server installations. GPT is a newer disk-partitioning scheme than MBR, and GPT disks support files and volumes larger than 2 TB. In contrast, the older MBR disk type is capped at 2 TB.

  • The appropriate file unit allocation size for SQL Server volumes is 64 KB, with few exceptions. Setting this to 64 KB for each volume can have a significant impact on storage efficiency and performance. The Windows default is 4 KB, which is not optimal for SQL Server data and log files.

    To check the file unit allocation size for an NT File System (NTFS) volume, run the following from the Administrator: Command Prompt, repeating for each volume:

    fsutil fsinfo ntfsinfo d:

    The file unit allocation size is returned with the Bytes Per Cluster; thus, the desired 64 KB would be displayed as 65,536 (bytes). If formatted as the default, this will display 4096. Correcting the file unit allocation size requires formatting the drive, so it is important to check this setting before installation.

    If you notice this on an existing SQL Server instance, your likely solution is to create a new volume with the proper file unit allocation size and then move files to the new volume during an outage. Do not format or re-create the partition on volumes with existing data; you will lose the data when it is reformatted.

    Modern storage devices are currently in a transition between disks that use a Bytes per Physical Sector size of 512 bytes (the old standard) and “4K Native” disks that have both a Bytes per Sector size and a Bytes per Physical Sector size of 4 KB. Usually, a DBA will not notice or even be aware of this difference. When configuring availability groups or log shipping between servers on different storage systems with mixed Bytes per Physical Sector modes, however, this can result in very poor performance, with the transaction logs unable to truncate, and the error message “There have been nnn misaligned log IOs which required falling back to synchronous IO.” You may encounter this with hybrid availability groups spanning on-premises and Azure VM–based SQL Server instances, for example.

    This cannot be resolved via reformatting, but can potentially be resolved via hardware-level storage or firmware settings. To avoid this, all storage that hosts the transaction log files of SQL Servers in an availability group or log shipping relationship should have the same Bytes per Physical Sector.

    A workaround is to apply Trace Flag 1800 as a startup flag on the SQL Server instances that use storage without having a Bytes per Physical Sector setting of 4K. TF1800 overrides disk default behavior and writes the transaction log in 4-KB sectors, resolving the issue. TF1800 must be enabled on the on-premises SQL Server instances in the case of using the older on-premises and Azure VM availability group.

    Check the Bytes per Physical Sector setting of a volume by using the same fsutil command noted in the previous code sample.

  • A hardware-level concept related to file unit allocation size called disk starting offset deals with how Windows, storage, disk controllers, and cache segments align their boundaries. Aligning disk starting offset was far more important before Windows Server 2008. Since then, the default partition offset of 1,024 KB has been sufficient to align with the underlying disk’s stripe unit size, which is a vendor-determined value and rarely a concern for DBAs. Still, it should be verified upon first use of a new storage system or upon the migration of disks to a new storage system. This can be verified in consultation with the drive vendor’s information.

    To access the disk starting offset information, run the following from the Administrator: Command Prompt:

    wmic partition get BlockSize, StartingOffset, Name, Index

    A 1,024-KB starting offset is a Windows default, which is displayed as 1048576 (bytes) for Disk #0 Partition #0.

    Like the file unit allocation size, the only way to change a disk partition’s starting offset is destructive: You must re-create the partition and reformat the volume to align with the vendor-supplied offset.

SQL Server editions

The following are brief descriptions of all the editions in the SQL Server family, including past editions that you might recognize. It’s important to use the appropriate licenses for SQL Server, even in preproduction systems.

NOTE

This book is not intended to be a reference for licensing or sales-related documentation; still, editions are a key piece of knowledge for SQL Server administrators to understand what features may or may not be available.

  • Enterprise. Appropriate for production environments; not appropriate for preproduction environments such as user acceptance testing (UAT), quality assurance (QA), testing, development, or a sandbox. For these environments, you should instead use the free Developer edition. You’ll have a far easier time in a licensing audit if your preproduction environment installations are Developer edition.

  • Developer. Appropriate for all preproduction environments, especially those under a production Enterprise edition. Not allowed for production environments. This edition has the same features and capacity as Enterprise edition and is free.

  • Standard. Appropriate for production environments. Lacks the scale and compliance features of Enterprise edition required in some regulatory environments. Limited to the lesser of 4 sockets or 24 cores and 128 GB of buffer pool memory, whereas Enterprise edition is limited only by the OS for compute and memory.

  • Web. Appropriate for production environments but limited to low-cost server environments for web hosting.

  • Express. Not appropriate for most production environments or preproduction environments. Appropriate only for environments in which data size is small, is not expected to grow, and can be backed up with external tools or scripts (because Express edition has no SQL Server Agent to automate backups). The free Express edition is ideal for production proofs-of-concept, lightweight applications, and student projects. It lacks some critical features and is severely limited on compute (lesser of 1 socket or 4 cores), available buffer pool memory (1,410 MB), and individual database size (10-GB capacity).

  • Express with Advanced Services. Like Express edition in all caveats and limitations, this edition includes some additional features, including R integration and full-text search.

  • Evaluation. Functionally the same as Enterprise edition, and free with a 180-day shutdown timer. Evaluation edition isn’t supported. This edition can be upgraded to any edition except for Express. Do not use this edition if you plan for a clustered installation, because an upgrade in that case is not supported.

    It’s worth noting that the hardware limitations of SQL Server editions have not changed since SQL Server 2016.

NOTE

When you run the SQL Server 2022 Setup, you can choose to install several features outside the core database features. Installing SQL Server features on multiple Windows servers requires multiple licenses per server, even if you intend to install each SQL Server instance’s features only once.

There is an exception to this rule, however: If you have licensed all physical cores on a host server for SQL Server Enterprise edition, and purchased Software Assurance, you can install any number or combination of SQL Server instances and their standalone features on virtual guests.

Change SQL Server editions and versions

Upgrading editions in-place is supported by a feature of the SQL Server 2022 installer. You can upgrade in the following order: Express, Web, Standard, and Enterprise.

You cannot downgrade a SQL Server version or licensed edition. This type of change requires a fresh installation and migration. For example, you cannot downgrade in-place from SQL Server 2022 Enterprise edition to Standard edition.

In-place upgrades for major versions (from 2019 to 2022, for example) is supported but not recommended. Instead, we strongly recommend that you perform a fresh installation of the newer version and then migrate from old to new instances. This method offers major advantages in terms of duration of the planned outage, rollback capability, and robust testing in parallel.

Although in-place upgrades to SQL Server 2022 are not recommended, upgrades are supported for versions as old as SQL Server 2012 SP4. You can even migrate databases using detach and reattach, from older versions of SQL Server to SQL Server 2022, as long as the source database compatibility level is 90 or higher. Databases with a compatibility of 90 (SQL Server 2005) will be automatically upgraded to compatibility level 100. Databases already at compatibility level 100 will not change.

A supported upgrade also assumes that the OS and previous version of SQL Server are not 32-bit installations. Beginning with SQL Server 2016, SQL Server is available only for 64-bit platforms. For more information on upgrades to SQL Server 2022, visit https://learn.microsoft.com/sql/database-engine/install-windows/supported-version-and-edition-upgrades-2022.

Save to your account

This chapter is from the book