How to install Microsoft SQL Server on Linux

Linux and MSSQL Server

During the past few years, Microsoft has been investing resources in Linux-related projects. Today you can install Microsoft SQL Server on Linux. All of it began with .NET being open-sourced. Then it was the turn of PowerShell. Microsoft even released a subsystem to run Ubuntu on Windows 10. We could discuss for hours whether these actions are pure open-source spirit or just a big commercial plan. Nevertheless, under Satya Nadella’s leadership Microsoft is changing, and even its attitude toward Linux is changing.

Microsoft SQL Server on Linux

With the umpteenth shocking announcement, Microsoft made SQL Server available for Linux. Now, remember it is still a preview and only runs on Ubuntu and RHEL at the moment. The only requirement exposed by Microsoft is memory: at least 3.25GB no more than 256GB (untested). The current supported versions (and the ones used in this guide) are:

Installing Microsoft SQL Server on Linux

  1. The first step is to add the repositories, it can be done by performing the following commands. I modified them from the original Microsoft documentation so that you can read the actual contents of each repository.
UbuntuRed Hat

You will need root access to perform these commands.

  1. First let’s trust the packages from Microsoft:
# curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
  1. Next, let’s retrieve the main repository containing MSSQL:
# curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server.list | tee /etc/apt/sources.list.d/mssql-server.list
  1. If you want to use a command line interface on the same machine you also need the following repository:
# curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | tee /etc/apt/sources.list.d/mssql-prod.list

You will need root access to perform these commands.

  1. This command retrieves the YUM repository configuration file:
# curl https://packages.microsoft.com/config/rhel/7/mssql-server.repo | tee /etc/yum.repos.d/mssql-server.repo
  1. If you want to use a command line interface on the same machine you also need the following repository:
# curl https://packages.microsoft.com/config/rhel/7/prod.repo | tee /etc/yum.repos.d/mssql-prod.repo
  1. The second step is to actually install the software:
UbuntuRed Hat
  1. Refresh the repositories and install the package:
# apt update
# apt install mssql-server
  1. If you also need the command line:
# apt install mssql-tools
  1. Install the package:
# yum install mssql-server
  1. If you also need the command line:
# yum install mssql-tools
  1. The third step involves the execution of a script that will prompt you to accept the license and set an administrator (SA) password for SQL Server. You will also be asked whether to start and/or enable the service at boot-time.
UbuntuRed Hat
  1. This step is the same for both distributions:
# /opt/mssql/bin/sqlservr-setup
  1. This step is the same for both distributions:
# /opt/mssql/bin/sqlservr-setup
  1. The last step involves adding the rule to the firewall:
UbuntuRed HatIPTABLES

If you are using UFW:

# ufw allow 1433/tcp

If you’re using FirewallD:

# firewall-cmd --add-port=1433/tcp --permanent
# firewall-cmd --reload

If you’re using Iptables:

# iptables -A INPUT -p tcp --dport 1433 -j ACCEPT
# iptables-save > /etc/sysconfig/iptables

Managing Microsoft SQL Server

The newly installed DBMS is now available on the machine and it is controlled by systemD:

CLI

You can start it by:

# systemctl start mssql-server

You can stop it by:

# systemctl start mssql-server

To see its status use:

# systemctl status mssql-server

You can enable its start at boot:

# systemctl enable mssql-server

And you can disable its start at boot:

# systemctl disable mssql-server

Test the connection

Now you should have a functioning installation of Microsoft SQL Server, it is time to access to it. You have two options:

  • Use the command line from Linux.
  • Use SSMS (SQL Server Management Studio) from a Windows machine.

Linux command line:

$ sqlcmd -S localhost -U SA
Password:
1> CREATE DATABASE Test
2> GO
1> EXIT

SQL Server Management Studio

SSMS Connection

SSMS Connection

To connect to the server using SQL Server Management Studio, just type the address of the server and select SQL Server Authentication. After that enter username and password (you can use SA and the password you set up using sqlserv-setup).

Here you can see two screenshots of SSMS (version 16.5 and 17.0 RC1) connecting to Ubuntu and CentOS machines. During the first boot Ubuntu was showing correctly, while CentOS was not. Now both are displayed as Windows NT. I initially tested both using SSMS 16.5, but version 17.0 is required for the correct operating system to show up. (Thanks to David Shiflet and Travis Wright.)

Conclusion

You now know how to install and perform basic operations with Microsoft SQL Server for Linux. Please keep in mind that this is still a preview and it might not work as you expect (e.g. CentOS shows Windows NT).

Image courtesy of mark | marksei
mark

You may also like...

5 Responses

  1. David Shiflet says:

    which version of SSMS are you using where you see that server information with Windows NT?

  2. mark says:

    Thank you for your insight, I updated the original article.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: