SQL Server 2017 is the first SQL Server version that can be installed on Windows, Linux and a Docker container. This stairway series serves as a guide for the experienced SQL Server DBA to acquire skills and knowledge on this platform.
This is the first article in a new stairway series that will explore SQL Server on Linux using the most common best practices and comparing both the versions for Windows and Linux.
This series will cover the following topics:
- Linux installation
- Linux commands for DBA
- SQL Server installation
- Using the new SQL Server instance
- Installing and using SQL Server Agent on Linux
- HA/DR basics: Log Shipping
- Monitoring SQL Server on Linux
- Troubleshooting
- Advanced HA/DR: AlwaysOn Availability Groups
For the first installment of this stairway series, let’s go through the first step: the Linux server installation.
Which Linux distribution to choose?
SQL Server 2017 is currently supported only on these Linux distributions:
- RedHat Enterprise Linux 7.3 o 7.4
- Ubuntu 16.04
- Suse Linux Enterprise Server v12 SP2
SQL Server 2017 is also supported as a Docker image, which can run on Docker Engine on Linux or Docker for Windows/Mac. The docker image is based on Ubuntu 16.04.
But, which is the best Linux distribution among those supported by SQL Server 2017 ? Well, it depends on what your company needs and the confidence level you already have with the supported Linux distros.
Today, Linux is not only a free OS, but a platform to run business applications. When comparing the best Linux distros, corporate Linux users usually take care not only about the feature matrix supported by that distro, but also about support and lifetime of the product.
Red Hat is one of the market leaders of Linux distributions. Red Hat Enterprise Linux, or RHEL, entered many data centers via support deals with hardware and software vendors. Today is a stable, proven foundation that’s versatile enough for rolling out new applications, virtualizing environments, and creating a secure hybrid cloud. RHEL 7.x deliver 10 years of support, with 5 and a half years of Full support, 1 year of so-called Maintenance Support 1 and further 3 years and half for the Maintenance Support 2.
Ubuntu is a free and open source operating system and Linux distribution based on Debian. Canonical, the company behind Ubuntu, has a mission to make open source available to people everywhere, generates revenue from the sale of professional services around the distribution. New releases of Ubuntu occur every six months, while long-term support (LTS) releases occur every two years.
Suse Linux Enterprise Server (SLES) is a Linux-based operating system developed by SUSE. Major versions are released at an interval of 3–4 years, while minor versions (called "Service Packs") are released about every 18 months. From a technical perspective, SUSE Linux is more accessible than the other supported Linux distributions. SUSE is also the most administrator-friendly, thanks to its integrated YaST platform. SUSE is one of the main developers for Pacemaker High Availability, the standard tool for high-availability clusters in all current Linux distributions, used also by SQL Server 2017 to implement AlwaysOn or Failover clustering. Long term support for the supported version (v12 with SP2) is 31 March 2021.
From the SQL Server DBA point of view, there are no significant differences among the three operating systems and choosing one of them could be based on your internal knowledge, if the company already have a Linux-based machine, or the list of the features you want to implement together with your SQL Server.
For this series I chose to install an Ubuntu server 16.04. Generally speaking, this distribution is well-know for some of the included services like OpenStack cloud services, Ceph scalable distributed file system or the Metal-as-a-Service (MAAS) tool, which can be used to easily deploy OpenStack or other server types on bare-metal, all programmatically. We don’t need all these services for our purposes, but we can use Ubuntu Server just because it’s easy to setup, well-documented and supported among the Linux communities.
Installing Ubuntu
To install Ubuntu on your own machine, either physical or virtual, you have to use to the past releases download page: http://releases.ubuntu.com/16.04.4/ due to the fact that the current release is the 18.04 LTS, which is not supported by Microsoft.
I want to point out that is very important for a server to choose the long-term support (LTS) release of the Linux distribution, because it’s more stable and supported for the years to come by its vendor, with further security or feature updates.
Before starting the installation, let’s take a look at the prerequisites: Ubuntu requires a minimum of 512MB of RAM and 5GB of disk space, while SQL Server itself requires at least 3.25GB of RAM and additional 3GB of disk for the binaries. Considering that we should have at least space to save OS and SQL Server logs, system databases and a few other things, I would not create a server with less than 4GB of RAM and 20GB of disk space.
In a later level of this series we’ll see which best practices we should apply to our new SQL Server on Linux, including a multiple disk configuration to spread our data files, transaction logs and TempDB files. For now, we are assuming this is a very basic server configuration.
Let’s start with the installation: boot the system from the media containing the ISO file (for example an USB key if you are using a physical server) or connect the ISO to the virtual machine if you are creating a virtual server.
At the boot prompt you will be asked to select a language for the setup.
From the main boot menu you can choose further options to install Ubuntu on your system, do some check or rescue a broken system. We’ll cover only the basic installation of a fresh Ubuntu server.
The installer asks which language is should use and the keyboard layout (by auto-detecting the layout or by entering manually).
The installer then discovers the hardware configuration and configures the network settings using a DHCP server or gives you the option to configure it manually by going back and selecting the option “Configure the network manually”.
Next, the installer asks for the system’s name and sets up a new user that will have root access through the sudo utility. You are required to choose and confirm username and password.
For Windows users: let me explain some concepts before going further. In a Linux operating system root is the user name that by default has access to all commands and files, like the Administrator user in a Windows operating system.
At this point of the installation, we are providing the system the name of a new unprivileged user that can be used for everyday tasks. When we need superpowers, we can still run a command with the sudo utility, which elevates the privileges to the root user level and runs the specific command. This is just like the “RunAs Administrator” command in Windows.
You will be asked then if you want to encrypt your home directory, so that your stored files are not readable if the computer is stolen.
After being asked to choose the timezone, you are entered into the disk layout configuration. We have four choices, with a guided partitioning method or a manual one. I chose to use the entire disk and set up a Logical Volume Manager (LVM). Logical Volume Management is a storage device management technology that gives users the power to pool and abstract the physical layout of component storage devices for easier and flexible administration. This is like gathering existing storage devices into groups and allocate logical units from the combined space as needed.
Logical volumes can have meaningful names like "databases" or "backups". Volumes can be resized dynamically as space requirements change and migrated between physical devices within the pool on a running system or exported easily. LVM also offers advanced features like snapshotting, striping, and mirroring.
You can learn more about LVM here: http://tldp.org/HOWTO/LVM-HOWTO/index.html.
The Ubuntu base system installation then starts. During this phase of the installation process, you may be given the opportunity to select additional software to install, by selecting predefined collections of software (for example DNS server or OpenSSH server) to quickly set up your computer to perform various tasks. We can simply “continue” and skip this task, as we only need a base system.
A last step requires us to install the GRUB loader, and then we are ready to start with our new server.
GRUB or GRand Unified Bootloader, is a multiboot boot loader. A boot loader is the first software program that runs when a computer starts. It is responsible for loading and transferring control to the operating system kernel software (such as Windows or Linux). This can add the flexibility, for example, to run different operating systems from the same physical (or virtual) disk or to run specialized Linux distributions (for example to troubleshoot a failed system or to run diagnostic tools).
At the end of the installation process, you will be asked to reboot the system to start from your new Ubuntu server.
As you can see, this process is really straightforward and doesn’t require particular skills. There are a number of options that you can change by choosing the advanced installation, and we’ll learn about some of them when we’ll be talking about the best practices and the perfomance tuning.
Now, let’s log in and take a look at our new Linux machine.
After rebooting the server, when prompted type sqladmin as the username we are using to enter the system and the password you provided during the setup to enter the machine.
One of the most evident differences moving from Windows to Linux is case sensitivity, especially when creating or navigating a folder structure. The directory "Data" is not the same as "data" !
Once logged in, we can use a lot of commands, provided by the command-line interpreter, known as shell), we are using. The Bash shell is one of the most commonly used (but you can obviously change it whenever you want).
Let's see some example. With the command pwd, short for print working directory, we can show where we are located in the directory tree and then moving, for example, to another directory by using the cd command (change directory), like we do in a Windows command prompt.
We can create or edit a file from the command shell by using the program vi (visual editor), which is included in Linux/UNIX operating system since its early days, or switch to a “modern” editor like nano or vim.
We’ll talk about Linux commands in a future article: this brief introduction was intended only to show you how to log into your newly installed system and check if it works.
If you want to know more about the Ubuntu Server Linux and how to do a particular task, you can use the official documentation site at help.ubuntu.com and click on 16.04 LTS version, which is the supported edition at the time we are writing this article.
Update
With the release of the Cumulative Update 10 for SQL Server 2017 (build 14.0.3037.1) in late August, Microsoft has updated the package dependecies that prevented the installation of SQL Server 2017 on servers with Ubuntu 18.04 LTS. There may be additional actions required on some systems: if the library libcurl4 is installed, you need to remove it and install libcurl3, using the following commands:
$ sudo apt-get remove libcurl4
$ sudo apt-get install libcurl3
Microsoft recommends to install SQL Server 2017 on Ubuntu 18.04 for non-production purpose only, until thorough testing has been completed and full support has been documented.
You can find additional info on the original blog page: https://blogs.msdn.microsoft.com/sql_server_team/installing-sql-server-2017-for-linux-on-ubuntu-18-04-lts/
Summary
We learned how to setup a fresh Linux machine with Ubuntu Server 16.04, bearing in mind which prerequisites we do need to move to the next step: the SQL Server 2017 installation.
Related links
