The first step we need to take before installing SQL Server on Linux is to understand that although the feature is titled SQL Server on Linux, there is a selection of supported Linux distributions. At the time of writing this book, the supported distributions were:

• Red Hat Enterprise Linux (version 7.3 or 7.4)
• SUSE Linux Enterprise Server (v12 SP2)
• Ubuntu 16.04

If you are knowledgeable about Linux, you will probably know that these listed distributions being supported means that other distributions will be able to host SQL Server. This is the case on a technical level, as the operating systems that are variants based on one of the supported distributions should pose no problems. However, Microsoft has limited resources to be able to provide enterprise level support for multiple Linux distributions and therefore focused their efforts on distributions found by the majority of customers/potential customers.

For this chapter, we will be using Ubuntu to perform the installation and demos. The main difference between the distributions as far as installing SQL Server are the commands to download and install SQL Server.

Further details on specific distributions can be found on MSDN: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup#platforms.

Once our Ubuntu server has been downloaded, installed and prepared for our first use we need to download and install the SQL Server binaries.

The first thing to do is to download and associate the PGP key from Microsoft so that our system can trust the applications that are published by Microsoft:

wget -qO- https://packages.microsoft.com/keys/microsoft.asc 
sudo apt-key add -

The next step is to register the official Microsoft repository (all on one line):

sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2017.list)"

This now allows our Ubuntu machine to access the official package repository of Microsoft, containing all the necessary files and dependencies to be able to download and install SQL Server onto the Linux machine:

sudo apt-get update
sudo apt-get install -y mssql-server

The following screenshot shows the installation of SQL Server on Linux:

We see that the installation of SQL Server on Linux is a much easier prospect than on Windows. Effectively, a single line is needed once the Microsoft repository has been registered in the operating system.

Our final step to complete the installation is to configure the instance for use:

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

The following screenshot shows the output of the preceding code:

The first configuration step is deciding the edition we want to use. Although Linux operating system distributions are available without licensing costs, SQL Server on Linux remains a commercially available product and does require a license. The preceding screenshot named Custom image deployment shows the editions that are available, with notations about each edition's usage and cost.

The last step is to assign the sa password to the instance.

At this point, the SQL Server is installed, configured, and potentially ready for use. A quick test to verify that the service is running correctly at a service level will confirm this:

systemctl status mssql-server

The result preceding code is as shown in the following screenshot:

Our SQL Server is now ready for action. However, Linux does not natively provide local connectivity and tooling to access SQL Server. We can either connect using SSMS from a remote machine (which works out of the box, provided the firewall is open to the SQL Server port) as with any SQL Server, or download and install tools inside Linux.

To add the Linux SQL tooling, we must add another repository and then install the Unix ODBC developer package:

sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/16.04/prod.list)"
sudo apt-get update
sudo apt-get install -y mssql-tools unixodbc-dev

This package provides SQLCMD and bcp to Linux machine. These are simplified tools that have existed for as long as SQL Server has existed. They are command-line interfaces for SQL Server and will allow us to connect to SQL Server and issue commands or export data. At the time of writing, there was no cross-platform support for SSMS and no such support is on the roadmap. SQL Server Operations Studio is the cross-platform tool of choice supplied by Microsoft and can be installed on Windows, macOS, or Linux (the supported distributions already mentioned in this chapter).

When we install the SQLCMD tool onto a Windows machine, the path to SQLCMD is registered for the user (allowing us to directly call SQLCMD regardless of the current folder location on the command prompt). To achieve the same "ease of use" using the Linux tools, we need to register the paths to the tools into our user session. We are registering the path of the tools 'bin' folder into the bash profile, so that we can directly call 'sqlcmd' without having to specify the full path to the command each time we wish to use it. This is done by entering the following three lines of commands/code:

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

We can then connect to the instance using sqlcmd as we usually would on a Windows installation, by calling 'sqlcmd' without the full path to the mssql-tools/bin folder:

sqlcmd -S localhost -U SA -P '<YourPassword>'
SELECT name FROM sys.databases
GO

The execution of the preceding code is as shown in the following screenshot:

At this point, we have a regular SQL Server that can be queried and used just as any other SQL Server. From the outside, it is not even apparent that the server is running on Linux (just like the container variant of SQL Server). The programming experience and application connectivity are exactly the same as a SQL Server hosted on Windows. This is in keeping with the goal of transparency from a programming standpoint. It shouldn't matter to an application developer or a user where the SQL Server is hosted; a database is a database.

A further magic functionality around SQL Server on Linux is the process of installing patches and updates for SQL Server. Because the Linux operating system and filesystem separate processes from files, it is possible to upgrade files when they are in use (unlike in Windows). This functionality allows files to be updated without the dreaded Windows reboot. When it's time to apply an update to a SQL Server on Linux, we are able to continue using the package manager, pull down updated binaries, and apply them to the server:

sudo apt-get update
sudo apt-get install mssql-server

This command will update the installed instance to the latest version available in the official Microsoft repository previously registered to the Linux operating system. No system or user databases are affected by this action (just as with an update of a Windows hosted SQL Server instance).

It is equally simple to downgrade an instance (within a major version). You issue an install and provide the desired version number:

sudo apt-get install mssql-server=<version_number>
sudo systemctl start mssql-server

This will allow a seamless rollback from a particular build to a previous build, which is much simpler than a downgrade on a Windows-hosted SQL Server.