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.
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
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
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:
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.
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], [b]) WHERE [b] 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.