SQL Server On Linux Installation and Configuration

The previous articles, Introduction to SQL Server on Linux and Overview of SQL Server on Linux requirements and comparison with Windows SQL Server, begin your journey into SQL Server on Linux. Traditionally, Microsoft SQL Server is a Windows-based relational database. Starting from SQL Server 2017, you can choose your supported OS from below:

• Red Hat Enterprise Linux 7.7 – 7.9, or 8.0 – 8.3 Server

• Ubuntu 16.04 LTS, 18.04 LTS, 20.04 LTS

• SUSE Enterprise Linux Server v12 SP3 – SP5

• Docker Engine 1.8+ on MAC, or Linux

• Deploy container using OpenShift and Kubernetes

This article will cover the SQL Server Linux installation configuration on Ubuntu 20.04.2 LTS.

System requirement for SQL Server on Linux

The minimum system requirements are similar for SQL Server Linux on all supported Linux platforms:

• Minimum OS memory: 2 GB

• Minimum free disk space: 6 GB

• Processor: 2 cores with 2 GHz, x-64 compatible only

• File system: XFS or EXT4

SQL Server on Linux repositories

SQL Server on Linux is available using the Microsoft repositories for both SQL Server 2017 and 2019 versions. These repositories are available for the database engine and related SQL Server packages. The following table lists SQL Server 2019 Linux repository:
 

Install SQL Server 2019 Linux on Ubuntu 20.04

This section will explore installing SQL Server 2019 on Ubuntu 20.04. In the Windows Server, you have options to install SQL Server using command-line and graphical mode. However, for SQL Server on Linux, there is no graphical installation available.

• Ubuntu 20.04 requires SQL Server 2019 CU 10 or later.

• You require active internet connectivity on the server to download repositories.

Launch a terminal in Ubuntu and use the following steps for SQL Server Linux installation:

Step 1: Import the public repository GPG keys

The first step requires downloading GPG keys, a public cryptography key, from the Microsoft package store.

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

Step 2: Register the SQL Server 2019 Linux Ubuntu repository

This step adds an APT repository for SQL Server 2019 Linux on Ubuntu 20.04. The add-apt-repository uses python script to add an ART repository in the file – /etc/apt/sources.list

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

You can verify that the repository is defined in the /etc/apt/sources.list file.

cat /etc/apt/sources.list

Step 3: Update the repository index

This step can apt-get update command that downloads the package information from the repository defined in the /etc/apt/sources.list.

sudo apt-get update

Step 4: Install SQL Server Linux

We are now ready to install SQL Server on Ubuntu distribution. The below command starts SQL Server installation by downloading the package from the Microsoft repository:

sudo apt-get install -y mssql-server

This step takes a little longer depending on your network speed during the mssql package download. It downloads approx 253 MB of archives:

It writes a message to run the mssql-conf command for completing SQL Server Linux installation. Suppose you are familiar with Windows SQL Server. In that case, you might have noticed that SQL Server on Linux setup did not ask for inputs such as SA password, Edition (developer, standard, enterprise, express). The mssql-conf utility configures these values for Linux SQL Server:

Step 5: Configure SQL Server on Linux

As instructed in step 3 output, execute the command, and provide inputs for the following:

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

Edition of SQL Server (1-8): Enter your desired SQL Server Linux edition number from the following list:

Enter Yes for accepting the license terms.

Select the language for SQL Server from the list of options. For English language, enter number 1. 

Enter the SQL Server administrator (SA) password: The SA password requires a minimum of 8 characters, including uppercase, lowercase letters, digits, and non-alphanumeric symbols.

It configures the SQL instance, enables force flush for log durability, and creates a link.

Verify the SQL Services status using the following query.

systemctl status mssql-server --no-pager

As shown here, the status is active (running), which shows we have configured SQL Server on Linux successfully for Ubuntu 20.04.

Install command-line tools for SQL Server

To connect with SQL Server on Linux, you can use the following client tools:

1. SQLCMD

2. Azure Data Studio

3. SQL Server Management Studio

You can install SQLCMD and Azure Data Studio on Linux as well. However, if you are familiar with SQL Server Management Studio (SSMS), You need to install it on Windows and connect Linux SQL remotely.

In this article, we will use SQLCMD for connecting SQL Server on Linux. It requires installing the command-line tools using the following script:

Step 1: Install CURL and import the public repository GPG key

sudo apt install curl 
curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list

Step 2: Register Microsoft Ubuntu repository on Ubuntu 20.04

curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list

Step 3: Update the repository sources list and install the mssql-tools package

This step updates the package repository list and installs the mssql-tools package in Ubuntu 20.04.

sudo apt-get update 
sudo apt-get install mssql-tools unixodbc-dev

Enter Y to proceed with the mssql-tools installation. Further, it asks for permissions for msodbcsql17 installation. Choose Yes in the screen below and proceed.

Accept license agreement for configuring mssql-tools.

It continues and finishes the mssql-tools and ODBC installation.

Step 4: Add the directory  /opt/mssql-tools/bin/ in the PATH environment variable in a bash shell

You can add the directory /opt/mssql-tools/bin in the PATH environment variables so that SQLCMD is accessible from the bash for the login sessions.

Similarly, you can add it for the interactive/non-login sessions in the ~/.bashrc file.

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

Connect to SQL Server on Linux and run sample commands

In the terminal, run the following SQLCMD command to connect with SQL Server on Linux.

$ sqlcmd -S localhost -U SA

Enter the SA password, and it connects to locally running SQL Server Linux. Once connected, you get prompt 1> as shown below.

Let’s verify the SQL Server version using the command – SELECT @@VERSION. As shown below, we installed Microsoft SQL Server 2019 RTM-CU12 version 15.0.4153.1 in the Ubuntu 20.04

To get a list of the database, use the T-SQL below. It returns a list of system databases master, tempdb, model, and msdb available in each SQL Server instance.

Conclusion

This article is a practical demonstration of installing SQL Server on Linux on Ubuntu 20.04. You can now deploy your databases, objects such as tables, views, procedures on it. In an upcoming article, we will explore installing SQL Server on Linux in other environments such as RHEL and docker containers. Stay Tuned.