Sunday, November 20, 2016

Installing SQL Server on Linux, connecting and running queries from Linux

I decided to play around with installing SQL Server vNext on Linux today. I decided to pick the Ubuntu distribution. I created a VM in VirtualBox and then installed Ubuntu 16.04.   After that was done, it was time to install SQL Server.

All this stuff on this page runs on Linux, there are no windows components, if you have just a Linux desktop/server, you are all set

Here are the steps

Import the public repository GPG keys:

curl | sudo apt-key add -

Register the Microsoft SQL Server Ubuntu repository:

curl | sudo tee /etc/apt/sources.list.d/mssql-server.list

Run the following commands to install SQL Server:

sudo apt-get update
sudo apt-get install -y mssql-server

After the package installation finishes, run the configuration script and follow the prompts.

sudo /opt/mssql/bin/sqlservr-setup

Once the configuration is done, verify that the service is running:

systemctl status mssql-server

I myself like to run ps and then grep for mssql

ps -ef | grep  mssql

Ok so SQL Server is installed. In order to be able to run queries against SQL Server, we need to install the SQL Server tools

Open a new terminal window

Import the public repository GPG keys:

curl | sudo apt-key add -

Register the Microsoft Ubuntu repository:

curl | sudo tee /etc/apt/sources.list.d/msprod.list

Update the sources list and run the installation command:

sudo apt-get update 
sudo apt-get install mssql-tools

You will get a bunch of prompts, just follow it, say YES or y and you are set

Now let's run a command

Run this from a terminal window

sqlcmd -S localhost -U SA
Enter you password
Enter a simple query like SELECT @@version
Type GO, hit enter and voila, you should get results

Here is what it looks like

Here is also a video of the whole process, all of this was done in less than 5 minutes

Here is also a link to the official documentation

Install SQL Server:
Install the tools:

Visual Studio Code

Now unless you want to spend the rest of your life in a command window, I suggest you install VS Code

Visit this page

Now for me .deb didn't work, so I got the tarball (.tar.gz) and manually extracted it

Then I ran the Code application inside the VSCode-linux-x64 folder

The first thing we have to do is install the mssql extension

Click on View-->Extensions, type in mssql, click on the install button

Now open a new file and save it with a .sql extension

Click on View-->Command Palette

Type sqlman

Add the server localhost), db name, username (probably sa) and add the password, save this with a name so you will know what it is

Type some valid SQL, hit CTRL + SHIFT + E or right click and select execute query

You should see something like this

There you have it, SQL Server running on Ubuntu, VS Code running on Ubuntu returning data from the SQL Server instance

I did hit an issue with trying to run xp_readerrorlog, you can read about that here:
How to read the errorlog on SQL Server installed on Linux

If you want to install SQL Server on Docker on a Mac, take a look at Aaron Bertrand's post
VS Code on Mac meets SQL Server on Linux (in Docker)

To see all my SQL Server on Linux posts, click here: SQL Server on Linux

No comments: