Installing and configuring SQL Server instances and features

Contents

In this sample chapter from SQL Server 2019 Administration Inside Out, the authors review the process of installing and configuring a Microsoft SQL Server instance as well as the creation or migration of databases. They pay special attention to new features introduced in SQL Server 2019, and other recent features you may not have noticed in SQL Server 2017. They review a post-installation checklist for you to verify every time and, when necessary, direct you to where you can find other sources of information and details for critical steps elsewhere in this book.

  • What to do before installing SQL Server

  • Installing a new instance

  • Post-installation server configuration

  • Post-installation configuration of other features

In this chapter, we review the process of installing and configuring a Microsoft SQL Server instance as well as the creation or migration of databases. We pay special attention to new features introduced in SQL Server 2019, and other recent features you may not have noticed in SQL Server 2017. We review a post-installation checklist for you to verify every time and, when necessary, direct you to where you can find other sources of information and details for critical steps elsewhere in this book.

The SQL Server Setup tool content of this chapter mainly applies to SQL Server installations on Windows operating systems. The installation or creation is vastly simplified for an Azure SQL Database, Azure SQL Database managed instance, SQL Server on Linux, SQL Server in Docker containers, or Azure VMs running SQL Server. Many recommended settings in this chapter still apply for server-based platforms of SQL Server, such as in Docker containers or SQL Server on Linux. They are, after all, still very much the same SQL Server product that has always existed on Windows.

In Chapter 6, “Provisioning and configuring SQL Server databases,” we’ll cover the initial creation and configuration of databases inside the SQL Server instance. This chapter, however, focuses on server-level setup and settings.

What to do before installing SQL Server

Before running the SQL Server installer on your Windows Server, there are a number of 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, is not a choice you can easily reverse after installation. (More about the server level collation option later in this chapter, under the “Instance Collation” section.)

However, many mistakes made in installation can be resolved afterwards, likely with some tedium and outages. For example, skipping the initial default data and log directories may land all of your databases on the Operating System 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 Setup to fail. For more information on the risks and limitations, visit: https://docs.microsoft.com/sql/sql-server/install/hardware-and-software-requirements-for-installing-sql-server-ver15#DC_support.

We recommend you acquire the contents of this list prior to beginning SQL Server Setup:

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

  • The latest downloaded cumulative update 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 be generating, backups of certificates, keys

  • Whether to install the default or a named instance

  • A plan for where SQL Server files will go, and each volume formatted to 64KB disk unit allocation size

Let’s talk more about that last item now.

Deciding 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 prior to installation.

The examples in this chapter assume that your Windows operating system installation is on the C volume of your server. You should have many other volumes for SQL Server files–we’ll review a sample layout soon. One of the basic guiding principles for a SQL Server installation is that anywhere you see “C:\,” change it to another volume. This helps minimize SQL Server’s footprint on the operating system (OS) volume, especially if you install multiple SQL Server instances, and can have potential disaster recovery implications in terms of volume-level backup 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 will delete temporary files involved in their installation, but not log files or configuration files, which should have a minimal footprint. Outside of log files, we recommend that you do not delete any files installed by SQL Server Setup or cumulative updates. Instead, let’s take a look at some proactive steps to move these files off of the C volume.

Some parts of SQL Server Setup will 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\150\Setup Bootstrap\ subfolder structure, where 150 is specific to the internal version number (15.0) of SQL 2019. 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 will be, 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 “Automating SQL Server Setup by using configuration files” section 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), default database locations for user database files, and their backups. If this is not the first SQL Server 2019 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 it.

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 2019 can consume more than 14 GB.

INSIDE OUT

What can you do with the D: volume on an Azure VM?

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

The temporary storage volume in Azure VMs is a temporary disk that is locally present on the machine hosting your 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 if certain other considerations are taken. For more about this, see Chapter 3: Designing and implementing an on-premises database infrastructure. Otherwise, do not store any non-temporary files in the temporary storage.

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. OS, some SQL Server files must install 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 file. (Alternatively, use the D: Temporary Storage volume on Azure Windows VMs.)

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

And, 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 onto 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 impact to 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 four important volume configuration settings required to 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 disk support files and volumes larger than 2 TB, whereas 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: 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 prior to installation.

    If you notice this on an existing SQL Server instance, your likely resolution steps are 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 of course lose the data.

  • 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 a formatting decision, 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.

  • There is a hardware-level concept related to file unit allocation size called “disk starting offset” that deals with how Windows, storage, disk controllers, and cache segments align their boundaries. Aligning disk starting offset was far more important prior to 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 DBA’s. Still, it should be verified upon first use of a new storage system or 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: Prompt:

    wmic partition get BlockSize, StartingOffset, Name, Index

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

    Similar to 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 for all of 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; rather, editions are a key piece of knowledge for SQL administrators to understand what features may or may not be available.

  • Enterprise edition. 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, instead use the free Developer edition. You’ll have a far easier time in a licensing audit if your pre-production environment installations are Developer edition.

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

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

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

  • Express edition. 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 proof-of-concepts, lightweight applications, or 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 cap).

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

  • Evaluation Edition. Functionally the same as Enterprise edition, and free with a 180-day shutdown timer, but it isn’t supported. Can be upgraded to any edition but Express. Do not use it 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 2019 installer, you are prompted to install a number of features outside of 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, and that is if you have licensed all physical cores on a virtual 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.

Changing SQL Server editions and versions

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

It is important to note that 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 2019, from Enterprise edition to Standard edition.

In-place upgrades for major versions (from 2017 to 2019, 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 2019 are not recommended, they are supported for versions as old as SQL Server 2012 on Windows Server 2016. SQL Server 2019 is the first SQL Server version that does not support Windows Server 2012 or 2012 R2.

A supported upgrade also assumes that the operating system 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 2019, visit https://docs.microsoft.com/sql/database-engine/install-windows/supported-version-and-edition-upgrades-version-15.

Beginning with SQL Server 2019, a SQL Server upgrade from SQL Server 2008 is no longer supported, though individual databases can still be migrated to SQL Server 2019. You can attach or restore its databases to SQL Server 2019, although they will be upgraded to compatibility level 100, the version level for SQL 2008.

Save to your account

This chapter is from the book