Running MS SQL Server 2017 on Ubuntu Server 16.04

Due to my need for a SQL server in my home lab, I decided to try Microsoft SQL Server 2017 on Ubuntu Server. As I mentioned in a previous blog post, I try and keep the Windows management and maintenance to a minimum in my home lab. I find managing linux systems much easier and requires fewer compute resources when running them in virtual machines. This post I’m going to walk through the installation of Microsoft SQL Server 2017 on Ubuntu Server 16.04.

Prerequisites

  • Ubuntu Server 16.04 configured with as such:
    • Memory: 2GB of RAM (4GB or more is recommended).
    • File system: XFS or EXT4 (other file systems, such as BTRFS, are unsupported).
    • Disk space: 6GB of free space.
    • CPU: 2 cores, 2 GHz, 64-bit
    • If you are installing on NFS, requirements are as follows:
      • NFS 4.2 or later.
      • Only locate the /var/opt/mssql directory on the NFS mount.
      • Ensure that NFS clients use the ‘nolock’ option when mounting the remote share.

Installation

  1. Import the public GPG keys.

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

  2. Add the SQL Server repository.

    sudo add-apt-repository "$(curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2017.list)"

  3. Install MSSQL server.

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

Configuration

  1. Configure SQL Server.

    sudo /opt/mssql/bin/mssql-conf setup
    Note: I chose the free development install, which doesn’t require a license.

  2. Verify that SQL server is running.

    systemctl status mssql-server

Install the SQL Server command-line tools

  1. Import the public repository GPG keys (if not already completed).

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

  2. Add the SQL server tools repository.

    sudo add-apt-repository "$(curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list)"

  3. Install the tools.

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

  4. Add the tools to the environment path for easy access.

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

  5. Connect locally with the sqlcmd.

    sqlcmd -S localhost -U SA -P ''

    Connection is successful if you are at the 1> prompt.

Create and query data

  1. Create a new database TestDB (via the sqlcmd 1> prompt)

    CREATE DATABASE TestDB
    SELECT Name from sys.Databases
    GO

  2. Insert data into a new table Inventory

    USE TestDB
    CREATE TABLE Inventory (id INT, name NVARCHAR(50), quantity INT)
    INSERT INTO Inventory VALUES (1, 'banana', 150); INSERT INTO Inventory VALUES (2, 'orange', 154);
    GO

  3. Select data from the Inventory table

    SELECT * FROM Inventory WHERE quantity > 152;
    GO

Other tools to connect to your SQL Server

For other tools that run on Windows but connect to SQL Server on Linux, see:

Posted in Home Lab, Linux and tagged , , , .

Leave a Reply

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