Installing SQL Server 2019 on Linux

Since SQL Server 2017, Microsoft decided to offer its distribution on Linux. SQL Server's Linux distribution helps administrators to use familiar operating systems as well as install SQL Server to containers. This section is a step-by-step walkthrough example of the sample SQL Server installation process on Ubuntu 18.04.

Preparing the test environment

For many administrators and DBAs strongly bound to Microsoft operating systems, the world of Linux seems very strange and confusing. That's why the first step is a preparation of the Hyper-V virtual machine (VM). Microsoft provides a Hyper-V option called Hyper-V Quick Create. Its usage is very simple and straightforward, as follows:

1. In the Start menu, find the Hyper-V Quick Create application.
2. In the opened window, select Ubuntu 18.04.
3. Click the Create Virtual Machine button.
4. Follow the installation instructions.

The whole installation process is almost self-managed and takes up to 15 minutes. When your Ubuntu is ready, turn it on and connect to it using Hyper-V Manager. If everything works, you are prepared for SQL Server 2019 installation on Linux.

Installing SQL Server

The installation process of SQL Server on Linux differs from the Windows installation because of the following three points:

• It uses the Linux shell.
• All services such as SQL Server Agent or SQL Server Integration Services are installed separately.
• Not every service is supported on Linux (for instance, SSRS).

Our task is to install just the database engine of SQL Server, but the process of installation of other services is very similar.

First of all, we need to look for the Linux shell. At the bottom-left corner of Ubuntu PC is an icon that looks similar to the Windows Start button. When this icon is clicked, the main screen appears, showing all installed applications. One of these applications is Terminal. Click on it and take the following steps:

1. Execute the following command in Linux Terminal:

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

   The command imports the public repository key needed for validity checks of the downloaded build of SQL Server.

2. The second command registers the MS SQL Server Ubuntu repository from which the installation will be downloaded. The following command does the registration:

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

   The sudo command from the preceding code snippet says to the operating system that the command itself will be executed with elevated permissions. So, it's possible that you will be asked to write your password into the command line.

3. Once the repository is registered, the following two commands will install SQL Server:

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

4. After installation, you need to go through a simple configuration. You will be asked for a password and an edition of the freshly installed instance of SQL Server. The following command runs the configuration:

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

5. The last step is to check if SQL Server runs and if a connection can be established to it. Checking the service status can be done using the systemctl command, as follows:

   systemctl status mssql-server

6. The preceding command should show status Active (running). If not, execute the following command:

   systemctl start mssql-server

This command starts the SQL Server service. If anything is not correct, the result of the command will show part of the SQL Server error log with a certain error.

Testing connection to SQL Server on Linux

We should test the connection to our fresh SQL Server installation from inside as well as from outside of the Linux computer. The Linux distribution of the SQL Server service does not contain client tools, or even the sqlcmd command line. So, the first step is to install the sqlcmd command line. The process of installation is very similar to the installation of SQL Server. The following script shows all commands leading to the sqlcmd installation:

wget https://packages.microsoft.com/config/ubuntu/16.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list
sudo apt-get update 
sudo apt-get install mssql-tools unixodbc-dev
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

When all the preceding commands are executed, we can call the sqlcmd command line with the following parameters:

sqlcmd -U sa -P <strong sa password>

If the preceding command succeeds, lines in the Terminal window start to be numbered, and we can try any SQL command such as SELECT @@VERSION.

If everything works from inside, we can make the same connection test from outside of our Linux computer. During this walkthrough, we used the Hyper-V virtualization environment, so we can install Management Studio and try to connect to the SQL Server on Linux using both Management Studio or the sqlcmd command.

Note

When testing the connection to the virtual computer, use the IP address rather than the computer's name.

SQL Server on Linux works the same way as on SQL Server on Windows. From this moment, we will use SQL Server on Windows primarily, but if you wish to, enjoy your Linux distribution of SQL Server.