Monthly Archives: February 2020

Installing SQL on Linux Mint

Not everything that can be counted counts, and not everything that counts can be counted.

Albert Einstein

I am using Linux Mint as my main daily OS driver and loving it. Ease of use, powerful configuration and tons of small “shortcuts”, that is helping me stay productive. I have cravings for some of the tools in Windows, but I can easily find good enough replacement in my Linux journey going forward.

As .NET Core is growing and becoming better and better in terms of performance, I needed to port one of my applications from .NET 4.5 framework to .NET Core. I have used MS SQL as a database and the structure is quite complex / big. I could rewrite it or ….

Install SQL Express

After reading the documentation, installing SQL on Linux is straight forward procedure. Since I am on Linux Mint, following Ubuntu instructions will do the trick.

First we need to add the GPG repository keys and update the system (click here to follow along).

GPG keys

After we update the system, we need to call install mssql-server.

 sudo apt-get install -y mssql-server 

When the tools are installed, we need to run the setup to choose version of our SQL engine.

sudo /opt/mssql/bin/mssql-conf setup
Run setup
Select MS SQL Edition

I selected Express edition as I will use it for development purposes only.

After confirming the terms, you’ll need to set your admin password (sa account).

Setting admin page

With that under the hood, we need to check (after installation finishes), if the SQL is running.

systemctl status mssql-server --no-pager
Checks, if MS SQL is running

Tools to connect to MS SQL instance

To create a database, you need to connect with a tool that can run Transact-SQL statements on the SQL Server. You can install command line tools, if you are more into that, but I do prefer GUI to visualize the data.

Let me introduce Azure Data Studio, a cross-platform database tool with intellisense with SQL snippets and extensions support on Windows, MacOS, and Linux. It is useful Electron app, which is easy to use and works great with MS SQL instance on Linux.

For Linux Mint you have an option to download .deb file – which you then open with package installer (double click on deb file and press Install package).

Package installer

After that is finished, run the Azure Data Studio (I use ULauncher for that).

Launch via ULauncher

Connect and use your instance of MS SQL

To connect to your instance of SQL server, you can use this quick start. For the server name you should use localhost, username sa and password (what you entered at installation time).

First connection to MS SQL

If you connection is successful, then you will see system databases and server dashboard with additional information.

Server dashboard

Click New Query in the task window. To have a little bit more information about the databases, open servers on the left pane (first icon).

New query

Write below T-SQL query for creating test database TestDb.

CREATE DATABASE TestDb

Press Run to execute. When finished, right click on the databases and choose Refresh. You should see the TestDb created. Return to query window and delete create statement.

Azure Data Studio is packed with SQL snippets. If you start writing table in query window, you should see snippet sqlCreateTable. Select it and press enter to invoke it. Check the list of built-in snippets here.

Change the name to Categories and change the name of the fields to CategoryId and Name (as stated below – use tab to move between fields). Don’t forget to select appropriate database (TestDb) in the drop-down field in upper right corner.

Press Run, when finished. Let us add few records in the table to check, if everything works properly.

Use snippet sqlInsertRows. Insert few records and then use SELECT * FROM Categories to see the result. You should do as follows:

Conclusion

I wanted to use SQL in containers to solve the need for running MS SQL as an instance on my Linux machine. I tried it in the past with Ubuntu instance just as a test, if it works. It was in preview and the experience was horrible.

The MS SQL team did a fantastic job on making MS SQL a first class citizen on Linux and I have everything I need to continue my journey with MS SQL without having to change my structure, tools and data. All that without any magic workarounds and additional tailored scripts.

It just works 🙂