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

sudo apt-get update

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.

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.

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

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.

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], )
WHERE  IS NOT NULL;

Finally I inserted some data and queried the table.

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.

The post SQL Server on Ubuntu appeared first on The Database Avenger.