How To Install SQL Server On Ubuntu Linux

In December 2016, Microsoft made their SQL Server database available in Linux. Here, we are going to cover how you can install and perform basic setup of MSSQL in the Ubuntu distribution of Linux.
Before going into deep, let’s have look at what we have covered already on SQL and Linux VPS Hosting;

Install MSSQL In Ubuntu 16.10

First, we are going to set up the repository file. Microsoft has provided a copy of this for Ubuntu here:
https://packages.microsoft.com/config/ubuntu/16.04/mssql-server.list
We are going to use the get command to copy this file to the /etc/apt/sources.list.d directory so that we may use it with apt-get. After this, we will execute ‘apt-get update’ in order to sync the package index files with the new source that we’ve just added.

root@ubuntu:~# wget https://packages.microsoft.com/config/ubuntu/16.04/mssql-server.list -O /etc/apt/sources.list.d/mssql-server.list
root@ubuntu:~# apt-get update

When this tutorial was made, the 16.10 directory at https://packages.microsoft.com/ubuntu/16.10/mssql-server/ was empty, so we instead used the 16.04 files here, which seems to have worked just fine.
Since the repository file is in place, the installation is as simple as running the command below. When this tutorial was made, the total size of the package and all dependencies was 747MB.

root@ubuntu:~# apt-get install mssql-server -y
...
+-------------------------------------------------------------------+
| Please run /opt/mssql/bin/sqlservr-setup to complete the setup of |
|                  Microsoft(R) SQL Server(R).                      |
+-------------------------------------------------------------------+

After the installation has finished, we recommend you run the /opt/mssql/bin/sqlservr-setup bash script to finish the setup process.
While performing our first installation attempt, we received the following error, as our virtual machine was only running with 2GB of memory. Make sure that you have enough memory before continuing.
sqlservr: This program requires a machine with at least 3250 megabytes of memory.
Microsoft(R) SQL Server(R) setup failed with error code 1.
You’ll be able to continue after you have adequate memory available.
This is all; Microsoft SQL Server should now be running successfully and listening for traffic on TCP port 1434.

root@ubuntu:~# systemctl status mssql-server
â mssql-server.service - Microsoft(R) SQL Server(R) Database Engine
   Loaded: loaded (/lib/systemd/system/mssql-server.service; enabled; vendor preset: enabled)
   Active: active (running) since Fri 2016-12-30 22:55:23 AEDT; 33s ago
 Main PID: 4164 (sqlservr)
   CGroup: /system.slice/mssql-server.service
           ââ4164 /opt/mssql/bin/sqlservr
           ââ4201 /opt/mssql/bin/sqlservr
root@ubuntu:~# netstat -antp | grep 1434
tcp        0      0 127.0.0.1:1434          0.0.0.0:*               LISTEN      4201/sqlservr

Connecting To MSSQL

If you’d like to connect to the server from Linux, we have to install the mssql-tools package, which comes from a different repository than the one that we just set up. You can find it here:
Packages
Now, we will be downloading a copy of the prod.list file, and we will place it in the /etc/apt/sources.list.d directory.

root@ubuntu:~# wget https://packages.microsoft.com/config/ubuntu/16.04/prod.list -O /etc/apt/sources.list.d/pord.list
root@ubuntu:~# apt-get update

We may now continue with installing the mssql-tools package, as shown below:

root@ubuntu:~# apt-get install mssql-tools -y

After this is installed, we will be able to use the sqlcmd command to interact with the database.
To see how to run sqlcmd, just run it with the -? Option for help.
Sadly, it appears that once you specify the –P option for the password, the password needs to be provided in the command line with no option of being prompted for it later. Note that your password is going to be stored in your bash history when running the package this way.

root@ubuntu:~# sqlcmd -U SA -P password
1> create database test;
2> go
1> use test;
2> go
Changed database context to 'test'.
1> create table websites(domain varchar(255));
2> go
1> insert into websites (domain)
2> values ('rootusers.com');
3> go
(1 rows affected)
1> select domain
2> from websites;
3> go
domain
rootusers.com
(1 rows affected)

In the example above, we created a test database with a table named websites and a column for domain names. We then insert a domain name and pull it back out with select, confirming both that we are able to connect and that basic SQL queries appear to be working as expected.

Final Thoughts 

Microsoft’s SQL server is now available for installation on Linux.
Personally, we don’t think we will ever use this over other alternatives, like MariaDB or PostgreSQL.
One more thing..
Share this tutorial with your hosting administrators and networking experts friends