sql server on ubuntu

SQL Server on Ubuntu

They finally did it! Finally SQL Server for Linux was announced. I’ve been waiting for this for quite a while, so let’s jump in and get it installed. After that I’ll test some of the features that until today were only available in enterprise edition.

I’m installing Ubuntu 16 on a Hyper-V VM. I’ll be using a generation 1 Hyper-V VM as I’ve had much better luck installing Linux VMs on these. I’ve had boot and performance issues with Linux on Generation 2 VMs.

After installing Ubuntu I followed the instructions in the SQL Server Linux documentation and recorded my progress below.

For Ubuntu we need to register the SQL Server repository before we can use the apt-get package manager to download and install SQL Server. First drop into super user mode (similar to administrator in Windows) with the command below.

sudo su

The next two commands download the repository GPG keys and add the SQL Server repository to apt-get:

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

curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server.list > /etc/apt/sources.list.d/mssql-server.list

register the sql server repository for ubuntu

Then type exit to quit super user mode. Now we need to update apt-get to gain access to the latest packages.

sudo apt-get update

apt get update sql server

This will check all the registered repositories (including the newly added SQL Server ones) for the latest packages. Now we can download and install.

sudo apt-get install mssql-server

The first problem I hit was that my VM didn’t have enough RAM. After a quick restart and reconfigure of my VM I tried the same command again.

sql server requires at least 3.25gb

The install command displays all the packages SQL Server requires to run on Ubuntu and asks you if you want to continue with the installation. Type yes and you should get the message below.

sql server installed on ubuntu

To run this script type:

sudo /opt/mssql/bin/sqlservr-setup

You will be asked for a password for the sa user and if  you want the service to start on boot.

To use SSMS or Visual Studio Code from another machine we need to open a port on the local firewall. By default SQL Server will accept connections on port 1433. Nothing has changed here for the Linux version so run the code below to open the port.

sudo ufw allow 1433/tcp

ubuntu firewall for ssms

Connect with SSMS

Download the latest SQL Server vNext version of SSMS from here. This will take a while to install and I required a restart once it was done. Once it’s ready, use the IP address of your VM to connect. If you don’t know the IP then run the command below on your VM.

ifconfig

Now we have all we need to connect to our SQL Server on Linux.

sql server vnext ssms connect

Once logged in we can use SSMS as normal. I even ran a few extended event traces to make sure that was working. To kick the tires a little, I tried creating a table with a filtered updateable nonclustered columnstore index. First I created a table.

CREATE TABLE test 
(
  a INT IDENTITY(1,1),
  b INT,
  c NVARCHAR(MAX),
  CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED 
  (
    [a] ASC
  ) ON [PRIMARY]
) ON [PRIMARY];

Then the Index.

CREATE NONCLUSTERED COLUMNSTORE INDEX [NC_CSI_test_a_b]
ON [dbo].[test]([a], [b])
WHERE [b] IS NOT NULL;

Finally I inserted some data and queried the table.

columnstore index on ubuntu sql server

I think it’s amazing that the product has this level of functionality so early on. I’ll be poking around more over the coming weeks and recording what I find right here.

 

 

About the author

James Anderson

I'm a freelance SQL Server DBA in Southampton, UK. Performance tuning code and improving development processes are what I love to do for work. Outside of work, I live to travel to as many places as possible with my wife Sarah.

2 thoughts on “SQL Server on Ubuntu”

Leave a Reply

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

Sign Up

James Anderson SQL Server DBA

Hi, I'm James Anderson. When I'm not blogging about SQL Server, I do this.

The SQL People

DBA Stackexchange Profile