(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)
I have already shown you in the past how to install and run SQL Server in Docker Containers, and how to deploy Availability Groups and stand-alone SQL Server Instances into Kubernetes Clusters. In today’s blog posting I want to continue this discussion and you will learn how to install and configure SQL Server on Linux.
SQL Server 2019 CTP 2.3 is currently supported on the following Linux distributions:
- Red Hat Enterprise Linux 7.3, 7.4, 7.5, or 7.6 Server
- SUSE Enterprise Linux Server v12 SP2
- Ubuntu 16.04 LTS
To show you how you can run SQL Server 2019 on Linux, I have chosen Ubuntu 16.04 for this blog posting. Because a lot of my readers are currently not yet familiar with Linux itself (who though a few years ago that we will be able to run SQL Server on Linux?), I also want to show you how to install and configure Ubuntu from scratch.
Installing Ubuntu 16.04
In my case I will deploy SQL Server 2019 onto an Ubuntu Virtual Machine that I’m running in my serious powerful home lab. As you can see in the following picture, I have attached an additional hard disk which is attached to the high-performance VMware Paravirtual SCSI driver. We will store later on this dedicated hard disk all SQL Server Data and Transaction Log files.
I don’t want to cover every single step of the Ubuntu installation, because it’s really straightforward. But I want to discuss a little bit about the Disk Partitions that are created during the installation. The following picture shows you the options that you have during the setup.
I have chosen here the first option, which partitions the whole disk. Because we have attached 2 individual hard disks to our Virtual Machine, you also have to select the correct disk in the next step.
In my case, the smaller OS disk (16 GB) is /dev/sdb, and the larger 300 GB disk for the SQL Server Data and Transaction Log files is /dev/sda. Therefore, I have only partitioned /dev/sdb during the installation. The other disk – /dev/sda – has no partition yet. We will partition that disk later prior the SQL Server installation itself.
I have also installed the OpenSSH Server during the installation, so that I can access the Virtual Machine later through an SSH connection.
And finally, I have installed the GRUB Boot Loader on the /dev/sdb hard disk.
Network Configuration
If you have completed the Ubuntu installation (which only takes a few minutes), we have to configure in the next step our network connection. By default, your Ubuntu Virtual Machine will get its IP address from the DHCP server in your network. You can get this IP address from the ifconfig command. You can use it to connect through an SSH connection to the Virtual Machine itself.
To make everything as reliable as possible, I always give a server machine a fixed IP address. To perform that task on Ubuntu, you have to change the file /etc/network/interfaces accordingly. Currently it is configured for a DHCP server – as you can see in the next picture.
Therefore, I have changed that file as follows to give the Virtual Machine an IP address in the 192.168.100.0 subnet:
Afterwards I have performed a reboot to change the IP address through a sudo reboot command.
Disk Management
As I have mentioned in the beginning of this blog posting, we will store the SQL Server Data and Transaction Log files on a different hard disk to separate them from the OS and SQL Server installation itself. Therefore, we have an additional 300 GB disk attached to our Virtual Machine, which is presented as /dev/sda to our Ubuntu installation. To verify that /dev/sda has currently no partition on it, you can run the following command:
sudo parted -l
As you can see, there is no Partition Table on /dev/sda:
So, let’s create now through the following command a new ext4 file system on /dev/sda:
sudo mkfs -t ext4 /dev/sda
When you now check again the partitions, you can see that /dev/sda has now a partition:
By now we have created a new ext4 partition, but that new partition doesn’t exist anywhere within the Linux Directory Hierarchy, because we have to mount it somewhere. In my case I want to mount the newly created partition at the path /sql. So, let’s create in the first step that new directory:
cd /
sudo mkdir sql
And now let’s mount our new ext4 partition at this mount point:
sudo mount -t ext4 /dev/sda /sql
And after we have mounted our new partition, let’s create the necessary directories for the SQL Server Data and Transaction Log files:
cd sql
sudo mkdir data
sudo mkdir log
Note: in a real-world scenario I would also recommend storing critical Transaction Log Files on their own dedicated disks for maximum performance.
When you now run the df command, you can see that we have around 300 GB of free space at the mount point /sql:
Everything is great so far, but as soon as you would restart your Virtual Machine, the mount point /sql would be gone, because it is not automatically mounted during the boot process. To automatically mount a file system during the boot process, we have to change the file /etc/fstab – the so-called File System Table. That file contains for each mounted partition an entry. Each partition is here referenced by a so-called UUID – an universal identifier. The UUID of our created ext4 partition can be retrieved through the blkid command:
Based on that information, we can create now a new entry in the /etc/fstab file for our partition:
By now everything is configured correctly, so that we can install and configure SQL Server 2019 in the next step.
Installing SQL Server 2019
Installing SQL Server 2019 is a quite easy and straightforward process, because Microsoft provides us a simple package that does the real work for us. As a first step you have to import the public repository GPG keys:
wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add –
And then we add the repository for the SQL Server 2019 CTP version:
sudo add-apt-repository “$(curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-preview.list)”
And now let’s install SQL Server 2019 – it’s easy as ABC:
sudo apt-get update
sudo apt-get install -y mssql-server
As soon as you have installed SQL Server 2019, we also have to configure SQL Server through the provided configuration program /opt/mssql/bin/mssql-conf. You can think about that program like the SQL Server Configuration Manager on a Windows-based SQL Server installation. Let’s run now that program with the argument setup.
sudo /opt/mssql/bin/mssql-conf setup
In the first step you are asked about which SQL Server edition you want to configure:
In the next step you have to accept the EULA:
And then – the configuration program just crashed! It reported the following error message:
ValueError: unknown locale: UTF-8
After some research, I have found out that I had to run the following command, which fixed that problem:
export LC_ALL=en_US.UTF-8
I was able to continue with the next step in the configuration by specifying the password for the sa account:
And finally, SQL Server is up and running – on Ubuntu!
Configuring SQL Server
As I have mentioned previously, we want to store all our SQL Server Data and Transaction Log files under the mount path /sql, which is stored on a separate disk. Unfortunately, the setup program doesn’t give us the option to store the system databases in a different path. They are always stored by default at /var/opt/mssql/data.
The challenge is now to move all system databases (master, model, tempdb, msdb) to the path /sql. First of all, we must make sure that the mssql user (under which the SQL Server service is executed) has the correct permissions for the /sql mount point. With the chown command you are able to change the owner for a given directory:
sudo chown mssql /sql
sudo chown mssql /sql/data
sudo chown mssql /sql/log
And finally, we use the chgrp command to change the group membership to the mssql user:
sudo chgrp mssql /sql
sudo chgrp mssql /sql/data
sudo chgrp mssql /sql/log
First of all, we have to move the master system database to the new location in the file system. We can use here again the configuration program /opt/mssql/bin/mssql-conf to set the file location of the Data and Transaction Log file accordingly:
sudo /opt/mssql/bin/mssql-conf set filelocation.masterdatafile /sql/data/master.mdf
sudo /opt/mssql/bin/mssql-conf set filelocation.masterlogfile /sql/log/mastlog.ldf
Then we stop the SQL Server service:
sudo systemctl stop mssql-server
And we move the file in the file system:
sudo mv /var/opt/mssql/data/master.mdf /sql/data/master.mdf
sudo mv /var/opt/mssql/data/mastlog.ldf /sql/log/mastlog.ldf
And we restart the SQL Server service:
sudo systemctl restart mssql-server
By now we have moved the master database, but the model, tempdb, and msdb databases are still at their old original location:
Therefore, let’s alter their file paths within SQL Server through a few ALTER DATABASE commands:
ALTER DATABASE model MODIFY FILE ( NAME = modeldev, FILENAME = '/sql/data/model.mdf' ) GO ALTER DATABASE model MODIFY FILE ( NAME = modellog, FILENAME = '/sql/log/modellog.ldf' ) GO ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev, FILENAME = '/sql/data/tempdb.mdf' ) GO ALTER DATABASE tempdb MODIFY FILE ( NAME = templog, FILENAME = '/sql/log/templog.ldf' ) GO ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBData, FILENAME = '/sql/data/MSDBData.mdf' ) GO ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBLog, FILENAME = '/sql/log/MSDBLog.ldf' ) GO
Then we stop again the SQL Server service:
sudo systemctl stop mssql-server
And we move the remaining files in the file system:
sudo mv /var/opt/mssql/data/model.mdf /sql/data/model.mdf
sudo mv /var/opt/mssql/data/modellog.ldf /sql/log/modellog.ldf
sudo mv /var/opt/mssql/data/tempdb.mdf /sql/data/tempdb.mdf
sudo mv /var/opt/mssql/data/templog.ldf /sql/log/templog.ldf
sudo mv /var/opt/mssql/data/msdbdata.mdf /sql/data/msdbdata.mdf
sudo mv /var/opt/mssql/data/msdblog.ldf /sql/log/msdblog.ldf
Note: The msdb files are reported by SQL Server with upper cases (MSDBdata.mdf, MSDBlog.ldf), but they are stored in the file system with lower cases (msdbdata.mdf, msdblog.ldf). Therefore, you also have to reference them during the mv command with lower cases, because otherwise the files are not found.
And we restart the SQL Server service again:
sudo systemctl restart mssql-server
When you now check again the location of the files within SQL Server, you can see that all databases are now finally stored at /sql:
You can also cross-check that at the file system level:
In addition, you can also set the default location for new Data and Transaction Log files:
sudo /opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /sql/data
sudo /opt/mssql/bin/mssql-conf set filelocation.defaultlogdir /sql/log
Summary
In this blog posting I have shown you how easy it is to install and configure SQL Server on Linux. It’s a really straightforward process, and within a few minutes you have a fresh new SQL Server 2019 up and running – on Linux! One of the drawbacks is that the system databases are initially stored in the folder /var/opt/mssql/data. Therefore, you have to make some changes afterwards to move them to a different location – maybe a different disk for better performance.
For a production environment I would also recommend moving the TempDb files to a separate disk and create additional TempDb data file so that you don’t introduce Latch Contention problems in TempDb.
If you want to learn more about SQL Server on Linux, Docker, and Kubernetes, I highly suggest my upcoming Live Online Training on May 13 and May 14, where I will do a more technical deep-dive about all these exiting new technologies that will change our life as SQL Server Developers and DBAs over the next years.
Thanks for your time,
-Klaus