Microsoft SQL Server on Linux – installation (Ubuntu)

With the release of Microsoft SQL Server 2017 (SQL Server 2017) comes the ability to run SQL Server on the Linux Operating Systems. With Linux being the platform of choice for millions of application developers this is a smart move by Microsoft as they have publicly committed to making SQL Server on Linux as good as the windows offering with many of the same features delivered in a familiar format enabling application developers the choice of using this established RDBMS.

This article will give detailed instruction on how to install SQL on Linux for the most popular distribution, Ubuntu

Ubuntu Installation

Prerequisites

Ubuntu 16.04 or later with 2GB RAM  is required to run SQL Server on Ubuntu

SQL Server Installation Steps

• The first step is to import the public repository keys.

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

• Now we need to register the Microsoft SQL Server Repository

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

•  Update Local Repository information

sudo apt-get update

• Next, the below command will install SQL Server

sudo apt-get install -y mssql-server

• We now need to complete the install with some basic configuration. In order to do that run the below command and follow the prompts

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

1. Select the edition of SQL Server you would like to install and agree to terms

2.  Select Language

3. Enter a Secure SA Password

4. Installation is complete

5. Just to confirm we can check to make sure the SQL Server service is running

 systemctl status mssql-server 

Command Line Tools Installation

Now that we have a successful installation of SQL server we need to install tools to enable us to connect to the instance. The following steps will install command line tools and show you how to create a test database.

• First, we need to register another repository to be able to access the installation files

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

• As we have added a new Repository we now need to update our local repository information

 sudo apt-get update 

• Now we can install the command line tools with the below command. You will need to agree to the terms for the 2 packages.

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

• That completes the installation of the Command Line Tools. For Convenience i like to complete one more step to add /opt/mssql-tools/bin/ to my $PATH Variable. This allows me to call the command line tools without specifying the full path

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

Connecting to SQL Server and Creating a Database

• Run the below command to connect to SQL Server. you will be prompted to enter your password

 sqlcmd -S localhost -U SA 

• If you are connecting remotely you can pass the server name or IP address to the -S parameter. (You will also need to ensure that port 1433 is open in the firewall)

 sqlcmd -S IP Address or Hostname -U SA 

• Now that we have a connection to SQL Server we can run regular TSQL Commands. Below are a few for you to try out

CREATE DATABASE danTestDB
SELECT * FROM sys.Databases
Go

USE danTestDB
Create Table Motorcycles (ID INT, Name NVARCHAR(50))
Insert INTO Motorcycles VALUES (1,'Harley Davidson')
Insert INTO Motorcycles VALUES (2,'Honda')

USE danTestDB
SELECT * FROM Motorcycles
SELECT * FROM Motorcycles where ID > 1

Summary

I hope this helps you all get started with SQL Server On Linux. Please feel free to leave comments or questions below.

My next post will go in to more detail regarding the configuration options in Linux. Stay Tuned.

Learn more about Pythian Services for Microsoft SQL Server including professional consulting, implementation and ongoing support.