How to install Ms SQL Server 2019 on Ubuntu 20.04

In this guide, we are going to install Ms SQL Server 2019 on Ubuntu 20.04. As a bonus we are going to see how to use sqlcmd to connect to the server, create a database and run queries.

Microsoft SQL Server is a relational database management system developed by Microsoft. It is aimed to be used to store and retrieve data by applications. It can be run either on the same computer or on another across a network.

Also check these:

Requirements

To follow along, please ensure that you have:

Up to date Ubuntu 20.04 Server with at least 2 GB of memory
Internet Access from the server
Root access to the server

Table of Content

Ensure server is up to date
Install SQL Server
Install the SQL Server command-line tools
Connecting to the server locally
Create and query data

Ensure the server is up to date

Let us make sure that our server has updated packages with this command

sudo apt update
sudo apt -y upgrade

Next, let us install common packages that willbe essential during the installation.

sudo apt install curl wget -y

Install SQL Server

To configure SQL Server on Ubuntu, follow these steps:

Import the public repository GPG keys:

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

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

Update the repositories and install SQL Server:

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

After the package installation finishes, run mssql-conf setup and follow the prompts to set the SA password and choose your edition.

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

Make sure to specify a strong password for the SA account (Minimum length 8 characters, including uppercase and lowercase letters, base 10 digits and/or non-alphanumeric symbols).

$ sudo /opt/mssql/bin/mssql-conf setup
usermod: no changes
Choose an edition of SQL Server:
  1) Evaluation (free, no production use rights, 180-day limit)
  2) Developer (free, no production use rights)
  3) Express (free)
  4) Web (PAID)
  5) Standard (PAID)
  6) Enterprise (PAID) - CPU Core utilization restricted to 20 physical/40 hyperthreaded
  7) Enterprise Core (PAID) - CPU Core utilization up to Operating System Maximum
  8) I bought a license through a retail sales channel and have a product key to enter.

Details about editions can be found at
https://go.microsoft.com/fwlink/?LinkId=2109348&clcid=0x409

Use of PAID editions of this software requires separate licensing through a
Microsoft Volume Licensing program.
By choosing a PAID edition, you are verifying that you have the appropriate
number of licenses in place to install and run this software.

Enter your edition(1-8): 1
The license terms for this product can be found in
/usr/share/doc/mssql-server or downloaded from:
https://go.microsoft.com/fwlink/?LinkId=2104078&clcid=0x409

The privacy statement can be viewed at:
https://go.microsoft.com/fwlink/?LinkId=853010&clcid=0x409

Do you accept the license terms? [Yes/No]:Yes

Enter the SQL Server system administrator password:
Confirm the SQL Server system administrator password:
Configuring SQL Server...

The licensing PID was successfully processed. The new edition is [Enterprise Evaluation Edition].
ForceFlush is enabled for this instance.
ForceFlush feature is enabled for log durability.
Created symlink /etc/systemd/system/multi-user.target.wants/mssql-server.service → /lib/systemd/system/mssql-server.service.
Setup has completed successfully. SQL Server is now starting.

Once the configuration is done, verify that the service is running:

$ sudo systemctl status mssql-server --no-pager
● mssql-server.service - Microsoft SQL Server Database Engine
     Loaded: loaded (/lib/systemd/system/mssql-server.service; enabled; vendor preset: enabled)
     Active: active (running) since Thu 2021-10-21 18:24:04 UTC; 51s ago
       Docs: https://docs.microsoft.com/en-us/sql/linux
   Main PID: 11459 (sqlservr)
      Tasks: 136
     Memory: 591.2M
     CGroup: /system.slice/mssql-server.service
             ├─11459 /opt/mssql/bin/sqlservr
             └─11490 /opt/mssql/bin/sqlservr

Oct 21 18:24:09 ip-10-2-40-239 sqlservr[11490]: [145B blob data]

If you plan to connect remotely, you might also need to open the SQL Server TCP port (default 1433) on your firewall.

At this point, SQL Server 2019 is running on your Ubuntu machine and is ready to use!

Install the SQL Server command-line tools

To connect to the server, you need to have the client tools that will allow you to do so. In this section we will install SQL Server command-line tools: sqlcmd and bcp.

Import the public repository GPG keys.BashCopy

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

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

Update the sources list and run the installation command with the unixODBC developer package.

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

Add /opt/mssql-tools/bin/ to your PATH environment variable in a bash shell.

To make sqlcmd/bcp accessible from the bash shell for login sessions, modify your PATH in the ~/.bash_profile file with the following command:BashCopy

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

To make sqlcmd/bcp accessible from the bash shell for interactive/non-login sessions, modify the PATH in the ~/.bashrc file with the following command:BashCopy

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

Connecting to the server locally

Run sqlcmd with parameters for your SQL Server name (-S), the user name (-U), and the password (-P). In this tutorial, you are connecting locally, so the server name is localhost. The user name is SA and the password is the one you provided for the SA account during setup.BashCopy

sqlcmd -S localhost -U SA -P '<YourPassword>'

You can omit the password on the command line to be prompted to enter it.

If you later decide to connect remotely, specify the machine name or IP address for the -S parameter, and make sure port 1433 is open on your firewall.

If successful, you should get to a sqlcmd command prompt: 1>. Let us confirm that everything works by checking the version

$ sqlcmd -S localhost -U SA -P 'gM3PL3wh2YNjBm7TDTcw'
1> SELECT @@VERSION
2> GO

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2019 (RTM-CU13) (KB5005679) - 15.0.4178.1 (X64)
	Sep 23 2021 16:47:49
	Copyright (C) 2019 Microsoft Corporation
	Enterprise Evaluation Edition (64-bit) on Linux (Ubuntu 20.04.3 LTS) <X64>

(1 rows affected)

Create and query data

In this section, let us use sqlcmd to create a new database, add data, and run a simple query.

Create a new database

Use this Transact-SQL command in sqlcmd command prompt to create a test database called CitizixDB:

1> CREATE DATABASE CitizixDB
2> GO

Use this to query the name of all of the databases on your server

1> SELECT Name from sys.Databases
2> GO
Name
--------------------------------------------------------------------------------------------------------------------------------
master
tempdb
model
msdb
CitizixDB

(5 rows affected)

Note: You must type GO on a new line to execute the previous commands

Insert data

Let’s create a new table, Inventory, and insert two new rows.

In the sqlcmd command prompt, switch context to the new <meta charset="utf-8">CitizixDB database:

1> USE CitizixDB
2> Go
Changed database context to 'CitizixDB'.

Create new table named Inventory:

1> CREATE TABLE Inventory (id INT, name NVARCHAR(50), quantity INT)
2> GO

Insert data into the new table:

1> INSERT INTO Inventory VALUES (1, 'banana', 150); INSERT INTO Inventory VALUES (2, 'orange', 154);
2> GO
(1 rows affected)
(1 rows affected)

Select data

Use this query to fetch data from the Inventory table where the quantity is greater than 152:

1> SELECT * FROM Inventory WHERE quantity > 152;
2> GO
id          name                                               quantity
----------- -------------------------------------------------- -----------
          2 orange                                                     154

(1 rows affected)

Exit the sqlcmd command prompt

To end your sqlcmd session, type QUIT:

QUIT

Conclusion

In this guide we managed to install Ms SQL Server 2019 in Ubuntu 20.04