How to install MS SQL Server 2019

How To Install Microsoft SQL Server 2019 On Ubuntu 20.04 LTS

On this article we will discuss how to install Microsoft SQL Server 2019 and SQL Server command-line tools on Ubuntu 20.04 LTS operating system.

Introduction

Microsoft SQL Server is a relational database management system developed by Microsoft Corp. From SQL Server 2016 onward, the product is supported on x64 processors only. The current version is Microsoft SQL Server 2019, released November 4, 2019. Microsoft makes SQL Server available in multiple editions, with different feature sets and targeting different users. These editions are: Enterprise, Standard, Web, Business Intelligence, Workgroup and Express. Developer. On this article we will learn how to install Microsoft SQL Server 2019 Developer Edition on Ubuntu 20.04 LTS operating system. The SQL Server Developer Edition includes the same features as SQL Server Enterprise Edition, but is limited by the license to be only used as a development and test system, and not as production server. Starting early 2016, Microsoft made this edition free of charge to the public.



A. Microsoft SQL Server 2019 Installation on Ubuntu 20.04 LTS

On this article we will select Mircosoft SQL Server 2019 Developer edition which is free and no production use rights.
The MS SQL Server 2019 Installation on Ubuntu 20.04 LTS will be consist of several steps :

  1. Import the public repository GPG keys
  2. Register the Microsoft SQL Server Ubuntu repository for SQL Server 2019
  3. Install SQL Server 2019
  4. Selecting MS SQL Server 2019 edition
  5. Verify the Installation

Prerequisite

Before starting the installation, we have to prepare some things that support the installation, which will be mentioned below:

  • An Ubuntu 20.04 machine with at least 2 GB of memory
  • A non root user with sudo privilege

The following steps will explain in detail the MS SQL Server 2019 installation process.

1. Import the public repository GPG keys

This step can be done by running the command: wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -

2. Adding the Microsoft SQL Server Ubuntu repository for SQL Server 2019

This step can be done by running the command: sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/20.04/mssql-server-2019.list)"

ramans@otodiginet:~$ wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
 ramans@otodiginet:~$ sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/20.04/mssql-server-2019.list)"
 Get:1 https://packages.microsoft.com/ubuntu/20.04/mssql-server-2019 focal InRelease [10.4 kB]
 Get:2 https://packages.microsoft.com/ubuntu/20.04/mssql-server-2019 focal/main arm64 Packages [1,064 B]     
 Get:4 https://packages.microsoft.com/ubuntu/20.04/mssql-server-2019 focal/main amd64 Packages [5,833 B]     
 Get:5 https://packages.microsoft.com/ubuntu/20.04/mssql-server-2019 focal/main armhf Packages [1,064 B]     
 Hit:3 https://downloads.apache.org/cassandra/debian 40x InRelease                                           
 Hit:7 http://mirror.telkomuniversity.ac.id/ubuntu focal InRelease                                           
 Hit:8 http://mirror.telkomuniversity.ac.id/ubuntu focal-updates InRelease                                   
 Hit:6 https://downloads.apache.org/cassandra/debian 311x InRelease                                          
 Hit:9 https://deb.nodesource.com/node_14.x focal InRelease                                                  
 Hit:10 http://mirror.telkomuniversity.ac.id/ubuntu focal-backports InRelease                                
 Hit:11 https://dl.yarnpkg.com/debian stable InRelease                                                       
 Hit:12 http://mirror.telkomuniversity.ac.id/ubuntu focal-security InRelease                                 
 Hit:13 http://ppa.launchpad.net/deadsnakes/ppa/ubuntu focal InRelease                        
 Hit:14 http://ppa.launchpad.net/teejee2008/ppa/ubuntu focal InRelease  
 Hit:16 http://ppa.launchpad.net/ubuntu-mozilla-daily/ppa/ubuntu focal InRelease
 Hit:15 https://packagecloud.io/sensu/stable/ubuntu focal InRelease
 Fetched 18.4 kB in 5s (4,060 B/s)
 Reading package lists… Done


Import the public repository GPG keys and Microsoft SQL Server Ubuntu repository

3. Install SQL Server 2019

The installation process is started by updating the Ubuntu system, using the command line : sudo apt-get update
Followed by doing the installation with the command line: sudo apt-get update and sudo install -y mssql-server.

ramans@otodiginet:~$ sudo apt-get update
 Hit:1 http://mirror.telkomuniversity.ac.id/ubuntu focal InRelease
 Hit:2 http://mirror.telkomuniversity.ac.id/ubuntu focal-updates InRelease                                   
 Hit:3 http://mirror.telkomuniversity.ac.id/ubuntu focal-backports InRelease                                 
 Hit:4 http://ppa.launchpad.net/deadsnakes/ppa/ubuntu focal InRelease                                        
 Hit:7 http://ppa.launchpad.net/teejee2008/ppa/ubuntu focal InRelease                                        
 Hit:8 http://ppa.launchpad.net/ubuntu-mozilla-daily/ppa/ubuntu focal InRelease                              
 Hit:5 https://downloads.apache.org/cassandra/debian 40x InRelease                                           
 Hit:6 https://downloads.apache.org/cassandra/debian 311x InRelease                                          
 Hit:10 http://mirror.telkomuniversity.ac.id/ubuntu focal-security InRelease                                 
 Hit:11 https://deb.nodesource.com/node_14.x focal InRelease                                                 
 Hit:12 https://dl.yarnpkg.com/debian stable InRelease                                                       
 Hit:13 https://packages.microsoft.com/ubuntu/20.04/mssql-server-2019 focal InRelease               
 Hit:9 https://packagecloud.io/sensu/stable/ubuntu focal InRelease
 Reading package lists… Done
Update Ubuntu system base
Update Ubuntu system base
ramans@otodiginet:~$ sudo apt-get install -y mssql-server
 Reading package lists… Done
 Building dependency tree       
 Reading state information… Done
 The following additional packages will be installed:
   libc++1 libc++1-10 libc++abi1-10 libsasl2-modules-gssapi-mit libsss-nss-idmap0
 Suggested packages:
   clang
 The following NEW packages will be installed:
   libc++1 libc++1-10 libc++abi1-10 libsasl2-modules-gssapi-mit libsss-nss-idmap0 mssql-server
 0 upgraded, 6 newly installed, 0 to remove and 149 not upgraded.
 Need to get 247 MB/247 MB of archives.



4. Select MS SQL Server 2019 edition

After the MS SQL Server 2019 package installation process is complete, the next step is to select the edition to use. By submitting command line : sudo /opt/mssql/bin/mssql-conf setup. On this article we will select the Developer edition for our tutorial, for this just enter the number 2.

ramans@otodiginet:~$ 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): 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=2104294&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
Select MS SQL Server edition
Selecting MS SQL Server 2019 edition

Unti this step, the installation of MS SQL Server 2019 has just completed done. Then we will continue to the next steps below.

5. Verify the Installation

We will check whether the MS SQL Server service has been running normally or not. Just hit the command line : systemctl status mssql-server --no-pager.

ramans@otodiginet:~$ 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 Fri 2021-06-18 00:31:25 PDT; 1min 7s ago
        Docs: https://docs.microsoft.com/en-us/sql/linux
    Main PID: 11595 (sqlservr)
       Tasks: 156
      Memory: 651.5M
      CGroup: /system.slice/mssql-server.service
              ├─11595 /opt/mssql/bin/sqlservr
              └─11622 /opt/mssql/bin/sqlservr
 Jun 18 00:31:30 otodiginet sqlservr[11622]: [145B blob data]
 Jun 18 00:31:30 otodiginet sqlservr[11622]: [155B blob data]
 Jun 18 00:31:30 otodiginet sqlservr[11622]: [61B blob data]
 Jun 18 00:31:31 otodiginet sqlservr[11622]: [96B blob data]
 Jun 18 00:31:31 otodiginet sqlservr[11622]: [66B blob data]
 Jun 18 00:31:31 otodiginet sqlservr[11622]: [75B blob data]
 Jun 18 00:31:31 otodiginet sqlservr[11622]: [96B blob data]
 Jun 18 00:31:31 otodiginet sqlservr[11622]: [100B blob data]
 Jun 18 00:31:31 otodiginet sqlservr[11622]: [71B blob data]
 Jun 18 00:31:31 otodiginet sqlservr[11622]: [124B blob data]
MS SQL Server 2019 Services
MS SQL Server 2019 Services

The service is already running properly, and now the SQL Server 2019 is running on our Ubuntu machine and is ready to use.

B. Install Microsoft SQL Server command-line tools on Ubuntu 20.04 LTS

To be able to use SQL Server 2019, we need a tool that can run Transact-SQL statements on the SQL Server. So, we can create remove or manipulate the database. On this step we will install SQL Server command line tools : sqlcmd and bcp. Here are the steps to install MS SQL Server tools.

  1. Import the public repository GPG keys
  2. Register the Microsoft Ubuntu repository
  3. Update the sources list and run the installation.
  4. Add /opt/mssql-tools/bin/ to PATH environment variable in a bash shell.

1. Import SQL Server command line tools public repository GPG keys

This step is done by running the command line: sudo curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -

ramans@otodiginet:~$ sudo curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
   % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                  Dload  Upload   Total   Spent    Left  Speed
 100   983  100   983    0     0   2421      0 --:--:-- --:--:-- --:--:--  2421
 OK

2. Register the Microsoft Ubuntu repository

This step is done by submitting the command line : sudo curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list.

ramans@otodiginet:~$ sudo curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list
   % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                  Dload  Upload   Total   Spent    Left  Speed
 100    89  100    89    0     0    229      0 --:--:-- --:--:-- --:--:--   229
 deb [arch=amd64,armhf,arm64] https://packages.microsoft.com/ubuntu/20.04/prod focal main
Add MS SQL Server tools repository
Add MS SQL Server tools repository

3. Run the installation

After updating the system resources list, then we do the installation by submitting the command line : sudo apt-get install mssql-tools unixodbc-dev.

amans@otodiginet:~$ sudo apt-get install mssql-tools unixodbc-dev
 Reading package lists… Done
 Building dependency tree       
 Reading state information… Done
 The following additional packages will be installed:
   autoconf automake autotools-dev libltdl-dev libodbc1 libtool m4 msodbcsql17 odbcinst odbcinst1debian2
   unixodbc
 Suggested packages:
   autoconf-archive gnu-standards autoconf-doc gettext libtool-doc unixodbc-bin gfortran
   | fortran95-compiler gcj-jdk m4-doc
 The following NEW packages will be installed:
   autoconf automake autotools-dev libltdl-dev libodbc1 libtool m4 msodbcsql17 mssql-tools odbcinst
   odbcinst1debian2 unixodbc unixodbc-dev
 0 upgraded, 13 newly installed, 0 to remove and 149 not upgraded.
 Need to get 3,075 kB of archives.
 After this operation, 8,976 kB of additional disk space will be used.
 Do you want to continue? [Y/n] Y
Install MS SQL Sever tools
Install MS SQL Sever tools

While the installation we will be promfted by a confirmation dialog box, as shown below :

MSODBC package license acceptance dialogue box
MSODBC package license acceptance dialogue box

To achieve the last version of SQL Server 2019 command tools, we do the re-installation by submitting command lines : sudo apt-get update and sudo apt-get install mssql-tools, as shown below :

ramans@otodiginet:~$ sudo apt-get update
 Hit:1 http://mirror.telkomuniversity.ac.id/ubuntu focal InRelease
 Hit:2 http://mirror.telkomuniversity.ac.id/ubuntu focal-updates InRelease                                   
 Hit:3 http://mirror.telkomuniversity.ac.id/ubuntu focal-backports InRelease                                 
 Hit:4 https://deb.nodesource.com/node_14.x focal InRelease                                                  
 Hit:5 https://dl.yarnpkg.com/debian stable InRelease                                                        
 Hit:6 http://mirror.telkomuniversity.ac.id/ubuntu focal-security InRelease                                  
 Hit:7 https://packages.microsoft.com/ubuntu/20.04/mssql-server-2019 focal InRelease                         
 Hit:8 https://packages.microsoft.com/ubuntu/20.04/prod focal InRelease                                      
 Hit:9 http://ppa.launchpad.net/deadsnakes/ppa/ubuntu focal InRelease                                        
 Hit:11 http://ppa.launchpad.net/teejee2008/ppa/ubuntu focal InRelease                                       
 Hit:13 http://ppa.launchpad.net/ubuntu-mozilla-daily/ppa/ubuntu focal InRelease                             
 Hit:10 https://downloads.apache.org/cassandra/debian 40x InRelease                                 
 Hit:14 https://downloads.apache.org/cassandra/debian 311x InRelease                  
 Hit:12 https://packagecloud.io/sensu/stable/ubuntu focal InRelease
 Reading package lists… Done
ramans@otodiginet:~$ sudo apt-get install mssql-tools
 Reading package lists… Done
 Building dependency tree       
 Reading state information… Done
 mssql-tools is already the newest version (17.7.1.1-1).

At this point, we have the newst mssql-tools version. To enable sqlcmd/bcp tools are accessible from the bash shell, just do the following command line : echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile.

ramans@otodiginet:~$ echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
 ramans@otodiginet:~$ echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
 ramans@otodiginet:~$ source ~/.bashrc
MSSQL tools bash profile
MSSQL tools bash profile

C. Connect To Microsoft SQL Server 2019

At this point, we have reached the latest step where we could try to explore the MS SQL Server 2019 database. On this scenario we will connect and create a new database called as otodigi_db.

1. Connect to Microsoft SQL Server locally

ramans@otodiginet:~$ sqlcmd -S 127.0.0.1 -U SA
 Password: 
 1> select name from sys.databases;
 2> go
 name                                                                                                                            
 master                                                                                                                          
 tempdb                                                                                                                          
 model                                                                                                                           
 msdb                                                                                                                            
 (4 rows affected)
Connect to MS SQL Server 2019 database server
Connect to MS SQL Server 2019 database server

2. Create a New Database

1> create database otodigi_db01;
 2> go
 1> select name from sys.databases;
 2> go
 name                                                                                                                            
 master                                                                                                                          
 tempdb                                                                                                                          
 model                                                                                                                           
 msdb                                                                                                                            
 otodigi_db01                           
Create a New database on MS SQL Server 2019
Create a New database on MS SQL Server 2019

Conclusion

Finally we have finished the installation of Microsoft SQL Server 2019, install the MS SQL server tools and try to establish a connection, perform a query and create a new database. Hopefully this article is useful for all of us. For more details about MS SQL Server 2019 installation can be found on its official web site.

Have a nice day and stay safe.

Share this article via :

Leave a Reply

Your email address will not be published. Required fields are marked *