Microsoft SQL Server on Linux – installation (Ubuntu)

Daniel Holt

January 16, 2018

Tags: Microsoft Sql Server, Technical Track

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.

[code language="bash"] wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add - [/code]

Now we need to register the Microsoft SQL Server Repository

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

Update Local Repository information

[code language="bash"] sudo apt-get update [/code]

Next, the below command will install SQL Server

[code language="bash"] sudo apt-get install -y mssql-server [/code]

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

[code language="bash"] sudo /opt/mssql/bin/mssql-conf setup [/code]

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

Select Language

Enter a Secure SA Password

Installation is complete

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

[code language="bash"] systemctl status mssql-server [/code]

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

[code language="bash"] sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/16.04/prod.list)" [/code]

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

[code language="bash"] sudo apt-get update [/code]

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

[code language="bash"] sudo apt-get install -y mssql-tools unixodbc-dev [/code]

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

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

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

[code language="bash"] sqlcmd -S localhost -U SA [/code]

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)

[code language="bash"] sqlcmd -S IP Address or Hostname -U SA [/code]

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

[code language="SQL"] CREATE DATABASE danTestDB SELECT * FROM sys.Databases Go [/code]

[code language="SQL"] USE danTestDB Create Table Motorcycles (ID INT, Name NVARCHAR(50)) Insert INTO Motorcycles VALUES (1,'Harley Davidson') Insert INTO Motorcycles VALUES (2,'Honda') [/code]

[code language="SQL"] USE danTestDB SELECT * FROM Motorcycles SELECT * FROM Motorcycles where ID > 1 [/code]

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.

Insight and analysis of technology and business strategy