Install and configure SQL Server instances and features
- By Randolph West, William Assaf, Joseph D'Antoni, Louis Davidson, Meagan Longoria, Elizabeth Noble
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.
For more on SQL Server on Linux, see Chapter 5, “Install and configure SQL Server on Linux.”
For more on Azure SQL Database, see Chapter 17, “Provision Azure SQL Database.”
For more on Azure SQL Managed Instance, see Chapter 18, “Provision Azure SQL Managed Instance.”
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.
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)
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.
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.
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
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.
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.
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.