(Last Updated On: July 20, 2018)

MS SQL is a relational database system by Microsoft that was open-sourced in 2016. In this guide, I’ll take you through the steps to install MS SQL server on Ubuntu 18.04 Bionic Beaver Linux. In addition, We will look at how to create a database, add users, and lastly how to do daily backups for the databases.

Below are the minimum system requirements to run MS SQL on Ubuntu 18.04 server:

  • Minimum memory of 1 GB
  • CPU processor with a minimum speed of 1.4 GHz. But the recommended is >= 2 GHz
  • SQL Server requires a minimum of 6 GB of available hard-disk space

Install MS SQL Server on Ubuntu 18.04

Follow the steps below to install and configure MS SQL database server on Ubuntu 18.04:

Step 1: Update system

As usual, we ensure all system packages are updated before adding any new packages:

sudo apt-get update
sudo apt-get upgrade

If you have kernel updates done, ensure a reboot is done for the system to boot into a new kernel.

sudo reboot

Step 2: Import the public repository GPG keys:

Add GPG key for the system to trust MS SQL apt repository packages:

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

Step 3: Add Microsoft SQL Server Ubuntu repository:

Add SQL server apt repository to your Ubuntu 18.04 system.

echo "deb [arch=amd64] https://packages.microsoft.com/ubuntu/16.04/prod xenial main" | sudo tee /etc/apt/sources.list.d/mssql.list

Step 4: Update package list and install mssql-server package:

Now run the following commands to install SQL Server:

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

The output is similar to below:

Reading package lists... 0%
Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following packages were automatically installed and are no longer required:
  grub-pc-bin libpython-all-dev python-all python-all-dev python-asn1crypto python-cffi-backend python-crypto python-cryptography python-dbus
  python-enum34 python-gi python-idna python-ipaddress python-keyring python-keyrings.alt python-pkg-resources python-secretstorage
  python-setuptools python-six python-wheel python-xdg
Use 'apt autoremove' to remove them.
The following NEW packages will be installed:
  mssql-server
0 upgraded, 1 newly installed, 0 to remove and 15 not upgraded.
Need to get 176 MB of archives.
After this operation, 928 MB of additional disk space will be used.
Get:1 https://packages.microsoft.com/ubuntu/16.04/mssql-server-2017 xenial/main amd64 mssql-server amd64 14.0.3030.27-1 [176 MB]
Fetched 176 MB in 5s (36.3 MB/s)       
Preconfiguring packages ...
Selecting previously unselected package mssql-server.
(Reading database ... 117280 files and directories currently installed.)
Preparing to unpack .../mssql-server_14.0.3030.27-1_amd64.deb ...
Unpacking mssql-server (14.0.3030.27-1) ...
Processing triggers for libc-bin (2.27-3ubuntu1) ...
Setting up mssql-server (14.0.3030.27-1) ...

+--------------------------------------------------------------+
Please run 'sudo /opt/mssql/bin/mssql-conf setup'
to complete the setup of Microsoft SQL Server
+--------------------------------------------------------------+

SQL Server needs to be restarted in order to apply this setting. Please run
'systemctl restart mssql-server.service'.
Processing triggers for man-db (2.8.3-2) ...
Processing triggers for libc-bin (2.27-3ubuntu1) ...

If you encounter error “mssql-server : Depends: openssl (<= 1.1.0) but 1.1.0g-2ubuntu4.1 is to be installed“. Downgrade the version of openssl installed on your system and re-install the mssql server.

$ wget http://archive.ubuntu.com/ubuntu/pool/main/o/openssl/openssl_1.0.2g-1ubuntu4_amd64.deb
$ sudo dpkg -i openssl_1.0.2g-1ubuntu4_amd64.deb
$ apt-get -y install mssql-server

When the installation is done, proceed to set root user password by running initial setup  /opt/mssql/bin/mssql-conf setup

$ sudo /opt/mssql/bin/mssql-conf setup
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)
  7) Enterprise Core (PAID)
  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=852748&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): 2
The license terms for this product can be found in
/usr/share/doc/mssql-server or downloaded from:
https://go.microsoft.com/fwlink/?LinkId=855862&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]:y

Enter the SQL Server system administrator password: 
Confirm the SQL Server system administrator password: 
The passwords do not match. Please try again.
Enter the SQL Server system administrator password: 
Confirm the SQL Server system administrator password: 
Configuring SQL Server...

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.

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:

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 PATHin the ~/.bashrc file with the following command:

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

Step 5: Install MS SQL tools and unixODBC plugin

Run the commands:

sudo apt-get update 
sudo apt-get install mssql-tools unixodbc-dev
# sqlcmd -S localhost -U SA 
Password: 
1> create database testDB;

Get a list database:

1> select name from sys.databases;
2> go
name                                                                                                                            
--------------------------------------------------------------------------------------------------------------------------------
master                                                                                                                          
tempdb                                                                                                                          
model                                                                                                                           
msdb                                                                                                                            
testDB                                                                                                                          

(5 rows affected)