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:

Ubuntu 16.04 Xenial Xerus

RHEL 7.2 Maipo (CentOS 7.2 is okay too)

Installing Microsoft SQL Server on Linux

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.

Ubuntu Red Hat You will need root access to perform these commands. First let’s trust the packages from Microsoft: # curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - 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 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. 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 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

The second step is to actually install the software:

Ubuntu Red Hat Refresh the repositories and install the package: # apt update # apt install mssql-server If you also need the command line: # apt install mssql-tools Install the package: # yum install mssql-server If you also need the command line: # yum install mssql-tools

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.

Ubuntu Red Hat This step is the same for both distributions: # /opt/mssql/bin/sqlservr-setup This step is the same for both distributions: # /opt/mssql/bin/sqlservr-setup

The last step involves adding the rule to the firewall:

Ubuntu Red Hat IPTABLES 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

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).