by Joe Grant (@dba_jedi), Principal Architect

This article will be the first in a series that discusses installing and running Microsoft SQL Server on Linux, in both physical and virtualized worlds. In this first article, we will be discussing some Linux basics to help get SQL Server admins who need a basic understanding of Linux up and going. Prior to installing Microsoft SQL Server on Linux, there are several basics that need to be understood before a few important decisions can be made.

Distributions

The first decision point is which distribution and version of Linux to use. Unlike Windows, there are several vendors that put together distributions of Linux, and each vendor uses their own version number. The starting point here is to check with Microsoft on which distributions and versions they officially support.

The supported distributions and versions are:

• Red Hat Enterprise Linux (RHEL) versions 7.3 and 7.4

– Red Hat uses minor version numbers much in the same way that Microsoft uses the Service Pack concept.
– Version 7.5 was released in April 2018. I would expect that support/certification for version 7.5 is coming soon.

• SUSE v12 SP2

 – SUSE is a European based company and thus more popular in Europe.
– Version 12 SP2 is technically 12.2. Again the minor version is similar to a Service Pack from Microsoft.

• Ubuntu 16.04 LTS

 – The version numbers for Ubuntu are based on their release date in the form of YY.MM. For example 16.04 was released in April of 2016.
– Ubuntu releases their mainstream version twice a year in April and October. These mainstream releases are left unsupported shortly after the next version is released.
– Every two years a Long Term Support (LTS) release is made available. As the release schedule and name would suggest, these releases are supported for longer periods of time.
– It appears that Microsoft is only supporting the LTS versions for Ubuntu.

In general, the versions supported were the versions generally available at the time that Microsoft announced SQL Server for Linux. It is also likely that these were the versions that MS used for testing and/or development, but that could just be a rumor I made up.

As one would expect, it is always best to stick with a supported distribution and version. Typically, the support needs and license costs will determine which distribution you eventually select. While I prefer RHEL, that is mostly due to familiarity as I have been dealing with it since roughly 2001 (wow I am getting old).

For a learning environment and first time installation that you install and work with primarily on a VM on your laptop, Ubuntu may be the easiest distribution. Ubuntu has a lot of things enabled by default that will make it easier for first time users. However for production environments, I would probably stick with RHEL as previously noted in my bias.

Storage

The next basic element to get a handle on is dealing with storage. Once we review the basics here, we can then discuss a few stupid things that Microsoft has done. For these filesystem basics, it really does help to get on a Linux system and poke around a bit. It will really help with your understanding.

Unfortunately, there is no good point to start with when discussing filesystems and mount points. There several different things that all work together and somehow you just magically need to understand them all at the same time. So here goes my best shot.

Directory Structure
The base of the directory structure is “/” referred to as either slash or root. There are several directories that exist by default, and they have specific functions. The following figure displays most of the default directories.

NOTE: We are not in a Windows world, so it is directory not folder.

Filesystem
A file system is a logical unit of storage of a fixed size. It can either be a disk, a disk partition, or a logical volume. They are usually named after the mount point that is chosen for it. A file system can be mounted anywhere within the directory structure.

Filesystem Type
Every filesystem needs a filesystem type. Not everything is NTFS or FAT. The most common types are EXT3, EXT4, and XFS. There is great debate among Linux geeks about which filesystem is best and why. For now, simply use the default for the OS, as it should work well.

Mount Point
This is the place within the directory structure where a filesystem is attached. Remember each filesystem is its own unit of storage, so everything under a certain mount point in the directory structure is a part of that storage. The root filesystem/mount point is the catch all for everything that has not been explicitly defined.

Logical Volume Manager (LVM)

The Logical Volume Manager is the tool used for managing physical disks, volume groups, and logical volumes. The general flow is:

• A physical disk (or partition) is assigned that will be managed by LVM.
• Physical disks are then assigned to a volume group.

 – A volume group is one or more physical volumes.
– LMV can be used to manage the disks at different RAID levels, or JBOD.

• A volume group is then divided into the logical volumes.
• A filesystem is then created on the logical volume.
• Once the filesystem has been created, the logical volume can then be mounted to a specific point in the directory structure.

 – Edit /etc/fstab with the logical volume info to mount the filesystem at boot time.

It is a lot of steps, but it is not nearly as complicated as it sounds. The first time working through it will hurt a bit, but once you get the hang of things it really is fairly easy. Using LVM will make managing the filesystems later on easier.

Putting it together

The following is the output from the command “ls –lh” to show how the filesystems are mounted.

Filesystem                       Size  Used  Avail   Use%  Mounted on
/dev/mapper/root_vg-root         35G   946M  35G     3%    /
devtmpfs                         2.9G  0     2.9G    0%    /dev
tmpfs                            2.9G  0     2.9G    0%    /dev/shm
tmpfs                            2.9G  8.7M  2.9G    1%    /run
tmpfs                            2.9G  0     2.9G    0%    /sys/fs/cgroup
/dev/sda1                        1014M 143M  872M    15%   /boot
tmpfs                            580M   0    580M    0%    /run/user/0
/dev/mapper/data_vg-data_lv       20G   44M  20G     1%    /sql/data
/dev/mapper/logs_vg-logs_lv       20G   44M  20G     1%    /sql/logs
/dev/mapper/tempdb_vg-tempdb_lv   20G   44M  20G     1%    /sql/tempdb

So ignore all of the “tempfs” stuff for now. In looking at / the filesystem belongs to the volume group “root_vg” and the logical volume “root.” The /sql/data filesystem belongs to the volume group “data_vg” and the logical volume “data_lv.”

Some common filesystems and their recommend sizes are:

• / – 4-25 GB

 – Anything that is not its own filesystem falls under / so it becomes a bit of a catch all, thus the variable is size here.

• /boot – 1 GB

 – This is a special filesystem that contains the Linux kernel. It needs to be its own physical disk partition.

• SWAP – 2-4 GB

 – This is a special filesystem type for memory swap. We keep it small, because with database systems if the system starts swapping, not having enough swap is the least of your concerns.

• /var – 4-8 GB

 – Technically it stands for variable. Typically this is where system log files, printer spool files, mail, and other temporary(ish) files go.

• /tmp – 2-4 GB

 – Space for temp user files.

• /home – 10+ GB

 – This is the filesystem where user home directories go.

Additional filesystems are common, but it depends on what your specific requirements are. Two additional points to keep in mind:

• If certain filesystems hit 100%, the OS will pretty much stop responding.
• Due to this fact, we try to separate the OS from software and user files.

So at this point, we have enough of an understanding that those stupid things that Microsoft has done can be discussed. The first is not necessarily stupid, but MS has chosen to place the SQL Server software in /opt.

MS chose to use /var as the default location for the database data files. I have been in a few customer shops where something like this would be ok, but for the most part this is just weird for us Linux admins. The /var filesystem is not where database data files belong. I admit /var is where MySQL places datafiles by default, but I disagree with this practice as well.

In the Oracle world, we typically use a directory standard called OFA (Optimal Flexible Architecture) and it will work well for SQL Server as well. It helps to separate the OS, the database software, and the database datafiles (tempdb, log files, ect). There are a few ways to implement OFA, and for SQL Server it is recommended to create a separate filesystem for /opt (or /opt/mssql).

For the data files, a generic mount point of /u01 (or a series of mount points /u01,02,03…) can be created as a base. In the example above, the directory /sql was used as the generic directory to start, so either:

• /sql/data
• /sql/logs
• /sql/tempdb
• /sql/<whatever else is needed>

OR

• /u01 – for datafiles
• /u02 – for logs
• /u03 – for tempdb
• /u0X – for whatever else is needed.

Once the distribution is chosen and the storage has been planned out, the OS and SQL Server can be installed. The steps for those tasks will be discussed in the next blog in the series.