Monthly Archives: March 2022

Installing SQL Server 2019 and tools on Ubuntu 20.04

Installing SQL Server 2019 and tools on Ubuntu 20.04

Why writing a blog article about the installation of Microsoft SQL Server 2019, related command-line tools, and UI-based management solution despite the official documentation by Microsoft?

There are two main reasons:

  • For own purpose and reference with some additional hints
  • The official documentation seems to be "out-of-sync"

Perhaps reading the official articles Quickstart: Install SQL Server and create a database on Ubuntu and Install the SQL Server command-line tools sqlcmd and bcp on Linux in the Microsoft documentation might be a pre-requisite to the following content.

And the article Installation guidance for SQL Server on Linux provides guidance for installing, updating, and uninstalling SQL Server 2019 on Linux.

However, as mentioned, some information feels out of picture or eventually outdated given existence of newer versions of the referenced applications.

Prepare your system

Before we shall start with the installation of SQL Server 2019, command-line tools and related management software let's be sure that our local system is up-to-date and has the needed packages already installed.

Open a terminal to run the following commands. First one is going to update your local package cache. The second one installs the curl package which we are going to need for future commands.

sudo apt-get update
sudo apt install curl libxss1 libgconf-2-4 libunwind8

Next, import the public repository GPG keys.

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

Don't ignore the trailing dash (-) in the command above. It is essential and therefore necessary. Alternatively this could have been done using wget like so.

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

Same handling for the trailing dash (-) applies.

Get the packages

Now that the local system has been prepared it is time to get the actual software packages for SQL Server 2019 and command-line tools.

To be able to install SQL Server 2019 use one of the following commands in order to add the package repository.

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

Then add the package repository for the command-line tools provided by Microsoft accordingly.

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

Alternatively the information of each package repository could be used directly. Here are the corresponding entries for the sources and some more.

# SQL Server 2019
deb [arch=amd64,armhf,arm64] https://packages.microsoft.com/ubuntu/20.04/mssql-server-2019 focal main
# SQL command-line tools as part of the "productivity collection"
deb [arch=amd64,armhf,arm64] https://packages.microsoft.com/ubuntu/20.04/prod focal main

# A few additional Microsoft repos for reference...
# Edge
deb [arch=amd64] http://packages.microsoft.com/repos/edge/ stable main
# Azure CLI
deb [arch=amd64] https://packages.microsoft.com/repos/azure-cli/ focal main
# Visual Studio Code
deb [arch=amd64,arm64,armhf] http://packages.microsoft.com/repos/code stable main

Maybe you prefer to store all Microsoft package sources in one single microsoft.list file rather than having multiple ones.

I highly recommend to read Configure repositories for installing and upgrading SQL Server on Linux for more details on available product options and upgrade channels of SQL Server.

With the newly added repositories for apt it is time again to update the local package cache and to fill it with details about the new packages available.

sudo apt update

After successful update of the package cache you are able to search it for SQL Server related packages, like so.

apt search mssql

Or if you prefer less details, like this.

apt list mssql*
Installing SQL Server 2019 and tools on Ubuntu 20.04

The output of the command above is going to give a list of available packages for SQL Server 2019. Your choice might be different but I'm going to install the actual database engine, with full-text search, the latest command-line tools, and additionally ODBC driver support for Unix/Linux systems.

sudo apt install -y mssql-server mssql-server-fts mssql-tools18 unixodbc-dev

All necessary dependencies will be resolved and installed by apt.

After the installation SQL Server 2019 is not yet running on your system. Run mssql-conf setup command and follow the prompts to choose your edition and set the password of the super-administrator (sa) account.

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

After completing the setup, verify that the service is running as expected.

systemctl status mssql-server --no-pager
Installing SQL Server 2019 and tools on Ubuntu 20.04

Finally, add the SQL Server tools to the path by default.

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

Is it running?

Good question. Let's try and find out. The command-line tools of SQL Server 2019 comes with sqlcmd. The SQL Server command line tool which allows you to connect to local and remote instances of SQL Server including Azure SQL. Run the following to query the product version and edition of your installation.

sqlcmd -S localhost -C -U SA -Q "SELECT @@VERSION"

You will be prompted to enter the password of SA. If you are not familiar with the various command line switches, run sqlcmd -? to get a quick overview.

The resulting output should look similar to this.

Installing SQL Server 2019 and tools on Ubuntu 20.04

Congratulations!

All those steps could be merged into one script to simplify and eventually automate the installation of SQL Server 2019 and its command-line tools. Interestingly the Microsoft documentation has an article on that matter.

Unattended install for SQL Server on Ubuntu - SQL Server
Learn to use a sample Bash script to install SQL Server 2017 on Ubuntu 16.04 without interactive input.
Installing SQL Server 2019 and tools on Ubuntu 20.04

However the presented bash script needs some serious TLC and updates. I suggest that you have look and take it as a starting point only, if needed.

Missing SQL Server Management Studio?

While working with SQL Server on a Windows system the common choice would be SQL Server Management Studio (SSMS) in order to connect to the database server / instance and the database itself. Unfortunately, SQL Server Management Studio is not available on Linux. And probably won't be in the near (and far) future.

There are at least two possibilities to replace SQL Server Management Studio which offer sufficient and similar comfort on Linux.

  • Visual Studio Code with SQL Server (mssql) extension
  • Azure Data Studio
Installing SQL Server 2019 and tools on Ubuntu 20.04
Azure Data Studio and Visual Studio Code shown in the application menu

Both applications have their pros and cons. If you are already developing and writing code in Visual Studio Code you might probably prefer to use the extension and to stay within the same window. In case that you are more into SQL Server handling and Azure you might opt-in for Data Studio.

Download and install Azure Data Studio following this article.

Download and install Azure Data Studio - Azure Data Studio
Download and install Azure Data Studio for Windows, macOS, or Linux. This article provides release dates, version numbers, system requirements, and download links.
Installing SQL Server 2019 and tools on Ubuntu 20.04

Then launch the application via Application menu or run azuredatastudio in a terminal.

Installing SQL Server 2019 and tools on Ubuntu 20.04
Add a connection to a local instance of SQL Server in Azure Data Studio
Installing SQL Server 2019 and tools on Ubuntu 20.04
Azure Data Studio connected to local instance of SQL Server 2019

Install Visual Studio Code using apt.

sudo apt install -y code

Then launch the application via menu or run code in a terminal.

Installing SQL Server 2019 and tools on Ubuntu 20.04
SQL Server (mssql) extension in Visual Studio Code

More details on SQL Server tools can be found here.

SQL tools overview - SQL Server
SQL query and management tools for SQL Server, Azure SQL (Azure SQL database, Azure SQL managed instance, SQL virtual machines), and Azure Synapse Analytics.
Installing SQL Server 2019 and tools on Ubuntu 20.04
Image credit: Leif Christoph Gottwald

openSUSE Leap 15.4 Beta testing

I tested the Leap 15.4 Beta GNOME Desktop today on a Lenovo ThinkPad X250. The installation went through without any hiccup and I documented every step of the installation in a Twitter thread.

Lenovo ThinkPad X250 and a bootable USB drive w/ openSUSE Leap 15.4 Beta
Lenovo ThinkPad X250 and a bootable USB drive w/ openSUSE Leap 15.4 Beta

One thing that I noticed was new in the installer is the icon to change the color theme during installation. This was not present when I tested the Leap 15.4 Alpha distribution.

On the top-right corner of the installer there is a moon icon which can be clicked to select a different theme for the installer. Five themes are available which contain three dark and two light variants.

The default theme is set to the installation.qss style sheet.

The cyan-black , highcontrast and white-black are dark themes while installation and style are light themes.

highcontrast.qss
highcontrast.qss
white-black.qss
white-black.qss

I took the above screenshots using the Leap 15.4 Beta image on VirtualBox to get clear images. In the Twitter thread I took pictures of the installation process and the theme selection on a laptop.

In either installation I did not encounter any bug while setting up GNOME Desktop with defaults.