Active Directory Domain Services authentication

Unlike with Windows, Active Directory Domain Services (AD DS) is not built into Linux and must be installed separately. SQL Server can then use Active Directory on both operating systems, which is recommended for centralized security and management. This allows you to extend your current Active Directory (AD) to access SQL Server instances on Linux as an extension of your Windows network.

• Setting up Kerberos authentication on Linux (which AD uses) is outside of the scope of this book, but you can learn how here: https://learn.microsoft.com/sql/linux/sql-server-linux-active-directory-authentication.

• You can read more about Azure Arc in Chapter 4, and at https://learn.microsoft.com/sql/sql-server/azure-arc/overview.

File systems and directory structures

Windows Server supports NTFS and ReFS. Several file systems are supported on Linux and might differ between distribution and edition, but will usually be ext4. SQL Server is supported on both ext4 and XFS, because these two file systems provide similar features to NTFS, including journaling, large partitions, and fine-grained access control.

On Windows, individual volumes are addressed by a letter followed by a colon. For example, the default volume for a Windows OS is C: and all files beneath the root directory (C:) are addressable through that drive letter. Files are stored logically in folders, and these folders and subfolders (also called directories and subdirectories) are located beneath the root directory on each drive and separated by a backslash ().

On Linux, the directory structure is based off a root directory, called / (a single forward slash). While files are also stored in directories and subdirectories, everything, including individual drives, are addressed as subdirectories beneath the root directory separated by a slash (/).

Package managers

Every Linux distribution comes with a package manager to install, manage, update, and delete packages using online repositories. For practical purposes, this is the major difference between distributions when installing software.

When a package manager installs an application (the package), it connects to a central repository controlled by the distribution maintainer. You can also register a third-party repository to install packages outside of that distribution, provided the repository provides packages in the appropriate format.

Package managers use information from repositories to ensure that packages are compatible with one another, which makes it much easier to keep your system stable and up to date. This built-in dependency resolution means that you install only what you need. If a package is dependent on one or more other packages that are not installed, the package manager will automatically install those dependencies.

Run commands with elevated privileges

Many commands for installing, configuring, and administering Linux require administrative privileges, just like on Windows. The commands are preceded by the sudo keyword, which stands for superuser do. This is less risky than logging in as the superuser account (root). You will be prompted for the root password when you run sudo for the first time after a fixed period (usually 15 minutes).

Configure high performance

Aside from the computer’s BIOS, where high performance should be enabled (in other words, power saving should be disabled), you can also modify CPU settings at the Linux kernel level:

• Set the CPU frequency governor to 100% using the cpupower command.

• Use the performance option with the x86_energy_perf_policy command.

• Set the min_perf_pct setting to 100%.

• Set C-States to C1 only.

These CPU settings are functionally equivalent to enabling High Performance on Windows.

• You can read more about CPU C-States and power-saving modes at https://www.hardwaresecrets.com/everything-you-need-to-know-about-the-cpu-c-states-power-saving-modes.

Configure NUMA nodes

For computers with more than one NUMA node, use the sysctl command to disable auto-NUMA balancing, setting kernel.numa_balancing to 0. You do this because SQL Server handles NUMA internally.

Configure virtual address space

Chapter 2 discussed the working set, or memory provided by the OS for use by a process—in this case, SQL Server. The working set resides in a virtual address space, which in turn is mapped to physical memory by internal OS structures. The default setting for the number of memory map areas in virtual memory might not be sufficient for SQL Server on Linux, so you should use the sysctl command to change vm.max_map_count to the upper limit of 262144 (262,144).

Configure Transparent Hugepages

Certain Linux distributions, including Red Hat, provide improved performance on systems by increasing the size of memory blocks transparently to the underlying process. This is beneficial for applications with contiguous memory access patterns and works to SQL Server’s advantage. Transparent Hugepages (THP) is already enabled on Linux by default, so you should leave this on for a dedicated SQL Server instance.

Set up the file system

As mentioned in Chapter 3, “Design and implement an on-premises database infrastructure,” SQL Server on Linux requires either the ext4 or XFS file system. If the file system supports it, use a 64-KB block size to match the size of an extent. Otherwise, use the largest size that it supports. (For example, ext4 may only support a block size of 4 KB.) With newer storage subsystems and SANs, this block size is less important than it used to be.

By default, SQL Server places the data and log files in /var/opt/mssql/data. Notice that this path starts with a single forward slash, which is the root directory on a Linux system. This path then includes var, which is a default directory created for the OS to write data during normal operation. (var stands for variable.) Then, opt (which stands for optional) is for optional packages that are not included in a default installation of Linux. The next directory is mssql, which stands for Microsoft SQL.

A good practice is to keep transaction log files and tempdb on your fastest available storage. Additionally, you should mount volumes using the noatime attribute, which prevents tracking the last accessed time for that volume, thereby improving performance. This is managed in the file system table configuration file known as fstab. Refer to the fstab documentation for more information.

Recommended disk settings

For optimal settings at the physical disk level, you can set the disk read-ahead to 4096 bytes using the blockdev command. Additionally, there are several settings you can configure using the sysctl command:

• Set kernel.sched_min_granularity_ns to 10000000 (10,000,000).

• Set kernel.sched_wakeup_granularity_ns to 15000000 (15,000,000).

• Set vm.dirty_ratio to 40.

• Set vm.dirty_background_ratio to 10.

• Set vm.swappiness to 10.

Download the third-party repository configuration file

You must download SQL Server packages directly from Microsoft because they are not available from the official distribution repositories. You must add the Microsoft package repository to the list of approved repositories on your computer, depending on which version of the Linux distribution you have installed.

The following sample bash command loads the package repository configuration for SQL Server 2022 for RHEL 8.x into the repository on your machine. In this example, the trailing space and backslash at the end of the first line are a bash convention to indicate that the command spans more than one line:

Click here to view code image

sudo curl -o /etc/yum.repos.d/mssql-server.repo 
https://packages.microsoft.com/config/rhel/8/mssql-server-2022.repo

Download the package

Once the package manager is configured to accept packages from Microsoft and the list of available packages is refreshed, you can install SQL Server immediately or download the packages for offline installation on another computer. For RHEL, the following command will download the package locally (as well as any dependencies):

Click here to view code image

sudo yum localinstall mssql-server_2022.x86_64.rpm

Install the package

Depending on the distribution and package manager, you will install the SQL Server Database Engine package using one of the following methods:

• RHEL. sudo yum install -y mssql-server

• SLES. sudo zypper install -y mssql-server

• Ubuntu. sudo apt-get install -y mssql-server

Each install command has a -y switch, which forces any prompts to agree to the question in the affirmative. This is useful for agreeing to install dependencies without prompting for each one—for example, in unattended installs (see the next section). If you want to confirm every prompt manually, you can remove the -y switch.

Perform an unattended installation

You can also install SQL Server on Linux using a shell script, which is recommended for production deployments and deployments across multiple servers, to ensure a consistent experience. The script should include all the steps you need to register the Microsoft package repository, download the requisite packages, and perform the post-installation configuration.

See the “Configure SQL Server on Linux” section later in this chapter for more information about post-installation steps. You can also get a sample bash script from Microsoft Docs, depending on the distribution you have chosen, from the following locations:

• RHEL. https://learn.microsoft.com/sql/linux/sample-unattended-install-redhat

• SLES. https://learn.microsoft.com/sql/linux/sample-unattended-install-suse

• Ubuntu. https://learn.microsoft.com/sql/linux/sample-unattended-install-ubuntu

Update packages

With the Microsoft SQL Server package repository as an approved third-party option, the distribution’s package manager will update SQL Server components (including cumulative updates) at the same time as other OS updates. This is functionally equivalent to the Windows Update feature titled “Receive updates for other Microsoft products.”

To update the packages on your Linux OS, including any updates of SQL Server components you have installed, use the following commands:

• RHEL. sudo yum update

• SLES. sudo zypper update

• Ubuntu. sudo apt-get update

Configuration settings

The executable package for mssql-conf is in the /opt/mssql/bin path. You can see that /opt is a directory off the root directory where optional packages are stored, and mssql stands for Microsoft SQL. The bin directory stands for binaries, which are functionally the same as executable files on Windows.

mssql-conf uses a configuration file (called mssql.conf), which is a plain text file located at /var/opt/mssql/mssql.conf. Remember that /var is where files are written to, which is a convenient way to remember the difference between /opt and /var/opt.

The mssql-conf tool has two phases: first-run (initial) setup, and configuration, both of which we cover next.

First-run setup

From bash, run the following command to enter the configuration tool to configure SQL Server 2022 on Linux:

Click here to view code image

sudo /opt/mssql/bin/mssql-conf setup

You are presented with a numbered list. If you make an error, you can quit the tool and run it again.

Choose the correct edition

SQL Server on Linux might not ask you to enter a license key. When you set up SQL Server for the first time, you are prompted for the edition you will be using. The following editions are available (this list is copied from the mssql-conf setup prompt):

1. Evaluation (free, no production use rights, 180-day limit)

2. Developer (free, no production use rights)

3. Express (free)

4. Web (PAID)

5. Standard (PAID)

6. Enterprise (PAID)—CPU Core utilization restricted to 20 physical/40 hyperthreaded

7. Enterprise Core (PAID)—CPU Core utilization up to Operating System Maximum

8. I bought a license through a retail sales channel and have a product key to enter.

Make your selection based on the edition you want to install and move on to the next prompt. You are shown the path to the license agreement, and then prompted to accept that agreement.

The Evaluation, Developer, and Express editions are free and do not require a paid license, but you will still be prompted to agree to the license terms. A good guideline for choosing a license is that if you plan to process production data under any circumstances (including if you want to test your database backups), you cannot use the Evaluation or Developer edition, and must purchase a paid license. Alternatively, you can use the artificially limited Express edition.

Choose the language

Now you are prompted to choose a default language for the SQL Server instance. You can choose from among 11 different options: English, German, Spanish, French, Italian, Japanese, Korean, Portuguese, Russian, Simplified Chinese, or Traditional Chinese.

Choose a SQL Server system administrator password

Your system administrator (SA) password should be a strong password. Microsoft’s guidance is to choose an alphanumeric password with a minimum length of eight uppercase and lowercase characters, including letters, digits, and symbols. If you plan to use AD authentication, you can disable this account later.

After you choose a password, you will be prompted to confirm it. Once that’s done, the SQL Server service will restart, taking these settings into account.

Configure the SQL Server instance

The configuration settings for SQL Server are managed using the same mssql-conf tool, replacing the SQL Server Configuration Manager on Windows.

There is a wide range of settings that you can use to customize your SQL Server instance. This is a brief overview of what is available (taken from https://learn.microsoft.com/sql/linux/sql-server-linux-configure-mssql-conf):

• Agent. Enables SQL Server Agent. Although SQL Server Agent is installed along with the Database Engine, you still need to enable it.

• Collation. Sets a new collation for SQL Server on Linux.

• Customer Feedback. Specifies whether SQL Server sends feedback to Microsoft. This option cannot be disabled on free editions.

  • You can read more about the collection of usage and diagnostic data at https://learn.microsoft.com/sql/sql-server/usage-and-diagnostic-data-configuration-for-sql-server-tools.

• Database Mail Profile. Sets the default database mail profile for SQL Server on Linux.

• Default Data Directory. Sets the default directory for new SQL Server database data files (.mdf). As noted in Chapter 3, we recommend moving this to a dedicated volume.

• Default Log Directory. Changes the default directory for new SQL Server database log (.ldf) files. As noted in Chapter 3, we recommend moving this to a dedicated volume.

• Default Master Database Directory. Changes the default directory for the master database and log files. As noted in Chapter 3, we recommend moving this to a dedicated volume.

• Default Master Database File Name. Changes the name of master database files. We do not recommend changing this in the normal course of business, but it is useful in a disaster recovery scenario when restoring a master database.

• Default Dump Directory. Changes the default directory for new memory dumps and other troubleshooting files. You can set the dump file type using the Dump Type option (see below).

• Default Error Log Directory. Changes the default directory for new SQL Server ERRORLOG, Default Profiler Trace, System Health Session XE, and Hekaton (Memory-Optimized) Session XE files.

• Default Backup Directory. Changes the default directory for new backup files. We recommend moving this to a dedicated volume separate from the data and log files to ensure business continuity should a drive failure occur.

• Dump Type. Sets the type of memory dump file to collect in the event of a crash or exception in a SQL Server process. Each size setting (Mini, Miniplus, Filtered, and Full) provides a different level of detail in a memory dump for troubleshooting purposes. You can set the path for the dump directory using the Default Dump Directory option (see above).

• High Availability. Enables availability groups.

  • See Chapter 11, “Implement high availability and disaster recovery,” for more information about availability groups on Linux.

• Local Audit Directory. Sets a directory to add local audit files.

• Locale. Sets the locale for SQL Server to use, in the form of a language code identifier (LCID).

• Memory Limit. Sets the memory limit for SQL Server. Avoid going over the default of 80 percent of the maximum physical memory available on the server; Linux will terminate the SQL Server instance without warning if it detects high resource utilization.

  • You can read more about the aptly named out-of-memory (OOM) killer at https://lwn.net/Articles/317814.

• TCP Port. Changes the port on which SQL Server listens for connections. Do so only if you have a specific business case. We do not recommend changing it for security reasons because a network sniffing tool will detect the new port almost instantly.

• TLS. Configures Transport Layer Security (TLS). This is used to enforce encryption and to configure the path to the certificate and private key. You can also set TLS versions here, but Microsoft recommends only using TLS 1.2 (and higher when available). We cover TLS in more depth in Chapter 13. If you use Kerberos authentication to connect your SQL Server instance to AD, the Kerberos keytab file is also configured here.

• Trace Flags. Sets the trace flags that the service is going to use. We recommend enabling at least Trace Flag 3226, which disables messages in the error log for successful database backups.